当文件夹更改时，在同一文件夹中保留 Access DB 的链接表

Question

我有两个共享链接表的 Access 数据库。 它们一起部署在一个目录中，并通过 Word 形式的代码进行访问。

当两个数据库复制（一起）到不同的文件夹时，如何确保保留链接？ 由于我本身没有“打开”数据库（它是通过 ADO 访问的），所以我不知道如何编写代码来刷新链接。

Answer 1

您指的是更新Word 表单内的链接，还是Access 数据库之间的链接表链接？

对于前者，我知道的最好方法是将连接字符串保留在 Word 文档/VBA 项目中的模块级别，并使它们成为常量字符串。 然后，在为 ADO Connection 对象设置连接字符串时，将相关连接字符串 const 传递给它。

对于后者，我很想在连接字符串中使用每个 Access 数据库中的数据到另一个数据库的相对路径。 例如，

Dim connectionString as String

connectionString = ";DATABASE=" & CurrentProject.Path & "\[Database Name Here].mdb"

如果正如您所说，数据库被一起复制到不同的文件夹（我假设复制到同一文件夹中）。

Answer 2

不幸的是，我仍在使用 Access 2007。我从上面的代码块之一开始，该代码块对我来说不起作用。 由于访问 vba 的能力较少，我将其简化为仅第一个循环，该循环获取表路径并将其更新到位。 下一个遇到这个问题的人可以发表评论或更新。

选项比较数据库

'------------------------------------------------------------'
' Reconnect all linked tables using the given path.          '
' This only needs to be done once after the physical backend '
' has been moved to another location to correctly link to    '
' the moved tables again.                                    '
' If the OnlyForTablesMatching parameter is given, then      '
' each table name is tested against the LIKE operator for a  '
' possible match to this parameter.                          '
' Only matching tables would be changed.                     '
' For instance:                                              '
' RefreshLinksToPath(CurrentProject.Path, "local*")          '
' Would force all tables whose ane starts with 'local' to be '
' relinked to the current application directory.             '
'
' Immediate window type
' RefreshLinksToPath Application.CurrentProject.Path

