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

sql-server ms-access linked-tables
John picture John · Apr 28, 2014 · Viewed 12.2k times · Source

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

smoore4 picture smoore4 · Apr 28, 2014

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.

Related questions

How to link Access table to SQL Server with VBA

I am trying to create a linked (or imported) table in Access with a SQL Server backend. Basically the business users periodically need a copy of the table [SQL Rulesnew] (yes, with the space, sigh) so we want to give …

Read More
MS Access call SQL Server stored procedure

I have an MS Access application that contains all tables linked to SQL Server, so in MS Access VBA code or query I work with those tables very simple, I access them via name, like [Customers]. Also I have a …

Read More
How do I create a table alias in MySQL

I am migrating an MS Access application (which has linked tables to a MSSQL Server) to MySQL. As a means to overcome some MSAccess table naming problems, I am seeking a solution to add a MySQL table alias that will …

Read More