DeveloperBarn

Can someone please help me with this issue? I have a database which I created and it’s being shared by other users. When there is more than one (1) user using the database I get this error:


“The database could not lock table (tablename) because it is already in use by another person or process.”

Thank you in advance.

What version of Access are you using? There were substantial changes in the multi-user features, between 2003 and 2007.

__________________
If work was really that good, the rich would own all of it and charge the rest of us to do it.

Access 2003.

And when do you get this error, immediately when someone else accesses the database, or only when the user tries to perform some operation? And if the latter, what operation triggers the error? Be as specific as you can be, such as showing code or SQL, if appropriate.

It happens when more than one user tries to update/inputa data or tries to open any form in the database. I don't know what code to show, there is a lot of coding here so I have no idea where to find the corresponding code to show you.

Thank you.

OK, I would need to know the following:

1. How is this database being shared? Is it a single .mdb file on a network drive, or is the database "split" into "front-end" and "back-end" .mdb's? If it is being used by multiple users, it definitely should be split. If it IS split, do each of the users have their own copy of the "front-end" .mdb? They should have.

2. Is the database "secured" with user-level security? This means that users must LOGIN with a password in order to access the database.

3. Do I understand that the database is fully accessible as long as only one user is trying to use it at the same time?

1. The database is ina single .mdb file on a share drive. Users donot need a password, only username. I have created two other databases and have never heard or done anything with front or back end or split. They are on the same share drive different folders and they are working perfectly.

2. Yes, it's secure with user-level security. Username only no password.

3.Yes, only one user can edit or input data at one time.

Thanks....

OK, I would say that you have just been lucky up 'til now. Access databases should always be split when you need to have simultaneous use by multiple users on a network. Here's some information on this:

Using MS Access: Multiple users, joerg mayer, networked system
Multiuser Databases in Microsoft Access
Time To Link Your Microsoft Access Program To MS Access Data File | Daily Access Tips
http://www.ozgrid.com/forum/showthread.php?t=34665

I really appreciate your help. I read all of those links and that's great information. I will try to implement it as needed as possible.

Now, my thing is that the other two databases I created are working just fine without any problems and I never did the split. So I am curious as to why it has worked. Another thing to keep in mind is that this database I am talking about I did not created from scratch.

This database was already created by another user and all I did was redesigned it.

Thank you once again!

As I said, you were lucky. The specific reasons for lock-ups are hard to determine, and are really unimportant, since the only way to avoid them is to split the database. To satisfy your curiosity, though, it involves how many tables are in the database, and what kind of relationships exist, how many records are in the tables, how many users are trying to access the same tables at exactly the same time, etc. etc. etc. etc.

The only safe rule to follow is: in every case where multiple users need to be able to access the data simultaneously on a network, SPLIT THE DATABASE. It's really pretty easy to do. There's even a Wizard for it, although I don't bother even using it. The whole point is to import all data (TABLES) from the "front-end" .mdb into a new "back-end" .mdb. That's the .mdb file that will be "linked" to by all the users' "front-end" copies. When you have done that, open the front-end .mdb and [gasp!] DELETE ALL THE TABLES. Then, using the Linked Table Manager, link all the tables back in (don't import them, link to them), they will appear in the Tables Tab of the Database Window with little arrows beside them. The application will then work as it did before. Now, make a copy of this "front-end" for each user and install it on their work station. You may need to repeat the Linked Table Manager function on each one, depending on how each one has mapped the shared drive on the network, but it's a 30-second operation.

One word of caution: whenever you do something like this, always make a backup copy of the file before you begin -- JUST IN CASE something goes wrong!