Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MS Access Linked Tables

Posted on 2014-03-12
4
Medium Priority
?
877 Views
Last Modified: 2014-03-12
See attached.

2 questions

(1) Note the table Customer. I'm guessing that the arrow and green ball to the left of table name mean linked to something else. How can I determine what "something else" is?

(2) If I "open" the table, it takes quite a while and it looks like what is there is NOT all the fields in the target table, Does this "link" have to be updated if other daya is added to the "source" table? If so, how do I do that?

Thanks
linked-access-tables.jpg
0
Comment
Question by:Richard Korts
4 Comments
 
LVL 40

Accepted Solution

by:
PatHartman earned 1200 total points
ID: 39924355
The globe indicates an ODBC connection.  If you leave your mouse pointer over the table name, you will see the connection string which tells you what server it is connected to.  You could query the MSysObjects table.  The query below selects all tables whether they are linked or not.  Change the selection criteria to suit yourself.

SELECT MSysObjects.Connect, MSysObjects.Database, MSysObjects.ForeignName, MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "MSys*") AND ((MSysObjects.Type)=1 Or (MSysObjects.Type)=4 Or (MSysObjects.Type)=6))
ORDER BY MSysObjects.Connect, MSysObjects.Database, MSysObjects.ForeignName, MSysObjects.Name;

Open in new window


When you make structural changes to a linked ODBC table, Access doesn't always "see" the changes.  Sometimes you will receive an error message but not always so best practice is to relink any time you make a schema change to a linked table.
0
 
LVL 20

Assisted Solution

by:GrahamMandeno
GrahamMandeno earned 800 total points
ID: 39924367
The icon indicates it is a linked table from an ODBC source.  Hovering the mouse over it may give you some information.  If you need more, go to the Immediate Window (Ctrl-G) and type:
?CurrentDb.TableDefs("Customer").Connect
and then
?CurrentDb.TableDefs("Customer").SourceTableName

These commands will display the connect string (which gives the type and location of the external database) and the name of the source table in that database.

I'm not sure what you mean by "NOT all the fields in the target table".  You could try refreshing the link:

CurrentDb.TableDefs("Customer").RefreshLink

Best wishes,
Graham Mandeno [Access MVP 1996-2014]
0
 

Author Comment

by:Richard Korts
ID: 39924387
How do you relink or refresh the link?

Thanks
0
 
LVL 85
ID: 39924554
How do you relink or refresh the link?
You use the Linked TAble Manager, or you write code to refresh/recreate the links.

Looks like you're using 2003, so you'd find that on Tools - Database Utilities.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

577 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