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

smoore4 · Accepted Answer · 2014-04-28 19:02:51Z

7

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.

answered Apr 28, 2014 at 19:02

smoore4

4,9263 gold badges43 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

John Over a year ago

Dah, exactly what I was looking for. I guess I'm used to things being obvious in VS. Thanks!

smoore4 Over a year ago

Glad I could help! You put me over 1000 which is a goal I set for myself a while ago, so thank you. Good luck with Access. It is a great tool for small projects. Not so great for big or complex ones though.

John Over a year ago

OK, I updated all the tables but now the forms are REALLY slow. Did I do something wrong?

smoore4 Over a year ago

I've never seen that personally, but check this article out: superuser.com/questions/76849/ms-access-2007-link-table-manager

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related