Finding linked tables

elansolutionsltd · January 12th, 2006, 07:15 AM

When scrolling through the TableDefs collection using a For Each..Next loop, is there any way of identifying if the current table is linked or local, and if it is linked, reporting what it is linked to? I have browsed all the TableDef Methods I can find, but nothing seems to do what is required. I would be extremely grateful for any assistance or suggestions for an alternate method.

Cheers!

Bob Bedell · January 12th, 2006, 11:30 AM

Code:

Sub LinkedTableConnection()
   Dim dbs As DAO.Database
   Dim tdf As DAO.TableDef

   Set dbs = CurrentDb()

   With dbs
      For Each tdf In .TableDefs
         'Is the table a linked table?
         If tdf.Attributes And dbAttachedODBC Or tdf.Attributes And dbAttachedTable Then
            With tdf
               'Connect property contains path of link
               Debug.Print "Connect Property of " & .Name & " is: " & .Properties("Connect").Value
           End With
         End If
      Next tdf
   End With


End Sub

HTH,

Bob

Bob Bedell · January 12th, 2006, 12:06 PM

Evaluated dbAttachedTable and dbAttachedODBC attributes 'cause I wasn't sure what you're linked to.

A TableDef is flagged with the dbAttachedTable attribute if you're linked to a .mdb. Connect property looks like:

Code:

;DATABASE=C:\YourDatabase.mdb

A TableDef is flagged with the dbAttachedODBC attribute if you're linked to an ODBC data source via a DSN. Connect property looks like:

Code:

ODBC;DRIVER=SQL Server;SERVER=(local);APP=Microsoft Data Access Components;WSID=YourServer;DATABASE=YourDatabase

Bob

elansolutionsltd · January 12th, 2006, 06:11 PM

Many thanks, Bob.

Works like a dream and catches both ODBC and 'normal' linkages. You've made a fast-ageing programmer very happy

Bob Bedell · January 16th, 2006, 11:00 PM

Put this together for something I was palying with today and just thought I'd pass it along. The ADO version:

Code:

Sub ListAllTables(cnn As ADODB.Connection)

   Dim cat As ADOX.Catalog
   Dim tbl As ADOX.Table

   Set cat = New ADOX.Catalog
   cat.ActiveConnection = cnn

   cat.Tables.Refresh
   For Each tbl In cat.Tables
      Select Case tbl.Type
         Case "ACCESS TABLE"
            Debug.Print tbl.Name & " - Access table"
         Case "SYSTEM TABLE"
            Debug.Print tbl.Name & " - System table"
         Case "TABLE"
            Debug.Print tbl.Name & " - Native table"
         Case "LINK"
            Debug.Print tbl.Name & " - Linked table"
         Case "PASS-THROUGH"
            Debug.Print tbl.Name & " - ODBC DSN Linked table"
        End Select
    Next tbl

    Set tbl = Nothing
    Set cat = Nothing

End Sub

Bob