table is linked to what other Access database?

[Dan Williams]

I've inherited an Access database. Some of its tables are linked to
some other Access database. How can I tell what database these tables
are linked to?

Access 2000
Windows 2000

Dan Williams
danwPlanet

 

[John Spencer MVP]

Open the VBA window (Control + G)

In the immediate window type

?CurrentDb().TableDefs("NameofALinkedTable").Connect

and press return.

The path to the database should print out in the immediate window.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I've inherited an Access database. Some of its tables are linked to
some other Access database. How can I tell what database these tables
are linked to?

The quick and easy way is to hover the mouse over the tablename in the Tables
pane of the database window. The Connect string will appear as a tooltip.

 

[Peter Hibbs]

John,
Unfortunately that facility is not available in Access 2000 (which is
what the OP is using).

Dan,
Another option is to use the Linked Table Manager (Tools > Database
Utilities). The problem with this one is that if you have long
pathnames for the links they may not show up properly in the list, but
it may help.

HTH

Peter Hibbs.

 

[a a r o n _ k e m p f]

keep your data in SQL Server.

run profiler when you want to see who is using it

 

[Dirk Goldgar]

I've inherited an Access database. Some of its tables are linked to
some other Access database. How can I tell what database these tables
are linked to?

Here's a quickie routine you can call from the immediate window to get a
list of all the linked tables and their connection strings, or else to check
just a single table:

'----- start of code -----
Sub ListLinkedTables(Optional pTableName)

On Error GoTo Err_Handler

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb

If IsMissing(pTableName) Then
For Each tdf In db.TableDefs
If Len(tdf.Connect) > 0 Then
Debug.Print tdf.Name, tdf.Connect
End If
Next tdf
Else
Set tdf = db.TableDefs(pTableName)
If Len(tdf.Connect) > 0 Then
Debug.Print tdf.Name, tdf.Connect
Else
Debug.Print tdf.Name; " is not a linked table."
End If
Set tdf = Nothing
End If

Exit_Point:
Set db = Nothing
Exit Sub

Err_Handler:
If Err.Number = 3265 Then
Debug.Print pTableName; " is not a table in this database."
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End If
Resume Exit_Point

End Sub
'----- end of code -----

 

[Tom Wickerath]

Hi Dan,

You can use a query to return this information:

To reveal all linked paths:

SELECT Left(Database,255) AS [Linked Databases],
Count(MsysObjects.Database) AS [Number of Tables]
FROM MsysObjects
WHERE (MsysObjects.Type)<>9
GROUP BY Left(Database,255)
HAVING Left(Database,255) Is Not Null;


If you want to see each table name, use this instead:

SELECT Left(Database,255) AS [Linked Databases], Name, ForeignName
FROM MsysObjects
WHERE (((MsysObjects.Type)<>9))
GROUP BY Left(Database,255), Name, ForeignName
HAVING (((Left([Database],255)) Is Not Null))
ORDER BY Name, ForeignName;

Notes:
Make sure to compact the database first, to avoid revealing table links that
have already been deleted.

Grouping on the first 255 characters is required for Access 97, but not
later versions, since [Database] is a memo data type. (Contributed by Doug
Steele).

The criteria, WHERE (MsysObjects.Type)<>9, is used to filter out any table
constraints created with ADO. (Contributed by Dirk Goldgar).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________


I've inherited an Access database. Some of its tables are linked to
some other Access database. How can I tell what database these tables
are linked to?

Access 2000
Windows 2000

Dan Williams
danwPlanet

 

[David W. Fenton]

Unfortunately that facility is not available in Access 2000 (which
is what the OP is using).

Another option is to use the Linked Table Manager (Tools >
Database Utilities). The problem with this one is that if you have
long pathnames for the links they may not show up properly in the
list, but it may help.

I'm wondering why nobody is suggesting using the Immediate window?

?CurrentDB.TableDefs("MyTableName").Connect

Or, for that matter, opening MSysObjects and looking for the table
and checking the Connect field's value?

 

[Dan Williams]

David, in my thread via Google Groups, John Spencer's showed the
Immediate Window approach in the first reply. That's what I've now
used. Thanks, all, for the info, which I'll save for thr future.
Dan