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.
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?
Thanks in advance for the feedback
P.S. Here's the code, in case you're curious:
'*******************************************************************
'* 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 AutoExec
method for calling the code below. Anyone see a problem with this?
Action: RunCode
Function Name: RefreshTableLinks()
The most common error in this situation is forgetting to .RefreshLink
the 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.
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.
One thing you could try would be to call db.TableDefs.Refresh
right at the end of your routine to see if that helps.
The issue here was that the database had a "Display Form" specified in its "Application Options", and that form apparently opens automatically before the 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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With