Relinking database tables: Access, VBA

Question

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()

Gord Thompson · Accepted Answer · 2013-05-07T15:00:38.700

6

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.

Edit

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.

edited May 07 '13 at 15:00

answered May 07 '13 at 11:06

Gord Thompson

116,920
32
215
418

I tried what you said - refresh the table links - and it doesn't make a difference - i still have to close it and reopen it. I can't understand why. I guess the only solution would be to reopen the database then? – Katana24 May 07 '13 at 12:01
1

@Katana24 Maybe it's something to do with the code running in the AutoExec macro. Try making a change that will require the links to actually be updated, then open the database, and if the new links didn't "take" then try running *just your RefreshTableLinks function* again. If that works then you can investigate why they didn't "take" the first time (when run from AutoExec). – Gord Thompson May 07 '13 at 12:42
I think you're right Gord - it must have something to do with how it's being called in the AutoExec - because when i package it in another macro and run it it works. It also works when I call if from a form load - I've edited my answer to display how I'm calling it in the AutoExec macro – Katana24 May 07 '13 at 12:54
Gord i identified why i was getting the error - it wasn't really to do with my code - it worked fine even in the autoexec macro. The problem lay in the fact that AutoExec isn't getting called first - a form is getting called first – Katana24 May 07 '13 at 14:51
@Katana24 Aha! Good to know. Would that the "Display Form" specified in the "Application Options" for the "Current Database"? – Gord Thompson May 07 '13 at 14:54
Exactly - i was about to post another question asking how to get around this but I think the previous developer set it up like this >:/ thanks for your help – Katana24 May 07 '13 at 14:56

Relinking database tables: Access, VBA

1 Answers1

Edit

Linked