[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

General Questions about Linked table in Access

Posted on 2009-05-18
3
Medium Priority
?
230 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 1200 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 800 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 1200 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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use 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.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 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