Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 12th, 2006, 07:15 AM
Authorized User
 
Join Date: Jul 2004
Posts: 46
Thanks: 0
Thanked 1 Time in 1 Post
Default Finding linked tables

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!


 
Old January 12th, 2006, 11:30 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old January 12th, 2006, 12:06 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old January 12th, 2006, 06:11 PM
Authorized User
 
Join Date: Jul 2004
Posts: 46
Thanks: 0
Thanked 1 Time in 1 Post
Default

Many thanks, Bob.

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



 
Old January 16th, 2006, 11:00 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
Linked Tables edojan Access 1 July 20th, 2006 10:49 AM
Getting data from linked tables SoC Classic ASP Databases 2 October 13th, 2005 08:18 PM
Linked Tables ru1 Access 1 September 21st, 2005 07:24 AM
help with two linked tables query footohi Access 2 August 25th, 2005 10:25 AM
Linked Tables ricmar Access VBA 6 July 28th, 2004 05:08 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.