Solved

Access2000/2003: Find location of linked table

Posted on 2009-03-31
9
424 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 - Access MVP) 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

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