[AccessD] A97 Linked Tables

In one of our apps, we eventually just started relinking all of the tables
because there were issues when we did not.  This is back with Access 97 so
the issue may be corrected now.  But the app still does this.

Bobby 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of MartyConnelly
Sent: Saturday, February 10, 2007 7:46 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] A97 Linked Tables

Some old notes on this

MichKa (Michael Kaplan) made a statement on usenet years ago. He said
"For linked tables, there is a LOT of info cached by Jet in the
link as an optimization. However, sometimes backend changes are
made and that cached info is not invalidated as it should be, and
it causes a huge perf hit as Jet tries things that fail (at which
point you hit bug#2, which is that it does not invalidate it here
either). I have seen cases where even RefreshLink would not
totally make this work right.

"The fix? If this is the problem? You should completely delete the
links in the frontend, then after making sure you have recently
compacted the backend, relink all the tables."


Also

In Access 2000, when a second and subsequent user tries to access a
shared backend database on the server, there seems to be a situation 
where Access
tries to perform a delete on the LDB file (which fails because another 
user is currently
in the file). This attempt is made about 15 times before silently 
failing and the
records are returned from the linked table.

To resolve this issue we need a persistent connection to the back-end
from each of the front-end workstations. This can be done using a bound 
form which
is always open or by keeping a recordset open at all times..

Maintaining persistent connections to linked tables could improve
performance significantly because it prevents Microsoft Jet from constantly
deleting, creating, and obtaining locking information from the other 
database's locking
information file.

Refreshing table links can also be quite slow

Refreshing the links to tables can be quite slow even in Access 97.
This can get much worse for the second and subsequent users into a 
shared MDB on a
server.  Once you've successfully refreshed the first table open a 
recordset based on that
table. Once you've finished refreshing all the links close that recordset.

Then open a bound form or keep this recordset open if so desired
depending on your preference for better overall performance.



Gustav Brock wrote:

>Hi John
>
>Good question.
>It mentions caching, multi-user access, and API calls to the mdb file but
what that implies is not clear.
>
>/gustav
>
>  
>
>>>>jwcolby at colbyconsulting.com 10-02-07 11:50 >>>
>>>>        
>>>>
>What this doesn't discuss is where the performance hit is encountered.  Is
>it when a lock is attempted on the BE?  If so then the "hold the be open"
>trick would "solve" the issue.  Is it every time a specific table is
>referenced in a query?  That would be much more serious since that would
>imply no method other than the ones discussed in this KB article would
work.
>
>John W. Colby
>Colby Consulting
>www.ColbyConsulting.com
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
>Sent: Saturday, February 10, 2007 3:10 AM
>To: accessd at databaseadvisors.com
>Subject: Re: [AccessD] A97 Linked Tables
>
>Hi Lambert
>
>Thanks for pointing this out. I've never heard of the problem with long
>filenames but it certainly may explain some experiences we have had through
>the years.
>
>/gustav
>
>  
>
>>>>Lambert.Heenan at AIG.com 09-02-07 23:19 >>>
>>>>        
>>>>
>Never heard of any problem with using UNC paths. You may be confusing that
>with using long filenames in paths.
>
>If you are linking to a file in "\\server\share\Some Long Path Name\And a
>SubFolder" then you can get a performance hit as the OS has to walk the
>folder path resolving long file names to 8.2 file names.
>
>To eliminate that problem you can use the short file names in the linked
>file path, like
>
>\\server\share\SomeLon~1\AndASu~1
>
>See http://support.microsoft.com/kb/891176/en-us
>
>Lambert
>