如何在 ODBC 更改时刷新 Access mdb 中的链接表 [英] How to refresh linked tables in an Access mdb when ODBC changes

问题描述

我可以创建一个 Access mdb 并通过 ODBC 将链接表添加到 Sql Server 数据库.如果我使用 ODBC 控制面板小程序更改 ODBC 连接到的 Sql Server，mdb 仍会连接到原始 Sql Server，直到 Access 重新启动.

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.

有没有办法在不重新启动 Access 的情况下重新链接这些链接的服务器表?

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

我想在代码中做到这一点

I would like to do this in code

推荐答案

您可以使用以下代码将 Access 项目中的所有 ODBC 表刷新到给定的 DSN.

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

如何使用

只需将代码复制到新的或现有的 VBA 模块中，然后在要刷新链接的位置使用新 ODBC 连接的正确 DSN 调用它:

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;"

另外，请查看 TableDef.RefreshLink 方法的 Access 帮助.

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

代码版本 1

重新链接的经典方式，但如果在调用 RefreshODBCLinks 之前使用了表，Access 可能会将连接信息保留在内存中.

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

代码版本 2

这将完全重新创建 ODBC 链接表:将重命名旧表，然后在删除旧链接版本之前创建使用给定 DSN 的新表.
请确保您对此进行了测试，并且可能会根据需要添加一些代码以更好地处理错误.

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.

另请注意，在创建 ODBC 表期间传递的参数 dbAttachSavePWD 将在 Access 中保存 ODBC 密码(如果有).如果这不是您需要的，只需将其删除.

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

最后一件事:如果您仍然遇到需要重新启动 Access 才能看到更改的问题，请查看我在 在我的网站上以编程方式重新启动和压缩数据库.

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.

注意:代码版本 2 的部分灵感来自这篇 Access Web 文章.

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

这篇关于如何在 ODBC 更改时刷新 Access mdb 中的链接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！