Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

Access2000/2003: Find location of linked table

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
ouestque
Asked:
ouestque
  • 4
  • 2
  • 2
  • +1
4 Solutions
 
TWBitCommented:
You can view them in the Linked Table Manager (Tools|Database Utilities)
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
ouestqueAuthor Commented:
When I try the Linked Table Manager it is blank. Why?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
ouestqueAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now