Linked Table Source 3

[paulkeys]

Hi,

I have a few linked tables in huge access databases, and i'm trying to determine the table sources.

Is there a way to access this info using vba, as i'm trying to avoid having to get access documenter installed by our desktop support bods.

Any help greatly appreciated

Cheers
Paul

 

[jadams0173]

It can be done in VBA but if you just want to take a look at the sources you can view them in the linked table manager.

Tools>Database Utilities> Linked Table Manager

HTH

I tried to have patience but it took to long! -DW

 

[Remou]

How about:

Dim tdf As TableDef
Dim db As Database

Set db = CurrentDb
For Each tdf In db.TableDefs
    If tdf.Connect <> "" Then
        Debug.Print tdf.Connect
        Debug.Print tdf.SourceTableName
    End If
Next

 

[Ed2020]

Or in a query:

SELECT [MSysObjects].[Name], IIf([MSysObjects]![Database]<>"",[MSysObjects]![Database],CurrentDB.Name) AS Location FROM MSysObjects WHERE ((([MSysObjects].[Name]) Not Like "MSys*" And ([MSysObjects].[Name])<>"tblRestrictedText") And (([MSysObjects].[Type])=1)) Or ((([MSysObjects].[Name]) Not Like "MSys*" And ([MSysObjects].[Name])<>"tblRestrictedText") And (([MSysObjects].[Type])=6)) ORDER BY [MSysObjects].[Name];

Ed Metcalfe.

Please do not feed the trolls.....

 

[paulkeys]

Cheers for the responses guys

Unfortunately i was using 97 so no linked table manager available - but the vba worked a treat.

Stars all round!

Cheers
Paul

 

[Binnit]

Another quick way to do it is:-

Open the linked table you want to find the source in Design mode
You will get the standard "cannot edit linked table" message
Click OK to open anyway

Then: on the first field right click and select properties - the source Database path will be displayed.


Happiness is...not getting what you want but wanting what you have already got