Verify Linked Tables in Your Microsoft Access Application

Provided by the FMS Development Team

Ever had a user move a backend Microsoft Access database or delete it?

Here is a routine you can use to ensure that the linked table actually exists in the location specified using ADOX. Paste the function below into a new module and call it.

This comes from Total Visual SourceBook's modADOJetADOX module. There's also an equivalent set of code to use DAO in CJetLinkedTables and CJetDatabase classes.

Required References: ADO Extensions for Data Definition Language and Security (ADOX)

Function to Verify Linked Tables using ADOX

Public Function ADOXTestLinkedTable(cnnConnection As ADODB.Connection, strTable As String) As Boolean
  ' Comments : Tests the specified linked table to see if its link is valid
  ' Params   : cnnConnection - open ADODB connection to the Jet Database
  '            strTable - name of the table to test
  ' Returns  : True if the link is valid, False otherwise
  ' Source   : Total Visual SourceBook

  Dim catTmp As New ADOX.Catalog
  Dim tblTmp As New ADOX.Table
  Dim strTmp As String
  Dim lngSaveErr As Long
 
  On Error GoTo PROC_ERR

  ' Open the catalog by setting its ActiveConnection property
  catTmp.ActiveConnection = cnnConnection

  ' Set a pointer to the table
  Set tblTmp = catTmp.Tables(strTable)

  If tblTmp.Properties("Jet OLEDB:Create Link") = True Then
    ' It is a linked table so try to open it by getting the name property of one it its fields. 
    ' If the table's link is not valid (for example, the database it is pointing to is moved, deleted, or renamed) this call fails. 
    ' We disable error handling to handle a potential failure.
    On Error Resume Next
    strTmp = tblTmp.Columns(0).Name
  
    ' Save the error number
    lngSaveErr = Err.Number
  
    ' Re-enable error handling
    On Error GoTo PROC_ERR

    ' If the save error number is not 0, an error occurred and we can assume that the link is invalid
    ADOXTestLinkedTable = (lngSaveErr = 0)
  End If
 
  ' Close the catalog to release resources
  Set catTmp = Nothing

PROC_EXIT:
  Exit Function

PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , "ADOXTestLinkedTable"
  Resume PROC_EXIT
End Function

To call the above function, all you need to do is open a connection to the database.

Function TestLink()
  Dim cnn As ADODB.Connection

  Set cnn = New ADODB.Connection
 
  ' Open the connection
  With cnn
    .CursorLocation = adUseServer
   .Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Northwind.mdb"
  End With

  ' Example code for ADOXTestLinkedTable with status in Immediate Window
  Debug.Print ADOXTestLinkedTable(cnn, "Categories")
End Function