Find where SQL table is linked to Access DB 1

[djj55]

Hello, I have several databases create using Access 2002 SP3 (around 40).
What I want is a way to find which Access databases have a link to a specific SQL table.

Is there a way to do this short of going to all 40 databases?

Thanks
djj

 

[Remou]

You could use a FileSystemObject, FileSearch or Dir to return a list of databases and ADOScemas to check if the table exists.

For example:

  Function ExternalTableExists(TableName, DBFullPath)
  Dim cn As New ADODB.Connection
  Dim rs As ADODB.Recordset
   
      Set cn = CreateObject("ADODB.Connection")
      Set rs = CreateObject("ADODB.Recordset")
      
      cn.Open "Provider  =Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & DBFullPath & ";User Id=admin;Password=;"
      
      Set rs = cn.OpenSchema( _
          adSchemaTables, Array(Empty, Empty, TableName))
      ExternalTableExists = Not rs.EOF
      
      rs.Close
      Set rs = Nothing
      cn.Close
      Set cn = Nothing
  End Function

From:

http://wiki.lessthandot.com/index.php/Table,_Query,_Form,_Report,_Module_Exists

http://lessthandot.com

 

[djj55]

Thank you that looks like it. Now if they have the table name the same.

Thanks
djj

 

[Remou]

Here is a DAO option.

Sub ExternalTableExists2(TableName, DBFullPath)
Dim rs As DAO.Recordset
 
    strSQL = "SELECT ForeignName, [Name] FROM MSysObjects " _
        & "IN '" & DBFullPath & "' " _
        & "WHERE (ForeignName='" & TableName _
        & "' Or [Name]='" & TableName & "') " _
        & "AND Type=6"
        
    Set rs = CurrentDb.OpenRecordset(strSQL)
    
    Do While Not rs.EOF
        Debug.Print rs.Fields(0), rs.Fields(1)
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
End Sub

http://lessthandot.com

 

[djj55]

Thanks that is what I needed. I tweaked it to give a general list:

SELECT Type, Connect, ForeignName, Name 
FROM MSysObjects 
IN 'P:\mydatabase.mdb'
WHERE Type = 4;

Note for other users the Type = 4 gives the liked tables while the Type = 6 gives local tables.

Thank you again,
djj

 

[Remou]

Note for other users the Type = 4 gives the liked tables while the Type = 6 gives local tables.

Not quite:

Tables are 1,
Linked tables are 6,
and ODBC-linked tables are 4.

http://lessthandot.com

 

[djj55]

Thanks for the clarification. As you can tell I was after ODBC-linked tables.

djj

 

[Remou]

Yes. And I should have been more careful

http://lessthandot.com