5

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

Community
  • 1
  • 1
John
  • 313
  • 1
  • 4
  • 8

1 Answers1

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.
smoore4
  • 4,520
  • 3
  • 36
  • 55
  • Dah, exactly what I was looking for. I guess I'm used to things being obvious in VS. Thanks! – John Apr 28 '14 at 19:12
  • 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. – smoore4 Apr 28 '14 at 19:16
  • OK, I updated all the tables but now the forms are REALLY slow. Did I do something wrong? – John Apr 28 '14 at 19:58
  • I've never seen that personally, but check this article out: http://superuser.com/questions/76849/ms-access-2007-link-table-manager – smoore4 Apr 29 '14 at 06:06