Multi-user application + database access

[RadarUK]

I have created an enquiry database for my business that uses linked tabled on a server and an application frontend on the clients.
No matter what I try I cannot get the database to lock at record level, it always exclusively locks at database level, thus not allowing more than one user at a time.
I have setup a security database with the relevant permissions and each user has to logon when they access the database, but it still locks up at database level.
I have tried all the things in my programming books and none of them work. The database is also taking a long time to 'load-up' when one person does eventually accesses it.
My manager is now calling my system crap, and I need help urgently. Is there anybody out there who has had similar problems.
If someone can help, I can send them the app to check-out and there could be a beer in it for you.
Microsoft Access 2000.
Tony

 

[FloBob]

Unfortunatly, I have found no way to fix the inherant lag you get when opening a table that is on the back end. Unless the table is local there will always be this connection time. What I have found though is if I only have to have that connection opened once. Say connect to the db in a switchboard that is always open, I will recieve the lag only that first time when I open that switchboard. All other forms open at the speed as if they were local. I hope that helps though I do not know what to give you about the record locks. What I try and do is keep forms unbound from tables but even in that event I dont believe you should have a multiple access issue. Have you tried seeing if its specific to a user or is it just the first user to access those records?

 

[RadarUK]

Unbound controls

Unfortunatly, I have found no way to fix the inherant lag you get when opening a table that is on the back end. Unless the table is local there will always be this connection time. What I have found though is if I only have to have that connection opened once. Say connect to the db in a switchboard that is always open, I will recieve the lag only that first time when I open that switchboard. All other forms open at the speed as if they were local. I hope that helps though I do not know what to give you about the record locks. What I try and do is keep forms unbound from tables but even in that event I dont believe you should have a multiple access issue. Have you tried seeing if its specific to a user or is it just the first user to access those records?

Thanks for the comments.
I am trying the unbound form option at the moment, I have all the necessary coding from the 'Profession Access 2000 programming' published by Wrox.
My question is, I have an enquiry system using 1 main form 'The header', with two subforms, I call 'Enq Item Details' and 'Qty Details'. On the header form (EnqHdr) I have bound controls i.e. A customer selection ComboBox, An enquiry source ComboBox and a Sales person ComboBox. All these controls are bound and allow the user to select from dropdown data. If the data does not exist then an DoCmd.Openform is used to open the relevant data form to add the new data. I was wondering how I combine all these contols into the one unbound form. Which event type do I use on the unbound controls? My book only gives an example of one form to one table, whereas, my system uses at least three tables in the one header form.

Look forward to your response.
Attached is a picture of the form.

Tony

 

[skwilliams]

Regarding the record lock issue. Have you tried to split the database. Keep the backend in a central location (i.e. network where all users can access) and make a copy (not shortcut) of the frontend to put on each PC. This should prevent any problem with record locks.

The lagging issue. Have you tried the compact and repair feature under tools|Database Utilities. Depending on the amount of concurrent users and size, location of the database, you could experience a certain amount of lag time.

HTH

 

[skwilliams]

Thanks for the comments.
I am trying the unbound form option at the moment, I have all the necessary coding from the 'Profession Access 2000 programming' published by Wrox.
My question is, I have an enquiry system using 1 main form 'The header', with two subforms, I call 'Enq Item Details' and 'Qty Details'. On the header form (EnqHdr) I have bound controls i.e. A customer selection ComboBox, An enquiry source ComboBox and a Sales person ComboBox. All these controls are bound and allow the user to select from dropdown data. If the data does not exist then an DoCmd.Openform is used to open the relevant data form to add the new data. I was wondering how I combine all these contols into the one unbound form. Which event type do I use on the unbound controls? My book only gives an example of one form to one table, whereas, my system uses at least three tables in the one header form.

Look forward to your response.
Attached is a picture of the form.

Tony

Regarding the combo box issues, I wrote up a piece on cascading combo boxes. Perhaps this will help you with your issue.