Multiple users slowdown

[jsjaedon]

Hi everyone,

This is my first post, hopefully you guys can help me out. I have set up an access database for my dept. stockroom. The stockroom previously was just 1 excel spreadsheet that had the shared setting enabled so multiple people could edit it and such. This became extremely tedious and saves started to take up to 15 minutes when about 5 people had them each open and did edits.

I managed to set up an access front end and backend for this situation. Basically the table was saved in an access database and the forms were in another that linked to the tables. So the table database is on the network shared folder, and the forms are on each individual computer.

It seems like this would work fine and such, but when just 3 to 4 of them open up the forms and start working, as soon as one person trys to make a change, the whole program slows down almost to a halt. what am i doing wrong? any suggestions would be appreciated.

Thanks!
Jerry

 

[The_Doc_Man]

First, check that your locking is set Edited Record for any form.

Next, how many indexes do you have on the underlying tables? Too many of them can slow you down.

Third, and this is the biggie - how recently have you repaired and compacted the DB? Both of these need to be done (as a precaution) on all DB apps. You will have to determine the optimum frequency for doing these two actions. Start with once per week and see what effect they have. See also how long it takes for their performance to crap out on you.

 

[jsjaedon]

hey thanks a lot doc man! i set it to edit record locking and the slowdowns disappeared! much thanks.

Now im getting a new problem. every so often my users complain about not being able to edit. this message shows up on their screen.

"could not update, currently locked"

but the weird thing is no one is editing that record. is this a backend setting i need to do? btw, are there any special settings on the backend i need to do? the edit record locking is only for the front end right? do i need to do taht for the backend?

thanks guys!

 

[geralf]

A record isn't necessary being edited at the time, but the record is probably in edit mode after it has been edited. Edit mode is indicatet on the formms record selector bar by a pencil. To go out of edit mode you'll have to save the record. This can be done by the user or done automatically after updating a field by code. In the controls (Fields) After Update event put DoCmd.RunCommand acCmdSaveRecord.

This might solve your problem.