Display the path of a linked table

[Guest]

How can I display the path of a linked tabled through VB. I am using Access 97.

 

[chris.nebinger]

CurrentDB.Tabledefs("TableName").Connect


Chris Nebinger

 

[Guest]

I'd like to do the same thing. Where do I type this command?

 

[Guest]

I entered the command in a Msgbox and it returned a long string. However, I
cannot view the string while I type it in a table field because I must first
close the message box. Can I view the path of a linked table in a report or
query that I can copy-n-paste from?

 

[chris.nebinger]

Yes, you can, but it takes a little workaround. First, create a Public
Function to return the name of the linked table:


Public Function LinkedTableSource(strTableName As String) As String
On Error Resume Next
LinkedTableSource = CurrentDb.TableDefs(strTableName).Connect
End Function

Now, you can use that function in the SQL of a query:


SELECT MSYSOBJECTS.Name, LinkedTableSource([name]) AS ConnectInfo
FROM MSYSOBJECTS
WHERE MSYSOBJECTS.Type=6


Note: Type = 6 is the criteria for only selecting linked tables in
Access.

You can also use this function in a report, form, etc. I didn't
include any handling of errors, you might want to in your application.


Chris Nebinger