12

I can create an Access mdb and add a linked table to an Sql Server database via ODBC. If I change the Sql Server that the ODBC is connecting to with the ODBC control panel applet the mdb still connects to the original Sql Server until Access is restarted.

Is there a way to relink these linked server tables without restarting Access?

EDIT: I would like to do this in code

BTB
  • 2,126
  • 3
  • 21
  • 22

2 Answers2

30

You can use the code below to refresh all ODBC tables in your Access project to a given DSN.

How to use it

Just copy the code in a new or existing VBA module and, where you want to refresh the links, call it with the proper DSN for the new ODBC connection:

RefreshODBCLinks "ODBC;DRIVER=SQL Server Native Client 10.0;" & _"
                 "SERVER=SQLSERVER;UID=Administrator;" & _
                 "Trusted_Connection=Yes;" & _
                 "APP=2007 Microsoft Office system;DATABASE=OrderSystem;"

Also, have a look at the Access help for the TableDef.RefreshLink method.

Code version 1

Classic way of relinking but Access may keep connection information in memory if the tables have been used before RefreshODBCLinks is called.

Public Sub RefreshODBCLinks(newConnectionString As String)
    Dim db As DAO.Database
    Dim tb As DAO.TableDef
    Set db = CurrentDb
    For Each tb In db.TableDefs
        If Left(tb.Connect, 4) = "ODBC" Then
            tb.Connect = newConnectionString
            tb.RefreshLink
            Debug.Print "Refreshed ODBC table " & tb.Name
        End If
    Next tb
    Set db = Nothing
End Sub

Code version 2

This will completely re-create the ODBC linked tables: the old ones will be renamed, then new tables using the given DSN will be created before deleting the old linked version.
Please make sure you test this and maybe add some code to better handle errors as necessary.

Note also that the parameter dbAttachSavePWD passed during creation of the ODBC table will save the ODBC password (if any) in Access. Just remove it if that's not what you need.

Public Sub RefreshODBCLinks(newConnectionString As String)
    Dim db As DAO.Database
    Dim tb As DAO.TableDef
    Dim originalname As String
    Dim tempname As String
    Dim sourcename As String
    Dim i As Integer

    Set db = CurrentDb
    ' Get a list of all ODBC tables '
    Dim tables As New Collection
    For Each tb In db.TableDefs
        If (Left(tb.Connect, 4) = "ODBC") Then
            tables.Add Item:=tb.Name, key:=tb.Name
        End If
    Next tb

    ' Create new tables using the given DSN after moving the old ones '
    For i = tables.count To 1 Step -1
            originalname = tables(i)
            tempname = "~" & originalname & "~"
            sourcename = db.TableDefs(originalname).SourceTableName
            ' Create the replacement table '
            db.TableDefs(originalname).Name = tempname
            Set tb = db.CreateTableDef(originalname, dbAttachSavePWD, _
                                        sourcename, newConnectionString)
            db.TableDefs.Append tb
            db.TableDefs.Refresh
            ' delete the old table '
            DoCmd.DeleteObject acTable, tempname
            db.TableDefs.Refresh
            tables.Remove originalname
            Debug.Print "Refreshed ODBC table " & originalname
    Next i
    Set db = Nothing
End Sub

One last thing: if you're still getting issues that require that you restart Access for the changes to be visible, then have a look at my code in Restarting and compacting the database programmatically on my site.

Note: Code Version 2 was inspired in part from this Access Web article.

Renaud Bompuis
  • 16,596
  • 4
  • 56
  • 86
  • 1
    It would be a very good idea to exclude system tables, that is tables that start "MSys" – Fionnuala Feb 19 '09 at 12:11
  • 1
    OK, but why would they have a connection string starting with "ODBC"? They wouldn't match that in any case. – Renaud Bompuis Feb 19 '09 at 12:29
  • 1
    It appears to work for me but only if I change the connection string. If I change say the server in the ODBC then the Linked table still connects to the original server after I call RefreshODBCLinks. If I change the database name in the connection string then the linked server is refreshed. – BTB Feb 19 '09 at 12:34
  • OK, please try the second version or simply use my restart function if that's enough to force Access to refresh. – Renaud Bompuis Feb 19 '09 at 17:25
  • These solutions do not handle the "attached views": a SQL Server view that is attached as a table in Access needs a local index in order to be updatable. – iDevlop Sep 06 '11 at 11:50
  • Wondering why you match on "ODBC;" rather than (tbl.Attributes And dbAttachedODBC) > 0 ... – Graham Charles Feb 10 '12 at 08:24
  • @GrahamCharles that will work just as well too. I think I used the string match because that code was extracted from a version that was doing more stuff than what was needed for answering the question. – Renaud Bompuis Feb 11 '12 at 09:49
  • 1
    Thanks for this great function, works like a charm. I had written my own but it was just modifying the Connect property and that does not allow changing the dbAttachSavePWD attribute. I would just add one optional argument, sFilter, to ignore tables that do not match the search string. That allows to use the function to selectively update tables when you have links to different databases. – iDevlop Aug 13 '14 at 08:11
  • If tables are linked to SQL Server *views*, code version 2 should be edited to re-create the PK, otherwise data will be R-O. – iDevlop Jul 31 '15 at 14:18
  • I waited too long to edit my above comment, but a routine to recreate the Indexes can be found here: http://www.accessmvp.com/djsteele/DSNLessLinks.html – iDevlop Jul 31 '15 at 14:27
  • I haven't used your code but have created my own. One thing I note is a 64 character limit with table names in Access. The obvious solution is to truncate the table name to 64 characters, but leave the SourceName argument as the original (more than 64 chars) table name. The link works fine the first time. However, and I think this is an oversight/bug in access, when refreshing the table using TableDef.RefreshLink, the SourceName is also truncated to 64 characters and an error is thrown! FRUSTRATING! – blobbles Feb 17 '16 at 20:13
5

What version of Access are you using? In 2000, you can go to Tools>Database Utilities>Linked Table Manager to change your settings.

Dinci Garrone
  • 103
  • 7
  • Thanks - you're right it works if I use the Linked Table Manager and check the box "Always prompt for new location" and then choose the ODBC again. I would like to relink the table in code though - I will edit my question to make this clear. – BTB Feb 19 '09 at 11:59
  • Why would you like to relink your table? What will be gained by doing that? – Brettski Feb 24 '09 at 04:56
  • 1
    The ODBC is changed to point at a different server/database - but the Access app is unaware of the change until its restarted. – BTB Feb 24 '09 at 13:25