ODBC Link Table in Access - Changing Database

[BRIANVH]

Access 2000

I have a new server that will be dedicated to our ERP database (sqlbase), and I have a test copy of my database functioning on the new server. Now I am trying to get my existing Access Reports working off the test database on the new server. I tried changing (and also deleting and re-creating) the ODBC system DSN (on a client workstation) to reference the new server and database name, but somehow the data I am still getting is from the live database. Anyone have any experience with how to change this? Will I have to recreate all of my link tables?

 

[Tranman]

Brian,
There is a tool called the "Linked Table Manager" that is used to change the connection of a linked table from one ODBC DSN to another.

In Access 97, it is found in Tools|Add-INs|Linked Table Manager. In Access XP (and 2K, I think), it is in Tools|Database Utilities|Linked Table Manager.

Just open the Linked Table Manager, mark all of the tables you want to re-link, check the box in the lower left (always ask, or something like that), and click OK or Change or whatever the top button is. You'll be able to navigate to the new ODBC DSN and update the links.

Good Luck,
Tranman