MS Access Multiuser - Access performace using a local linked backend verses a mapped drive

Author

Access performace using a local linked backend verses a mapped drive

deke

2005-08-11, 1:29 pm

Hello, This is my first post, although I use this site daily to look up
things. I have been an Access programmer for 10 years now and consider
myself somewhat proficient.

I have a multi-user access database where there is a front-end on each
of the 4 client PCs and a backend on the server. I also run one of the
front ends on the server. All clients have a mapped drive (including
the server to itself) to the server and the front-ends use it for
linking to the tables.

We notice that once more than one person opens the database,
performance decreases significantly. I noticed if use the front-end
that is on the server and link the tables to the local drive (where the
backend resides) the performance is back to normal no matter how many
people are using it. Once I link the tables back using the mapped
drive the performance is slow again. I also bench-marked this using
the linked table manager and refreshed the links both with the local
drive and the mapped drive. Local took a couple of seconds and mapped
took almost a minute to refresh the 6 tables. I also could reproduce
this with another Access database running on the same server so I know
it's not the particular database

I researched this for about a week and could not find a resolution. I
read the top performance related issues associated with MS Access and
even tried to use the persistent connection solution.

Front ends are XP pro with Access 2003 and the server is Win 2003. It
seems like this could is an authentication performance problem with the
Access connections modifying the ldb file, but I could not find
anything relating to this.

Any help would be appreciated
Thanks
Deke

Tony Toews

2005-08-11, 1:29 pm

"deke" <orders@inntec.com> wrote:

You've got an excellent description of your problem.

>I researched this for about a week and could not find a resolution. I
>read the top performance related issues associated with MS Access and
>even tried to use the persistent connection solution.

How did you do your persistent connection?

Presumably you've tried the tips at the Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

deke

2005-08-15, 8:25 pm

Tony, Yes i have tried using the hidden dummy form to keep a record
set open. This didn't help any. I would think that when I linked the
frontend on the server to the tables with the local drive instead on
the mapp drive, that it would be just as slow if this was a MS Access
problem.

david epsom dot com dot au

2005-08-15, 8:25 pm

Try this:

In c:\windows\server32\
drivers\etc there are two
sample (.sam) files:
hosts.sam file (used by tcpip) and an lmhosts.sam
(used by LanManager, ie netbios).

Copy the .sam files to .txt files, add entries for
you file server, rename the files to remove the file
extension (so the real files are "hosts" and "lmhosts"),
and re-boot.

This makes the name server lookups faster for that
file server name. The disadvantage is that the
settings are static and need to be set on every pc:
this is the old way of doing things, before we had
domain name servers and wins servers.

On some systems the name lookups can be quite slow,
and that can slow access down a lot.

I am suggesting this because I am interested in how
well it works on different systems, I would certainly
like to know if it helps you.

(david)

"deke" <orders@inntec.com> wrote in message
news:1124133664.733389.7790@f14g2000cwb.googlegroups.com...
> Tony, Yes i have tried using the hidden dummy form to keep a record
> set open. This didn't help any. I would think that when I linked the
> frontend on the server to the tables with the local drive instead on
> the mapp drive, that it would be just as slow if this was a MS Access
> problem.
>

Sophie Guo [MSFT]

2005-08-16, 1:27 pm

Hello,

You may refer to the following article:

889588 How to optimize Office Access and Jet database engine network
http://support.microsoft.com/?id=889588

When you run a Microsoft Jet database engine-based program, such as
Microsoft Office Access, on your Microsoft Windows 2000-based or Microsoft
Windows XP-based computer, the program may appear slower and less
responsive than you expect. This article contains information about how you
can optimize network performance for Windows 2000-based and Windows
XP-based computers. Doing this can make Office Access and Jet database
engine-based programs more responsive.

I hope the information is helpful.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

====================
====================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Sponsored Links