Access 2002 Database with multiple users on network

[zevw]

I have recently completed a Access Program and it worked phenomenal when I was running it on a local machine. It also worked pretty well when I was on the network and a single user was accessing the data. Each User has the front end on their machine so that the graphics are not passed through the network.

However if 2 users are accessing the database at the same time, it becomes extremly slow, it sometimes takes more than a half minute for a form to open. I am distraught and would like to hear some suggestions, what I can do to speed up the processes.

I have had a situaion like this a few years ago at a company that I was working for. We upgraded the network with a faster switch and the speed picked up drastically (not like a local machine but not longer than 6-7 secs.). We tried the same thing here and it even became slower.

Any ideas or suggestions are appreciated

Thanks alot

William

 

[Crowley16]

don't use access as a back end...

access is notoriously bad at multiple user handling.

although some things you could try:
move your back end to the root drive of your mapped drive on the server.
create a system dsn on the front end computers to the database, and use that dsn.
refresh your links to the back end.
regularly compact / repair
turn off database options like track name autochanges and others...

--------------------
Procrastinate Now!

 

[Ed2020]

To add to Crowley16's advice:

1. Make sure all tables are indexed correctly.
2. Avoid use of bound forms (especially for large recordsets).

I think "don't use access as a back end" is somewhat sweeping though. MS Access is fine providing the circumstances are correct.

Ed Metcalfe

Please do not feed the trolls.....

 

[zevw]

Crowley and Ed!

Thanks for responding and sorry for taking so long to get back.

Can you please explain "move your back end to the root drive of your mapped drive on the server".

I have the data on a server mapped as "P:" and the files are located in the directory "P:\Purchasing", where do you think I should place them?

 

[Ed2020]

Zevw,

It's not something I've ever experienced myself but I have heard on here that a long filepath (i.e. the number of sub-directories away from the root (in your case P:\) for the backend database can affect performance.

I would imagine that as your database is only one directory down from the root of P:\ this wouldn't be a factor for you.

In terms of your forms opening slowly this must be an issue with retrieving the data from the backend database. Are the forms based on tables or queries?

Ed Metcalfe.

Please do not feed the trolls.....

 

[zevw]

My Forms are based on Queries and are created based on the critria provided by the user

 

[Ed2020]

check your table indexing. my guess is that's the source of the problem.

Ed Metcalfe

Please do not feed the trolls.....