Listing all linked tables and their source databases 2

[Adamba]

Hiya all!

I wonder if anyone could help me a problem i am having.

I am trying to create a database which will sort through all the tables in it and give me a list of all the Linked tables and the database that they originate from. Is this possible?

I am using a form with various variables and a listbox which will hopefully list all the linked tables.

Thanks in advance!


Adam

 

[MichaelRed]

MSysObjects is the (usually hidden) table of system schema. It includes references to all objects in the database like the link source information.

While it varies a bit by the release, you need to find the menu item which toggles the viewing of Hidden objects (ver 2K, it is [Tools \ Options \ View]. Once you can view the hidden objects, you can simply open the table and browse the contents. MUCH of it will not make any particular sense, particularly at first, but you can see the information you are after rather clearly. Armed with the information on the content and structure, you can easily generate a query to obtain the recordset if interest (all the linked tables). The Query does not need the hidden objects to be visible, so after you are familiar with the process you can re-hide them and reference the table just like any tabledef/recordset.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[kewo99]

Here is the SQL for a query which will display the information:

SELECT msysobjects.Name, msysobjects.Database, msysobjects.ForeignName
FROM msysobjects
WHERE ((Not (msysobjects.Database) Is Null));

Ken

 

[Adamba]

Hiya!

Thanks Michael & Kewo youve been a great help!

Can finally put my problem to bed!

Many Thanks,

Adam