title: Relinking database tables: Access, VBA
body: <p>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. </p>
  
  <p>The code works but <strong>only if I close the database and reopen it</strong>. 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? </p>
  
  <p>Thanks in advance for the feedback</p>
  
  <p>P.S. Here's the code, in case you're curious:</p>
  
  <pre><code>'*******************************************************************
  '*  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 &amp; "") &gt; 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=" &amp; strEnvironment &amp; strBackEnd
                      Else
                          tdf.Connect = ";DATABASE=" &amp; CurrentProject.Path &amp; strBackEnd
  
                      End If
  
                      'Refresh the table links
                      tdf.RefreshLink
  
                  End If
  
              End If
  
      Next tdf
  
  ErrHandler:
  
   If Err.Number &lt;&gt; 0 Then
  
      'Create a message box with the error number and description
      MsgBox ("Error Number: " &amp; Err.Number &amp; vbCrLf &amp; _
              "Error Description: " &amp; Err.Description &amp; vbCrLf)
  
  End If
  
  End Function
  </code></pre>
  
  <p><strong>EDIT</strong></p>
  
  <p>Following on from Gords comments I have added the macro <code>AutoExec</code> method for calling the code below. Anyone see a problem with this?</p>
  
  <pre><code>Action: RunCode
  Function Name: RefreshTableLinks() 
  </code></pre>