Как обновить связанные таблицы в Access mdb при изменении ODBC

Вы можете использовать приведенный ниже код, чтобы обновить все таблицы ODBC в вашем проекте Access до заданного DSN.

Как это использовать

Просто скопируйте код в новый или существующий модуль VBA и, если вы хотите обновить ссылки, вызовите его с правильным DSN для нового соединения ODBC:

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

Также ознакомьтесь с справкой Access для метода TableDef.RefreshLink.

Версия кода 1

Классический способ повторного связывания, но Access может сохранить информацию о соединении в памяти, если таблицы использовались до вызова RefreshODBCLinks.

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

Версия кода 2

Это полностью воссоздает связанные таблицы ODBC: старые будут переименованы, затем будут созданы новые таблицы, использующие данный DSN, перед удалением старой связанной версии.
Убедитесь, что вы проверили это и, возможно, добавили код в лучше обрабатывать ошибки по мере необходимости.

Также обратите внимание, что параметр dbAttachSavePWD, переданный во время создания таблицы ODBC, сохранит пароль ODBC (если есть) в Access. Просто удалите его, если это не то, что вам нужно.

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

И последнее: если у вас все еще возникают проблемы, требующие перезапуска Access, чтобы изменения были видны, взгляните на мой код в Программный перезапуск и сжатие базы данных на моем сайте.

Примечание. Версия кода 2 частично основана на этой статье о Access Web.

Какую версию Access вы используете? В 2000 году вы можете перейти в Инструменты> Утилиты баз данных> Менеджер связанных таблиц, чтобы изменить свои настройки.