0

I am trying to update several MS Access applications after migrating a SQL server from one server to a newer one (yay for no longer hosting servers on someone's laptop).

The migration of the SQL server went well however now I need to update several MS access applications which have linked tables pointed to the old SQL server instance. I know I can use the linked table manager and add new links to these tables however I have about 20 applications with 50+ linked tables in each and am looking for a less cumbersome way of doing this.

TL/DR: I need to update the SQL Server address that is being used for hundreds of linked tables in ACCESS and am looking for the easiest way to do this.

timmyda
  • 65
  • 2
  • 12
  • 3
    Does this answer your question? [MS Access - How to change the linked table path by amend the table](https://stackoverflow.com/questions/47283679/ms-access-how-to-change-the-linked-table-path-by-amend-the-table) – Erik A Nov 01 '19 at 18:42
  • 3
    Changing an SQL server path is not really different than changing the path of a file. Just adjust the connection strings of all linked tables by using the `ChangeConnection` function I shared there. – Erik A Nov 01 '19 at 18:44
  • @ErikA unfortunately when I ran the query from your suggestion, it only gave me linked mdb's -- and didnt produce a way for me to list the ODBC linked tables. For reference -- here is an example of one of the hundreds of tables I need to relink as we've moved the address of our SQL server: – timmyda Nov 01 '19 at 19:29
  • https://i.imgur.com/LiFT1C1.png and thank you for your quick reply/help thus far! – timmyda Nov 01 '19 at 19:30
  • 1
    Sorry, but that image is too low quality for me to read. I don't understand exactly what's going wrong. Have you obtained the old and new connection strings first? Usually, you can do that by using `?CurrentDb.TableDefs!SomeTable.Connect` in the immediate window – Erik A Nov 01 '19 at 20:07
  • Here's a larger link for the image:https://i.imgur.com/znnqFzj.png I have the connection string (as it is the same, the only difference being the name of the server it is on) I just am wondering if there an easy to mass update the connection string for ODBC linked tables instead of having to add each table one by one. – timmyda Nov 01 '19 at 21:09
  • That's exactly what that `ChangeConnection` function does. Just plug in the old and new string, run it, and it replaces the old one with the new one. – Erik A Nov 01 '19 at 21:16
  • @ErikA I think that will do as I needed -- however, how am I able to run that VBA code one time in my project to update? I added the module, and then made a macro to run the module using RunCode, but as my parameters are so long (query strings) it wont let sufficiently add parameters to the ChangeConnection(old, new) call (says 255 chars is the limit for Run Code). Is there a different way I should be approaching this? Thanks again for your help! – timmyda Nov 04 '19 at 22:29
  • You should just use the immediate window. Please Google that, else I'd have to provide a lot of screenshots – Erik A Nov 05 '19 at 06:31
  • See this section [https://stackoverflow.com/questions/564265/how-to-refresh-linked-tables-in-an-access-mdb-when-odbc-changes](https://stackoverflow.com/questions/564265/how-to-refresh-linked-tables-in-an-access-mdb-when-odbc-changes) – Vlado Nov 12 '19 at 15:13

1 Answers1

0

I think this is the best solution: Automatically relink Microsoft Access tables

Vlado
  • 839
  • 6
  • 16