Linked table path

[GUIDO22]

I need to ascertain the current link path for my back end DB tables.
Using Application.CurrentDB.Name returns the current MDB file - but I need the DB file for the linked tables? Any ideas please?

THanks in advance.
Guido

 

[ghudson]

LINKED TABLES [4 = ODBC, 6 = Access]:
SELECT MSysObjects.Name, MSysObjects.Database
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") AND ((MSysObjects.Type) In (4,6)))
ORDER BY MSysObjects.Name;

ALL TABLES [1 = System & Access, 4 = ODBC & 6 = Access]:
SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") AND ((MSysObjects.Type) In (1,4,6)))
ORDER BY MSysObjects.Name;

I have code in my Browse [Find a directory or file] sample that will show you how to parse out the path and/or file name.

Searching the forum is a great way to discover and learn the answers to your Access programming questions.

 

[GUIDO22]

Thank you.

 

[map@freeola.net]

Hi ghudson
Thought your SQL code 11-14-2005, 04:48 AM "LINKED TABLES" is the best solution I've found to finding the path of backend tables. Thanks !!!
However can you put me right.
It works great in a query but when I put it in VBA get a runtime error 13. Here's code:
Dim IDx
Dim SQLText As String
Dim dbsA As Database
Dim R As Recordset
Set dbsA = CurrentDb
SQLText = "SELECT MSysObjects.Name, MSysObjects.Database"
SQLText = SQLText & " FROM MSysObjects WHERE (((Left([Name],1))<>" & Chr(34) & Chr(126) & Chr(34) & ") AND ((MSysObjects.Type) In (4,6)))"
SQLText = SQLText & " ORDER BY MSysObjects.Name;"
Application.Echo False
Set R = dbsA.OpenRecordset(SQLText) ' ERROR 13 HERE
If Not (R.BOF And R.EOF) Then '
IDx = R.Fields("CFData").Value

R.Close
Application.Echo True
End If

Can you put me right? Thanks in anticipation
mapline