Solved

Access2000/2003: Find location of linked table

Posted on 2009-03-31
9
435 Views
Last Modified: 2012-05-06
I have a bunch of linked tables in a Access database. How do I find out the location of the linked tables?
(When I hover my mouse of the table it does not show anything.)
0
Comment
Question by:ouestque
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 11

Assisted Solution

by:TWBit
TWBit earned 50 total points
ID: 24030153
You can view them in the Linked Table Manager (Tools|Database Utilities)
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 24030221
or jump into the debug window and type
debug.?  CurrentDB().TableDefs("<table name>").Connect
 In fact, you can put that in a loop:

Sub SeeTableConnects()
  Dim tblDef As DAO.TableDef
 
  For Each tblDef In CurrentDb().TableDefs
    If tblDef.Connect<>"" then
     Debug.Print tblDef.Name & "  " & tblDef.Connect
    End If
  Next tblDef
 
End Sub
JimD
0
 
LVL 57
ID: 24030232
I mention that beacuse often the linked table manager doesn't have enough width to show you the full connect string.  But it is the easist way to look at the connections.
JimD.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 75
ID: 24030601
"I mention that beacuse often the linked table manager doesn't have enough width to show you the full connect string"

Or in many other places either.  Which I find mind boggling that this still exist in 2009!

mx
0
 

Author Comment

by:ouestque
ID: 24038251
When I try the Linked Table Manager it is blank. Why?
0
 
LVL 57
ID: 24038390
<<When I try the Linked Table Manager it is blank. Why?>>
  That would imply that no tables are linked; everything is local in the DB.
JimD.
0
 

Author Comment

by:ouestque
ID: 24038505
I know there is definately linked tables, because I ran the code you gave above and it showed the table location. As well. All tables in this database have an arrow to the left of them. (Thanks for the help.)

Nonetheless, is there a reason why my "Linked Table Manager" does not show anything?
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 24038668
<<Nonetheless, is there a reason why my "Linked Table Manager" does not show anything?>>
  Well then that means the Linked table manager is broken in some way.  I poked around and found this:
http://www.experts-exchange.com/Databases/MS_Access/Q_21159613.html
  on EE, which  talks about the same problem where there are multiple versions of Access on the same machine.  The author in that thread had mixed results with trying to re-register the .DLL with this command:
regsvr32.exe "C:\Program Files\Microsoft Office\Office11\accwiz.dll"
  This is covered in the MSKB article here:
http://support.microsoft.com/kb/835519
You can try that (Office 11 is Access 2003) as it won't hurt anything.  I'd also make sure your up to date on the service packs.
 I found this problem all over the net and there were a few instructions on manually trying some registery entries if the .DLL register didn't fix the problem, but it looks like everyone has had varying degrees of success.
 Let me know if that works for you.
JimD.
 
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 200 total points
ID: 24041252
Better try this - to be sure the linked tables are not 'hidden'

Tools>>Options... View Tab>>
Be sure that Hidden Objects and System Objects are checked !  If the linked table is hidden, I don't think you will see it in the LTM.

Also, paste this SQL in the the query designer and run - it will show any linked tables and the connect string:

SELECT MSysObjects.Name, MSysObjects.Connect, MSysObjects.Database
FROM MSysObjects
WHERE (((MSysObjects.Type)=6))
ORDER BY MSysObjects.Name;

mx

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question