Solved

General Questions about Linked table in Access

Posted on 2009-05-18
3
202 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
  • 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 84

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

839 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