I need to create some linked tables in Microsoft Access to tables in SQL Server. I created a File DSN ODBC connection to my SQL Server, then in Access, chose New Data Source > From Other Sources > ODBC Database, selected “Link to the data source by creating a linked table”, navigated to my file DSN, selected my tables, and everything is working great, I’ve got data in my linked tables.

This is where I’m confused. If I delete the File DSN, Access still works. If I change the server in the File DSN, Access is still getting data from the original server. If I go to Access, select Refresh Link then go into the Linked Table Manger, the connection is still showing the original server, not the updated one in the File DSN.

What do I need to do to get Access to use the changes to the File DSN?

We have development, test and production environments. I want the Access database to connect to the different environments based on what is in the File DSN.

I’m using Access Version 2407 (Build 17830.20166). My Access database is in 2002-2003 format, it’s a really old application and I can’t change this.