MS Access slow with more than one user

[Lazarus52980]

I have a customer I'm trying to help with an odd MS Access issue. They have a database setup that they schedule classes in and keep a large list of customers. The database is split, so part of it is on the server, and they all have part of it on their desktops. If only one person is logged into the database, it works very quickly, but as soon as more than one person is logged in, it works very slowly, even when the 2nd person is not doing anything actively.

Any thoughts on how I can improve this? I don't really understand why it would slow down that much for simply having another person logged into it...

 

[Saint Nick]

Are you noticing slow performance on forms or table access or what? What part of it is going slow?

 

[Lazarus52980]

I am not super familiar with access, I am their network and IT guy, so please forgive my ignorance, I will answer this as best I can.

When they load up the database, it works just fine. When they log into a class, it seems to work fine as well, if a little slow. However, when they click on the name of one of the people attending the class, it can take 20-30 seconds to load up the name listing...

I believe that means its slow loading the tables, but please correct me if that is wrong...

 

[nsafreak]

Have you tried to run a compact and repair on the database? That will sometimes fix this kind of issue.

 

[Lazarus52980]

Have you tried to run a compact and repair on the database? That will sometimes fix this kind of issue.

I have not, but I will do so Monday morning and post the results here.


Thank you for the advice.

 

[Nothinman]

That's not odd, Access is slow and using Access on databases on CIFS shares is even slower.

If it's gotten noticeably slower recently there may another factor slowing it down like a disk or network config issue on the server, but Access is just a dog in general and should be avoided as much as possible.

 

[EagleKeeper]

Access is great for a single user and/or setting up development and then migrating to a SQL system

 

[Emulex]

keep in mind file locking and integrity is important to all databases. you can not have two people in a set of data at once or they will overwrite data dependencies. When you commit a transaction locks have to go in place and when you are reading you have to decide to read with locks or read dirty.

i'd suggest rebuilding indexes periodically and shrinking never the less. defragging the server helps but i strongly suggest you do that while its offline.

sql server has very much the same problems as access but it scales up greatly. But if it is an underlying problem throwing more hardware at it may not help.

 

[mjrpes3]

I've had good success using Access as the frontend and MySQL as the back end connected by ODBC. Our usage is very light, with no more than 2 or 3 people accessing the same data at one time. Occasionally there will be a write conflict, with the user getting a message that data cannot be written to. Lazy solution is to wait a bit and try again.

The only quirk I had to work with was row creation. Here's some note I wrote years back about it:

All MySQL tables that will have data inserted into them in MS Access need to have a special TIMESTAMP field in order to properly work with Access. This allows Access to use an INSERT operation and get back a correct primary key from MySQL. Without this, a newly created row will show '#DELETE#' instead of data after focus is changed, and a user will need to reopen the form to see the inserted data again. Do the following when designing MySQL fields with timestamps:

When using a table with multiple timestamp fields, The one used with Access must come first in field order
The timestamp field must have a default value of "CURRENT_TIMESTAMP"
If you are updating a MySQL table to include a timestamp field, existing rows must have a default value in this timestamp field. If the timestamp field is set to null, Access with give you a vague error message.

I'm sure you have an even easier time integrating with MSSQL, but I set this up long ago before Windows offered Express.