Solved

Access2000/2003: Find location of linked table

Posted on 2009-03-31
9
432 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

831 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