Query Access Database in Excel

I have a spreadsheet with multiple queries from a single database “DB1”. I have a second database “DB2” with the same tables as DB1 and would like to change all of my queries to run from DB2 instead of DB1. Is there an easy way to do that?


Thanks!

Hi

Yes - you need to get the current connection string you are using for your current database. Easiest way to do this is to (whilst this spreadsheet is active in Excel) open up the VBE with Alt+F11, open up the Immediate Window with Ctrl+G and type in:

Please Login or Register  to view this content.

and press return at the end of the line.

This will give you a string similar to the following:

Please Login or Register  to view this content.

obviously with different paths/folders. It's the bit in red that you will need to change to the new database location. You can do this for all the QTs by running the following code script in this workbook:

Please Login or Register  to view this content.

replacing the bit in red with your new database name and file path (this connection string must in all respects be identical to the one you got in the Immediate Window, just the new file name and path should be different).

Make sense?

Richard

It worked!

Thanks Much!