Thread: The database could not lock table 'tablename' because it is already in use by another

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

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.

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?

Originally Posted by Colper

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

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!