No, it's just a poor design.
You can look at the Connect property of the TableDef object if you have a
reference set to DAO:
Sub ListLinkedTablesUsingDAO(WhatDatabase As String)
Dim dbCurr As DAO.Database
Dim tblCurr As DAO.TableDef
Dim strOutput As String
Dim strSource As String
If Len(Dir$(WhatDatabase)) > 0 Then
Set dbCurr = OpenDatabase(WhatDatabase)
For Each tblCurr In dbCurr.TableDefs
If Len(tblCurr.Connect) > 0 Then
strOutput = tblCurr.Name & _
" linked to table " & _
tblCurr.SourceTableName & _
" in database " & tblCurr.Connect
Debug.Print strOutput
End If
Next tblCurr
dbCurr.Close
Set dbCurr = Nothing
End If
End Sub
You can query the (normally hidden) system catalog:
SELECT Name, Database, Connect, ForeignName
FROM MSysObjects
WHERE Type = 6
ORDER BY Name
You can query the ADOX Catalog:
Sub ListLinkedTablesUsingADOX(WhatDatabase As String)
Dim catCurr As ADOX.Catalog
Dim tblCurr As ADOX.Table
Dim prpCurr As ADOX.Property
Dim strOutput As String
Dim strSource As String
Const saLinkDS = "Jet OLEDB:Link Datasource"
Const saLinkPS = "Jet OLEDB:Link Provider String"
Const saRemTbl = "Jet OLEDB:Remote Table Name"
If Len(Dir$(WhatDatabase)) > 0 Then
Set catCurr = New ADOX.Catalog
catCurr.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & WhatDatabase & _
";User Id=Admin;Password="
For Each tblCurr In catCurr.Tables
If tblCurr.Type = "LINK" Or _
tblCurr.Type = "PASS-THROUGH" Then
strSource = tblCurr.Properties(saLinkDS)
If Len(strSource) = 0 Then
strSource = tblCurr.Properties(saLinkPS)
End If
strOutput = tblCurr.Name & _
" linked to table " & _
tblCurr.Properties(saRemTbl) & _
" in database " & strSource
Debug.Print strOutput
End If
Next tblCurr
Set catCurr = Nothing
End If
End Sub
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Primitive said:
I've had this problem awhile and haven't found an answer.
I have multiple linked databases and most are several folders deep. The
problem is I can only see part of the path and can't see the portion that
says which specific database a table is linked to.
I get something like "table(s:\folder\subfolder" before it runs out of
space. Is there a way to see the full path by resizing the dialog box or
any other way? Your help is appreciated.
