External data from Access & relocation of database (Office 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

This is a toughie. I had the same problem and had to write a macro to re-do the path. The easiest way is to record a macro as you set it up and then examine it for the details that you want to change. Put it the new path and use the macro to edit the connection.

There are two path type connections (from memory), one that deals with the location of the file and one that deals with the name of the file (includes the path for some reason!!).

I have no idea about Office XP – sorry.

Tim

Reply | Quote