Solved

General Questions about Linked table in Access

Posted on 2009-05-18
3
216 Views
Last Modified: 2012-05-07

I have an external Oracle table, used in Access as a Linked table using ODBC. Is this linked table always connected? Does it mean it's not a good design practice to use linked table in the application because it's always connected? When I added one clumn in Oracle table, in Access forms, I have to right click the access-Oracle linked table and right click  and choose linked table manager, then choose to update the table, then the linked Oracle table will get updated. Could anyone further explain why it is like this? Is there any command to do it in VB editor for this kind of update (form.refresh?)? Thanks a lot.
0
Comment
Question by:heyday2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 300 total points
ID: 24410692
CurrentDb("LinkedTableName").RefreshLink

will update the changes to the server table for you.
The reason you have to do this is because Access maintains a local cache of the server table's schema (it means it doesn't need to constantly read the schema from the server when all you want to know is the the schema without data (which designing a form for example). Equally - you need to be able to apply an index to your local linked table if so required, so it's very much a local object - which wraps external calls.
The local database maintains the link (which is effectively a wrapping via your ODBC driver) to the server database upon demand.
A connection isn't always open - but once you've requested data from that server a connection has been established and it's then up to Access to persist it or drop when it sees fit. You can use a code or form item to maintain the connection in the goal of a persisted connetion requires less overhead (no instantiation and connection) however bear in mind that you're also consuming some server resources with that persistance.  It's a trade off.
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 24410847
As to "good design practice" - that would depend on what your application is intended to do. In general, linked tables are a common and accepted design practice, and are in use in many, many Access apps. Assuming your source database is properly constructed, linked tables should provide good response and performance. Many consider linked tables to be poor practice, but with Access I (along with most Access developers) consider linked tables to be a perfectly reasonable design method.

One alternative to linked tables is to use "unbound" forms and such. Unbound methods would require you to handle all data manipulation (i.e. updates, deletes,etc) which negates many of the positive aspects of using Access. Even with unbound methods you' might still consume server resources in the same manner as Leigh described, depending on how you manage your connections and such.
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 300 total points
ID: 24411008
Yes the knee-jerk reaction to linked tables by mainly is fuelled by overstatement and misinformation.
There's no substitute for direct server connection and execution through code - but for a fast, convenient and pretty robust mechanism they do very well.
It's more related to how you work with them than failings in the underlying technology itself.
Making poor, lazy, ill-informed requests or a poorly designed server database will produce poor results.
Well crafted requests upon an efficiently designed database will product effective results.
When you reach an impass with them (i.e. you need to execute a query which would be filtered locally) then you have other options - not least of which is the still native passthrough.
If you're procifient at coding then direct server access (and ultimately unbound forms as Scott suggests) can offer greater efficiency but as always, with tradeoffs.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views 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 Access…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

690 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