How does one change the connection string of linked tables in MS Access

Question

So I just started a new job and part of my responsibilities are to support some old Access Database applications until I can get them replaced with something better. One of the first things I noticed is that the Access Database uses Linked Tables (linked to SQL Server), but they're pointing to production. Before I made any changes I wanted to set up a test environment and point the Access file to a test SQL Server.

Here is where I'm lost. I can't figure out how to change where the linked tables are pointing to. I've seen on here where some have suggested to change it in code, but forms seem to be bound at design time and I'd prefer to make the changes in Design mode so that the Test and Prod versions run completely separate. I can't find where this is done anywhere.

Is there someone that could point me in the right direction? I do NOT want to change the connection properties at run time...

Thanks, John

Answer 1

You basically want to set up a new ODBC data source to a dev or test environment then use MS Access's Linked Table Manager to point it to the new tables.

• Open the database that contains links to tables.
• On the Tools menu, point to Database Utilities, and then click Linked Table Manager.
• Select the Always prompt for new location check box.
• Select the check box for the tables whose links you want to
  change, and then click OK.
• In the Select New Location of dialog box, specify the new location, click Open, and then click OK.