'------------------------------------------------------------'
Public Function RefreshLinksToPath(strNewPath As String, _
    Optional OnlyForTablesMatching As String = "*") As Boolean

    Dim strDBPath As String
    'Dim strTbl As String
    'Dim strMsg As String
    Dim strDBName As String
    Dim dbCurr As DAO.Database
    Dim dbLink As DAO.Database
    Dim tdf As TableDef

    Set dbCurr = CurrentDb
    Dim strConn As String
    Dim strNewDbConn1 As String
    Dim strNewDbConn2 As String
    Dim strNewDbConn  As String

    '  On Local Error GoTo fRefreshLinks_Err

    'First get all linked tables in a collection'
    dbCurr.TableDefs.Refresh
    For Each tdf In dbCurr.TableDefs
        With tdf
            If ((.Attributes And TableDefAttributeEnum.dbAttachedTable) = TableDefAttributeEnum.dbAttachedTable) _
               And (.Name Like OnlyForTablesMatching) Then

                strConn = tdf.Connect
                strDBPath = Right(strConn, Len(strConn) - (InStr(1, strConn, "DATABASE=") + 8))
                strDBName = Right(strDBPath, Len(strDBPath) - InStrRev(strDBPath, "\"))
                Debug.Print ("===========================")
                Debug.Print (" connect is " + strConn)
                Debug.Print (" DB PAth is " + strDBPath)
                Debug.Print (" DB Name is " + strDBName)

                strDBNewPath = strNewPath & "\" & strDBName
                Debug.Print (" DB NewPath is " + strDBNewPath)

                strNewDbConn1 = Left(strConn, (InStr(1, strConn, "DATABASE=") - 1))
                strNewDbConn2 = "DATABASE=" & strDBNewPath
                strNewDbConn = strNewDbConn1 & strNewDbConn2
                Debug.Print (" DB strNewDbConn is " + strNewDbConn)

                'Change the connect path
                tdf.Connect = strNewDbConn
                tdf.RefreshLink
            End If
        End With
    Next
End Function

Answer 3

2009 年 4 月 14 日更新
我发现我之前在这里给出的答案是错误的，所以我用新代码更新了它。

如何继续

• 将以下代码复制到 VBA 模块。

• 从代码或 VBA IDE 中的“立即”窗口中，只需键入：

  RefreshLinksToPath Application.CurrentProject.Path 
    

这现在将重新链接所有链接的表以使用应用程序所在的目录。
只需要执行一次或每当重新链接或添加新表时执行此操作。
我建议您每次启动应用程序时都通过代码执行此操作。
然后您就可以毫无问题地移动数据库。

代码

'------------------------------------------------------------'
' Reconnect all linked tables using the given path.          '
' This only needs to be done once after the physical backend '
' has been moved to another location to correctly link to    '
' the moved tables again.                                    '
' If the OnlyForTablesMatching parameter is given, then      '
' each table name is tested against the LIKE operator for a  '
' possible match to this parameter.                          '
' Only matching tables would be changed.                     '
' For instance:                                              '
' RefreshLinksToPath(CurrentProject.Path, "local*")          '
' Would force all tables whose ane starts with 'local' to be '
' relinked to the current application directory.             '
'------------------------------------------------------------'
Public Function RefreshLinksToPath(strNewPath As String, _
    Optional OnlyForTablesMatching As String = "*") As Boolean

    Dim collTbls As New Collection
    Dim i As Integer
    Dim strDBPath As String
    Dim strTbl As String
    Dim strMsg As String
    Dim strDBName As String
    Dim strcon As String
    Dim dbCurr As DAO.Database
    Dim dbLink As DAO.Database
    Dim tdf As TableDef

    Set dbCurr = CurrentDb

    On Local Error GoTo fRefreshLinks_Err

    'First get all linked tables in a collection'
    dbCurr.TableDefs.Refresh
    For Each tdf In dbCurr.TableDefs
        With tdf
            If ((.Attributes And TableDefAttributeEnum.dbAttachedTable) = TableDefAttributeEnum.dbAttachedTable) _
               And (.Name Like OnlyForTablesMatching) Then
                collTbls.Add Item:=.Name & .Connect, key:=.Name
            End If
        End With
    Next
    Set tdf = Nothing

    ' Now link all of them'
    For i = collTbls.count To 1 Step -1
        strcon = collTbls(i)
        ' Get the original name of the linked table '
        strDBPath = Right(strcon, Len(strcon) - (InStr(1, strcon, "DATABASE=") + 8))
        ' Get table name from connection string '
        strTbl = Left$(strcon, InStr(1, strcon, ";") - 1)
        ' Get the name of the linked database '
        strDBName = Right(strDBPath, Len(strDBPath) - InStrRev(strDBPath, "\"))

        ' Reconstruct the full database path with the given path '
        strDBPath = strNewPath & "\" & strDBName

        ' Reconnect '
        Set tdf = dbCurr.TableDefs(strTbl)
        With tdf
            .Connect = ";Database=" & strDBPath
            .RefreshLink
            collTbls.Remove (.Name)
        End With
    Next
    RefreshLinksToPath = True

fRefreshLinks_End:
    Set collTbls = Nothing
    Set tdf = Nothing
    Set dbLink = Nothing
    Set dbCurr = Nothing
    Exit Function

fRefreshLinks_Err:
    RefreshLinksToPath = False
    Select Case Err
        Case 3059:

        Case Else:
            strMsg = "Error Information..." & vbCrLf & vbCrLf
            strMsg = strMsg & "Function: fRefreshLinks" & vbCrLf
            strMsg = strMsg & "Description: " & Err.Description & vbCrLf
            strMsg = strMsg & "Error #: " & Format$(Err.Number) & vbCrLf
            MsgBox strMsg
            Resume fRefreshLinks_End
    End Select
End Function

此代码改编自以下来源：http://www .mvps.org/access/tables/tbl0009.htm。
我删除了对其他函数的所有依赖，使其独立，这就是为什么它比应有的长度要长一些。

Answer 4

Renaud 的答案在 Access 2010 中不再适用于 Excel 或 CSV 文件。

我做了一些修改：

• 适应连接字符串的当前模式
• 针对 Excel 文件（包括文件名）和 CSV 文件（不包括文件名）以不同的方式处理数据库路径

以下是代码：

Public Function RefreshLinksToPath(strNewPath As String, _
Optional OnlyForTablesMatching As String = "*") As Boolean

Dim collTbls As New Collection
Dim i As Integer
Dim strDBPath As String
Dim strTbl As String
Dim strMsg As String
Dim strDBName As String
Dim strcon As String
Dim dbCurr As DAO.Database
Dim dbLink As DAO.Database
Dim tdf As TableDef

Set dbCurr = CurrentDb

On Local Error GoTo fRefreshLinks_Err

'First get all linked tables in a collection'
dbCurr.TableDefs.Refresh
For Each tdf In dbCurr.TableDefs
    With tdf
        If ((.Attributes And TableDefAttributeEnum.dbAttachedTable) = _
           TableDefAttributeEnum.dbAttachedTable) _
           And (.Name Like OnlyForTablesMatching) Then
            Debug.Print "Name: " & .Name
            Debug.Print "Connect: " & .Connect
            collTbls.Add Item:=.Name & ";" & .Connect, Key:=.Name
        End If
    End With
Next
Set tdf = Nothing

' Now link all of them'
For i = collTbls.Count To 1 Step -1
    strConnRaw = collTbls(i)
    ' Get table name from the full connection string
    strTbl = Left$(strConnRaw, InStr(1, strConnRaw, ";") - 1)
    ' Get original database path
    strDBPath = Right(strConnRaw, Len(strConnRaw) - (InStr(1, strConnRaw, "DATABASE=") + 8))
    ' Get the name of the linked database
    strDBName = Right(strDBPath, Len(strDBPath) - InStrRev(strDBPath, "\"))
    ' Get remainder of connection string
    strConn = Mid(strConnRaw, InStr(1, strConnRaw, ";") + 1, InStr(1, strConnRaw, "DATABASE=") _
                - InStr(1, strConnRaw, ";") - 1)

    ' Reconstruct the full database path with the given path
    ' CSV-Files are not linked with their name!
    If Left(strConn, 4) = "Text" Then
        strDBPath = strNewPath
    Else
        strDBPath = strNewPath & "\" & strDBName
    End If

    ' Reconnect '
    Set tdf = dbCurr.TableDefs(strTbl)
    With tdf
        .Connect = strConn & "Database=" & strDBPath
        .RefreshLink
        collTbls.Remove (.Name)
    End With
Next
RefreshLinksToPath = True

fRefreshLinks_End:
    Set collTbls = Nothing
    Set tdf = Nothing
    Set dbLink = Nothing
    Set dbCurr = Nothing
    Exit Function

fRefreshLinks_Err:
    RefreshLinksToPath = False
    Select Case Err
        Case 3059:

        Case Else:
            strMsg = "Error Information..." & vbCrLf & vbCrLf
            strMsg = strMsg & "Function: fRefreshLinks" & vbCrLf
            strMsg = strMsg & "Description: " & Err.Description & vbCrLf
            strMsg = strMsg & "Error #: " & Format$(Err.Number) & vbCrLf
            MsgBox strMsg
            Resume fRefreshLinks_End
    End Select
End Function