Linking to Access from Excel - how to change DB name?

[spikepl]

I have Office 2007.

A spreadsheet has links to an Access DB, where data is drawn into Excel from some 15 tables in Access. Each connection is defined with name of database, the table/query name and other details.

Now, if I want to link to another database, with the same structure but a different name or location in the file sturcture, the thing requires that I redefine each connection from scratch. I cannot just simply input another file name.

In My Connections ( in the My Documents-folder) each connectioin is in a .ODC file, where I in principle could change the path to the database.

Is there a smarter way?

 

[Trevor G]

In your Excel workbook you can look at the Data Tab and Links and change the source.

One thing to thing of is in Excel record a macro to connect to the database this will then give you the code to the path of the database and if you want to change it then you can jsut change the path and re run the code or refresh the thinks.

It sounds as though you should be able to do this.

 

[spikepl]

Well, we gave up and redid the the thing. A macro is a good idea for the future and is noted, thank you for that.

I am surprised at how clumsy the relinking process is, where a change of DB requires respecification of what one wants to extract. But I guess it's one of those things ...