Solved

General Questions about Linked table in Access

Posted on 2009-05-18
3
178 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now