Solved

General Questions about Linked table in Access

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Normalization of a table 19 74
Create report using crosstab query 11 29
update all email addresses SQL 1 23
Why get error when delete all records on a sub-form 2 16
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

773 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