Database locking problem

[ojosVerdes]

Hi All,
I have this application which uses VB 6 as the front-end and MS Access as the back-end. Basically is a order entry App. When I first wrote the application there were only 1 or 2 users working on it at the same time. Now there are anywhere from 5 to 10 at any given time. Ever since the number of users increased the database keeps locking. It seens like access does not release the record locks. Though when one or two user work at the same time I don't have this problem. I have gone through the code and cleaned it, however I can't seen to solve this problem. I don't know if it access itself or if it is my code that needs to be redesigned. Can someone enlighten me as to what path to take or what things to look for?. Is ms access a true multi-user database?

Any help/hints would be greatly appreciated.

Thanks in advance.

Ed.

 

[jel]

Work with disconnected recordsets to get the data you want to display, and connect again if you want to update.
You can use ADODB, and set the activeconnecton of the recordset to nothing after you opened the recordset. To update: get a connection, and set it as the active connection again.

 

[ojosVerdes]

Thanks JEL. I would definitely consider your suggestion.
Please tell me, have you had similar problems with MS Access? Is this access database problem? Would using SQL solve this problems?

Thanks Again.

 

[jel]

Not that much experience with Access in multi-user situation (heard some horrifying storys, but that's just hear-say). Maybe someone on the Access-forum knows more...

My guess was just that database-locks will hardly occur if you're hardly connected to a database.
Of course, even then you can get deadlocks in your update-routines, but with only 10 concurrent users I wouldnt worry about that.