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.
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
strSQL = "DELETE * FROM LinkedTables"
strSQL = "INSERT INTO LinkedTables (TblName, fromDatabase, fromTable) " & _
"SELECT MSysObjects.Name, MSysObjects.Database, MSysObjects.ForeignName " & _
"FROM MSysObjects WHERE (((MSysObjects.Type)=6));"
MsgBox "Linked table names copied!", vbInformation, "Done"
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.
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.