How can one search TableDefs for linked tables?

ms-access vba ado dao
StockB picture StockB · Dec 17, 2013 · Viewed 12.3k times · Source

Looping through a set of TableDefs, how can one determine if each TableDef represents a linked table, as opposed to a local table?

Answer

HansUp picture HansUp · Dec 17, 2013

For a linked table, the TableDef.Connect property contains the connection information. But for a native table, the .Connect property is an empty string.

So you can tell which are which by examining Len() of .Connect

Dim tdf As DAO.TableDef
With CurrentDb
    For Each tdf In .TableDefs
        If Len(tdf.Connect) > 0 Then
            Debug.Print tdf.Name, tdf.Connect
        End If
    Next
End With

That approach is simpler for me to remember than tdf.Attributes And dbAttachedODBC Or tdf.Attributes And dbAttachedTable, or the ADOX approach. It is also much more concise than the ADOX approach.

Related questions

Difference between ADO and DAO

This is not a question about which is better, but rather a question regarding why they differ functionally. The problem I was running into has been handled, but I am curious as to why this behavior is happening. Background - …

Read More
Exporting MS Access Forms and Class / Modules Recursively to text files?

I found some code on an ancient message board that nicely exports all of the VBA code from classes, modules and forms (see below): Option Explicit Option Compare Database Function SaveToFile() 'Save the code for all modules to files in …

Read More
Connection string syntax for Classic ADO / ODBC / Oracle 10g EZConnect

I'm trying to connect various VBA projects to an Oracle 10g back end using ADO (2.8) and no TNS. After various attempts, we've decided that the simplest series of steps for a clean installation include: Set up an Oracle Instant Client …

Read More