Linked Table Attributes (1 Viewer)

[Lister]

Just a little help if anyone has the time and this wee project can be put to bed.

I am trying to create a function that will loop through my tables and select only those that are LINKED TABLES.
This I think I have done with the below code. (Unfinished) which tells me which of my tables are linked.

The bit that I am missing is that I want to report the LINK STRING.

So if it’s a linked table I would get something like (Debug.Print)

tblAccounts “S:\Projects\Project Accounts\Accounts0607\Admin.mdb”
etc

Well I hope that’s possible. What I don’t know and can’t seem to find is the Link Table String in the TableDefs. Am I even looking in the right place?

If someone could point me in the right direction that would be great.

Thanks

   Function ShowTableAttribs()
      Dim DB As Database
      Dim T As TableDef
      Dim TType As String
      Dim TName As String
      Dim Attrib As String
      Dim I As Integer

      Set DB = CurrentDb()

      For I = 0 To DB.TableDefs.Count - 1
         Set T = DB.TableDefs(I)
         TName = T.Name
         Attrib = (T.Attributes And dbSystemObject)
         Debug.Print TName & IIf(Attrib, ": System Table", ": Not System Table")
      Next I
   End Function

 

[gemma-the-husky]

if a table is linked it has a connect string, therefore the syntax

dim tdf as tabledef

for each tdf in currentdb.tabledefs
if len(tdf.connect)>0 then etc
next tdf

will select the linked tables. the connection string is different depending on access link, sql link etc.

 

[Lister]

As I am only working with ms access mdb files I dont really care about the link to "other" engine types (SQLServer etc). So I am just looking for the string property per linked table. It must be there as you can see the START of it in the link table manager.

Thanks for your comments but I am still looking.

 

[Lister]

knocked the Bastard off!

Got it.

Just one line of code.
So for interest sake.

   Function ShowTableAttribs()
      Dim DB As Database
      Dim T As TableDef
      Dim TType As String
      Dim TName As String
      Dim Attrib, strConn As String
      Dim I As Integer

      Set DB = CurrentDb()

      For I = 0 To DB.TableDefs.Count - 1
         Set T = DB.TableDefs(I)
         TName = T.Name
         Attrib = (T.Attributes And dbSystemObject)
         strConn = DB.TableDefs(TName).Connect
         Debug.Print TName & IIf(Attrib, ": System Table", ": Not System Table")
         Debug.Print strConn
      Next I
   End Function

 

[gemma-the-husky]

the dbs connect string holds the full connection info. If the string is blank, then you have a local Jet table. If not it is a linked table. My code did virtually the same as yours.

 

[Lister]

I see that now, what I was missing was the .Connect of the TableDefs object.
Just didn't know what I was looking at. Thanks for taking the time to post Gem'

 

[Pat Hartman]

You can do the same thing with a query against the MSysObjects table. There are three types for tables 1 = local Access tables, 4 = linked ODBC tables, and 6 = linked Access tables.

When using this method you need to exclude tables that start with "~" and that start with "MSys".