Slow Performance in a Multi-User Environment

[jwalsh604]

I have an Access db application that is intended to present the user
with the next available record to make an outbound phone call on.
Originally, I had set it up as a "split" database but, due to
performance issues, I moved it into one database which is accessed by
4-10 people at one time.

However, while the same record was popping up for multiple users at the
same time in teh old setup, it has continued in the new setup. In the
"OnCurrent" even of the main contact form, the database executes an SQL
statement to update the current status of the record to "Working" so
that no one else can get the same record. However, due to network lag
issues, users are retrieving the same record.

Does anyone have any suggestions on how to combat this issue? It is
causing a major implementation headache!

Thanks,
Joe Walsh

[Albert D. Kallal]

You need to split your database for reliability. I explain WHY you split
your database here:

http://www.members.shaw.ca/AlbertKal...plit/index.htm

If you keep a persisting connection, most of your performance should
return....

and, further, you can work your way through the following list:

http://www.granite.ab.ca/access/performancefaq.htm

>. In the
"OnCurrent" even of the main contact form, the database executes an SQL
statement to update the current status of the record to "Working" so
that no one else can get the same record. However, due to network lag
issues, users are retrieving the same record.

Record locking is built right into ms-access. Simply turn it on..and only
one user will be able to edit the record. All other users will see a
"circle" with a red line through it (like Ghostbusters symbol) in the record
selected bar. You don't have to write any code at all here..this feature is
built into ms-access....

Simply open up the form in design mode, and on the properties sheet (data
tab), simply set the locking to edited record.....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[jwalsh604]

Thanks, Albert. I'm trying some of these solutions now and will let
you know how it goes.

I knew about database splitting but thought I could increase
performance by not splitting the DB. I have now split it and will be
doing some testing today.

Thanks again.
Albert D. Kallal wrote:

You need to split your database for reliability. I explain WHY you split
your database here:

http://www.members.shaw.ca/AlbertKal...plit/index.htm

If you keep a persisting connection, most of your performance should
return....

and, further, you can work your way through the following list:

http://www.granite.ab.ca/access/performancefaq.htm

. In the
"OnCurrent" even of the main contact form, the database executes an SQL
statement to update the current status of the record to "Working" so
that no one else can get the same record. However, due to network lag
issues, users are retrieving the same record.

Record locking is built right into ms-access. Simply turn it on..and only
one user will be able to edit the record. All other users will see a
"circle" with a red line through it (like Ghostbusters symbol) in the record
selected bar. You don't have to write any code at all here..this feature is
built into ms-access....

Simply open up the form in design mode, and on the properties sheet (data
tab), simply set the locking to edited record.....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com