Excel external data from access (Access 97/ Excel 97)

Topic

New Reply

I use external data in Excel from access via the Access ODBC Driver. When the source database is relocated, Excel cannot find the database anymore as it stores the absolute path. Changing the ODBC source doesn’t help therefore.
Is the a workaround? Has this behaviour changed in Office XP?

Reply | Quote

Replies

Was your empty answer an error or was it an accident

Reply | Quote

If you move the database, then how should ODBC know where you’ve moved it to? Perhaps you moved it to a floppy disk drive, and then what good does it do if it somehow follows it?

If you’ve got machines which are relying on your database to retrieve data, then you will have to live with the fact that that database must not move or change name, otherwise those machines will not be able to use it anymore.

The ‘workaround’ would be to NOT move the source database!

Reply | Quote

ODBC knows because I told it where the database has gone to. But Excel/MSQuery don’t care. Of course I know that no one can access my database if I wear it in my pocket on a floppy. The reason is that I want to give the entire package of combined Excel & Access files to a collegue who doesn’t want to construct exactly the same file structure I have got. Also if you develop somtehing on your local drive and want to place it on a network drive you will get the same problem

Reply | Quote

I’m not clear on the problem you’re having. Are you trying to query Access from Excel using existing saved MS Queries? In that case, it will not adapt itself to a different location for the database. Are you doing this from code, for the MS Query grid or what?

You can’t expect it to adapt itself to an entirely different file structure without any human intervention.

Reply | Quote

The queries are not saved externally, but stored in the Excel sheet. The normal behaviour I would expect, is that EXCEL/MSQUERY look into ODBC to get the location of the access database, but they do only the first time, when the query is set-up, the they use the absolute path from the first setting in the ODBC source. The human intervantion was that after moving the database I changed the ODBC source according to the new location.

Reply | Quote

Aha – so you changed the ODBC setting and MSQuery doesn’t pick up the change?

Then I misunderstood your original post, apologies.

Perhaps one way to workaround it would be to re-bind the query to the ODBC entry at runtime. Have you tried this?

Reply | Quote

I tried that, but the query was totally destroyed and you have to redo them all by hand.

Reply | Quote

what a horrible bug, I do hope it’s fixed in XP!

Reply | Quote

It is not a bug. That is, and has always been, normal behavior since the path is hard coded into the query. You can get around it somewhat in Office 2000 by using a Microsoft DataLink (udl) to hold the link to the data source. Then you just point at the datalink, which can be in the same folder as the spreadsheet, and let it find the data source.

Reply | Quote

ODBC is supposed to abstract data storages and their locations into simple, commonly accessible Datasources, referenced by their DSN in the local ODBC configuration. If you should move the datasource and change the ODBC entry accordingly, the client application of that DSN should not notice, or indeed, care that the underlying datasource has been moved. I’m still of the opinion that this it is a bug in MSQuery in that it reads the underlying path and remembers it. Or, if it is indeed by design, I deem this to be very bad design.

Reply | Quote

It isn’t really an MSQuery or Excel issue, it’s an operating system issue.

Reply | Quote

why do you say it’s an operating system issue? how is the OS causing a problem here?

Reply | Quote

I recorded a macro whist using ‘GET EXTERNAL DATA’ to get data from an Access database. I now run the macro & just ignore the MS Query. Don’t know if this is a viable workaround, but here is the code (I don’t know how much of this is extraneous rubbish):

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
“ODBC;DSN=MS Access Database;DBQ=P:INFOTECUK_CSSYS3MAR_ADDMarine Accrual.mdb;DefaultDir=P:INFOTECUK_CSSYS3MAR_ADD;DriverId=2” _
), Array(“5;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;”)), Destination:= _
Range(“A6”))
.CommandText = Array( _
“SELECT MarData.LOSS_ID, MarData.LOSSNAME, MarData.CAT_CODE, MarData.PAID_GBP, MarData.PAID_USD, MarData.PAID_CAD, MarData.OSLR_GBP, MarData.OSLR_USD, MarData.OSLR_CAD, MarData.PAID_cSTG, MarData.INC_c” _
, _
“STG” & Chr(13) & “” & Chr(10) & “FROM `P:INFOTECUK_CSSYS3MAR_ADDMarine Accrual`.MarData MarData” & Chr(13) & “” & Chr(10) & “ORDER BY MarData.LOSS_ID” _
)
.Name = “Import Marine FGU”
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Reply | Quote

The OS keeps track of where things are. The applications can only use the locations hard coded into them. If your DSN’s are bad, you can’t blame Access or MS Query for that.

Reply | Quote

That’s the whole point. There is nothing wrong with the DSN’s. The problem lies in MSQuery reading the hardcoded path from the DSN and storing that, instead of using the DSN and allowing ODBC to realise the abstraction the DSN produces from the underlying path.

Reply | Quote

Sorry, but just because something doesn’t work the way you want it to doesn’t constitute a bug. Look up Microsoft Data Links for an alternative, although I’m not sure Access 97 could handle them properly.

Reply | Quote

let’s look at the facts…..

1. you set up an MSquery (in excel or whatever)
2. you point it to a DSN, because you want the physical location to be abstracted from the application, i.e. you only want your application to know abuot the ‘MYDB’ DSN and not care where the underlying DB is (this is one of the reasons ODBC was invented)
3. you run your app happily
4. you move your underlying mdb and you change the DSN accordingly, so the DB move is transparent to the app
5. the app breaks because the stupid thing has gone and read the underlying path from the DSN, hardcoded it into itself and forgotten abuot the DSN completely!

that does not look like expected behaviour to me….

Reply | Quote

Um, When you have a DSN, which ODBC enabled applications use to connect to data sources, and an application, which uses these DSN’s, hardcodes a link to a file path – rather than checking from the DSN – that IS a bug.

If it looks like a duck…

Regards

Al

Reply | Quote

Access doesn’t know *anything* about DSNs except the path the thing passes back when it first connects. The connection is not dynamic, nor does it pretend to be. If you want to use DSNs then use a data link, which does adjust itself.

Reply | Quote

so do we agree that Access does not use DSNs and ODBC as it is intended? obviously data links do

Reply | Quote

Hi,
I haven’t looked at this matter for along time because I was sick of it. Adam is exactly right and its not an Access Problem, but an Excel Problem. Its still appearent in Office XP. It did some investigatation:
When you save the Query and remove the “DSN” enty containing the path, the the path is reretrived from the ODBC but subsequently stored in the Excel Query.
I don’t know why Charlotte is defending Big Brother so enthusiastically, but this is what I call a bug. And they know about it, as in the according help files they describe that you have to maintain the absolute Path.
It was always easier to change the specs than to fix the errors.

Reply | Quote

I don’t disagree that it’s a bug. I just say it isn’t a bug in *Access*.

Reply | Quote