Access linked tables

[uemt08k]

I need a way using VBA to create a list of all the linked tables in a given database and their link information. The information can be seen on screen with the linked table manager but I need to have it placed in a table.

 

[Andrew Fergus]

Hi

I have assumed you have a table in which you want to store the details of the linked table and the table name is 'LinkedTables' and it contains the fields 'TblName', 'fromDatabase' and 'fromTable'. The following VBA code will find the linked tables and insert these 3 details into this table (after first clearing the contents of that table) :

Private Sub Command11_Click()

Dim strSQL As String

DoCmd.SetWarnings (False)
  
strSQL = "DELETE * FROM LinkedTables"

DoCmd.RunSQL (strSQL)

strSQL = "INSERT INTO LinkedTables (TblName, fromDatabase, fromTable) " & _
    "SELECT MSysObjects.Name, MSysObjects.Database, MSysObjects.ForeignName " & _
    "FROM MSysObjects WHERE (((MSysObjects.Type)=6));"

DoCmd.RunSQL (strSQL)

DoCmd.SetWarnings (True)

MsgBox "Linked table names copied!", vbInformation, "Done"

End Sub

My code was attached to a button on a form - there is nothing stopping you from using this code in other ways. Make sure you change the code to match your actual table and field names.

HTH, Andrew

 

[uemt08k]

Thanks

I have been needing this for a while and never could figure it out. Now that you have put me onto the MSys tables I have found all kinds of interesting information. Again thank you!

 

[Richard Schollar]

Hi Andrew

I realise what I'm about to ask is only remotely connected to the thread of this post, but is there any way (that you are aware of) of doing effectively the above but with an SQL Server Db? The reason I ask is because we have SQL Server atr work, the Db administrators aren't too keen on the functional areas having access to the details of the Db structure and I would love to see how their Db is linked together.

Best regards

Richard

PS Please excuse me if I'm being unreasonable

 

[Andrew Fergus]

Hi Richard
I don't have any experience with that but I found this webpage that describes the SQL server system tables:
http://www.mssqlcity.com/Articles/Undoc/SQL70UndocTbl.htm
Good luck in your quest!
HTH, Andrew

 

[Richard Schollar]

Thanks Andrew - I shall see how I can apply it to my work environment on Monday

Richard