Access linked tables

uemt08k

New Member
Joined
Mar 20, 2005
Messages
12
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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) :
Code:
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

New Member
Joined
Mar 20, 2005
Messages
12
Thanks

:biggrin: 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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

Forum statistics

Threads
1,137,208
Messages
5,680,196
Members
419,889
Latest member
ballsofspartans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top