重新链接数据库表：Access、VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16416747/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Relinking database tables: Access, VBA
提问by Katana24
I have a procedure that relinks all the tables in a database baed on whether or not they are a linked table. Currently this is set up to run automatically as it's set inside an AutoExec macro which calls the function.
我有一个程序可以根据它们是否是链接表来重新链接数据库中的所有表。目前，这被设置为自动运行，因为它是在调用该函数的 AutoExec 宏中设置的。
The code works but only if I close the database and reopen it. I know that this is because this needs to be done for the new links to take effect but is there anyway around this? Or, failing that, would it be better to make the VBA code close the database and reopen it?
该代码有效，但前提是我关闭数据库并重新打开它。我知道这是因为需要这样做才能使新链接生效，但无论如何都解决了这个问题？或者，如果失败了，让 VBA 代码关闭数据库并重新打开它会更好吗？
Thanks in advance for the feedback
提前感谢您的反馈
P.S. Here's the code, in case you're curious:
PS这是代码，以防你好奇：
'*******************************************************************
'* This module refreshes the links to any linked tables *
'*******************************************************************
'Procedure to relink tables from the Common Access Database
Public Function RefreshTableLinks() As String
On Error GoTo ErrHandler
Dim strEnvironment As String
strEnvironment = GetEnvironment
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strCon As String
Dim strBackEnd As String
Dim strMsg As String
Dim intErrorCount As Integer
Set db = CurrentDb
'Loop through the TableDefs Collection.
For Each tdf In db.TableDefs
'Verify the table is a linked table.
If Left$(tdf.Connect, 10) = ";DATABASE=" Then
'Get the existing Connection String.
strCon = Nz(tdf.Connect, "")
'Get the name of the back-end database using String Functions.
strBackEnd = Right$(strCon, (Len(strCon) - (InStrRev(strCon, "\") - 1)))
'Debug.Print strBackEnd
'Verify we have a value for the back-end
If Len(strBackEnd & "") > 0 Then
'Set a reference to the TableDef Object.
Set tdf = db.TableDefs(tdf.Name)
If strBackEnd = "\Common Shares_Data.mdb" Or strBackEnd = "\Adverse Events.mdb" Then
'Build the new Connection Property Value - below needs to be changed to a constant
tdf.Connect = ";DATABASE=" & strEnvironment & strBackEnd
Else
tdf.Connect = ";DATABASE=" & CurrentProject.Path & strBackEnd
End If
'Refresh the table links
tdf.RefreshLink
End If
End If
Next tdf
ErrHandler:
If Err.Number <> 0 Then
'Create a message box with the error number and description
MsgBox ("Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & vbCrLf)
End If
End Function
EDIT
编辑
Following on from Gords comments I have added the macro AutoExecmethod for calling the code below. Anyone see a problem with this?
继 Gords 评论之后，我添加了AutoExec用于调用下面代码的宏方法。有人看到这个问题吗？
Action: RunCode
Function Name: RefreshTableLinks()
采纳答案by Gord Thompson
The most common error in this situation is forgetting to .RefreshLinkthe TableDef but you are already doing that. I just tested the following VBA code which toggles a linked table named [Products_linked] between two Access backend files: Products_EN.accdb(English) and Products_FR.accdb(French). If I run the VBA code and then immediately open the linked table I see that the change has taken place; I don't have to close and re-open the database.
这种情况下最常见的错误是忘记.RefreshLink了 TableDef，但您已经这样做了。我刚刚测试了以下 VBA 代码，它在两个 Access 后端文件之间切换名为 [Products_linked] 的链接表：（Products_EN.accdb英语）和Products_FR.accdb（法语）。如果我运行 VBA 代码，然后立即打开链接表，我会看到更改已经发生；我不必关闭并重新打开数据库。
Function ToggleLinkTest()
Dim cdb As DAO.Database, tbd As DAO.TableDef
Set cdb = CurrentDb
Set tbd = cdb.TableDefs("Products_linked")
If tbd.Connect Like "*_EN*" Then
tbd.Connect = Replace(tbd.Connect, "_EN", "_FR", 1, 1, vbBinaryCompare)
Else
tbd.Connect = Replace(tbd.Connect, "_FR", "_EN", 1, 1, vbBinaryCompare)
End If
tbd.RefreshLink
Set tbd = Nothing
Set cdb = Nothing
End Function
I even tested calling that code from an AutoExec macro and it also seems to work as expected.
我什至测试了从 AutoExec 宏调用该代码，它似乎也按预期工作。
One thing you could try would be to call db.TableDefs.Refreshright at the end of your routine to see if that helps.
您可以尝试的一件事是db.TableDefs.Refresh在例程结束时立即致电，看看是否有帮助。
Edit
编辑
The issue here was that the database had a "Display Form" specified in its "Application Options", and that form apparently opens automatically beforethe AutoExec macro runs. Moving the function call for the re-linking code to the Form_Load event handler for that "startup form" seems a likely fix.
这里的问题是数据库在其“应用程序选项”中指定了一个“显示表单”，并且该表单显然在 AutoExec 宏运行之前自动打开。将重新链接代码的函数调用移动到该“启动表单”的 Form_Load 事件处理程序似乎是一个可能的解决方法。
