Checking what linked tables belong to what DB in a SQL server

[AT76]

Hi,

Does anyone know if there exits an app that checks linked tables in an access app and tells you what the corresponding database in a SQL server is?

Thanks for any feedback!

 

[cmmrfrds]

You could look at the properties in the linked table with a routine something like.

Function catalogTC()
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table
Dim cn As ADODB.Connection
Dim cl As Column
Dim pp As ADOX.Property

Set cg.ActiveConnection = CurrentProject.Connection

For Each tb In cg.Tables

If tb.Type = "PASS-THROUGH" Then
Debug.Print "table name = "; "-------"; tb.Name; "--------"; tb.Type
For Each pp In tb.Properties
Debug.Print "property name = "; pp.Name
Debug.Print "property value = "; pp.Value
Next
End If
Next

End Function

 

[AT76]

Thank you cmmrfrds!

I playing around with your code. Is there a way to get just the SQL database NAME of the linked table?

Thanks for your help!

 

[cmmrfrds]

Find out which property the table name is in and just display that property. All the information is in the properties.

 

[AT76]

Right... I did find that. But I'm having trouble displaying with some of the linked tables. I have a group of them that belong to the same database. The problem that I run into is that only some of them have the property that displays the name of the database they belong to. The others do not. Is there another way to identify a link tables corresponding database?

Thank you.

 

[cmmrfrds]

Are all of the tables sql server linked tables, or are some linked to another database type such as Access. If so, the types and property names will be different.

 

[JerryKlmns]

Ofcourse there is always the MSysObjects table where you could find info in the field Connect & Database, ForeignName for linked tables.

 

[AT76]

cmmrfrds,

Not all the tables are sql server linked tables. That is the problem I'm running into. Is there a method I can use to get me the name of the SQL linked table's database?

Thanks!

 

[cmmrfrds]

In my example, I showed the type for sql server linked tables. Access linked tables would be a different type.
tb.Type = "PASS-THROUGH" '- for sql server tables.
tb.Type = "LINK" '- for Access tables.

As Jerry Klmns pointed out the msysobjects table should have all the information also. This is a hidden table in Access, so to view, go under options and select the check mark for hidden objects.