Audit of linked tables

[adale]

Over time we have built a spider web of databases connected via linked tables. Now we are about to build a new SQL application to replace the hub of the old spider web, I hae to determine what Access databases are linked to the old hub. Maybe I'm lazy, but i'd rather not look at all 40+ databases to see what they link to.
Is there a tool or method that can whizz through various directories and tell me what tables are in each of the databases, and if they are linked what they are linked to?

 

[samaba]

If you have the resources to use Access 2003 - there is a new feature called Object Dependencies. It will allow you to see both what objects depend on it; as well as what objects it depends on.

I have not had a chance to see how it works with the linked tables - and don't currently have that version with me to try it.

The following is a link to an article about the new features.

http://www.databasejournal.com/features/msaccess/article.php/10895_3403871_1

Good luck on the big project!

 

[PWise]

this code will give you the name of the database
Dim mydb As Database
Dim Tds As TableDefs
Dim Td As TableDef
Set mydb = CurrentDb
Set Tds = mydb.TableDefs
For Each Td In Tds
Debug.Print Td.Connect
Next

 

[adale]

Thankyou both. I'm going with the code version as I have a table of the 140 (and rising) databases and their locations. So I am writing code to check each one out using what you have suggested.

 

[PWise]

after a good nite sleep i came up with a faster way to get a list of all databases where the tables are linked to just run this query
SELECT DISTINCT Database
FROM MSysObjects
WHERE AND Flags=2097152 AND Type=6

 

[adale]

That works even better.

On yesterday's suggestion, I have written and run the code we wrote about and have all my answers now.

I've got a table with all the database locations and names, the code goes into each one returns all the tables and whether they are linked. All the data is thrown into another table which I can then trawl through to see what's what. It's rough, but what works works

Thankyou again for your help.


Function GetDatabaseNames()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = "SELECT * FROM query1 ORDER BY FullName"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

Do
GetDependencies rs(0)
rs.MoveNext
Loop While Not rs.EOF

End Function

Function GetDependencies(strConnection As String)
On Error Resume Next

Dim mydb As Database
Dim Tds As TableDefs
Dim Td As TableDef
Dim rsAnswer As Recordset

'Set mydb = CurrentDb
Set mydb = OpenDatabase("" & strConnection & "")
Set Tds = mydb.TableDefs

With DoCmd
.SetWarnings False
For Each Td In Tds
strTableName = RemoveIllegals(Td.Name)
Select Case Len(Td.Connect)
Case 0
.RunSQL "INSERT INTO DatabaseContent ( FullName, TableName, LinkInfo ) " _
& "VALUES('" & strConnection & "', '" & strTableName & "', 'N/A')"
Case Else
.RunSQL "INSERT INTO DatabaseContent ( FullName, TableName, LinkInfo ) " _
& "VALUES('" & strConnection & "', '" & strTableName & "', '" & UCase(Td.Connect) & "')"
End Select
Next
.SetWarnings True
End With
mydb.Close
End Function


Function RemoveIllegals(strString As String) As String
Dim y As Integer

For y = 1 To Len(strString) 'Take out illegal characters
If Mid(strString, y, 1) = "'" Then
Mid(strString, y, 1) = " "
End If
Next y
RemoveIllegals = strString
End Function