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

Thanks for your help once again. I see now, this database I am talking about has more than 163,000 records and may be this is the reason it locks. I will try this but manually, great explanation there.

Now, one concern I have is how I link this to the network. The way is working now is; I created a shortcut pointing to the "S" drive and the security.mdw file on the network and then I place it on a share folder on the desktop. I didn't have to install anything on each user's PC. Sorry if I am not understanding you. I am still learning. I consider myself an intermediate in Access.

God Bless!

Originally Posted by Colper

Now, one concern I have is how I link this to the network. The way is working now is; I created a shortcut pointing to the "S" drive and the security.mdw file on the network and then I place it on a share folder on the desktop. I didn't have to install anything on each user's PC.

Right, that's why you're having problems. Although this isn't made clear in the Microsoft documentation, it is well established throughout the professional developer community that there is only one way to safely deploy a multi-user application on a local network:

1. Split the database. One .mdb will contain TABLES and nothing else. This is the .mdb that needs to be secured.
2. The other .mdb (the "front-end") will contain Queries, Forms, Reports, Modules (and if you use them, Macros). Each user will have A COPY of this .mdb on their work station. (This also means that each user must have Access installed on their work station!)
3. The "front-end" .mdb's are independent of each other and do not cause any interference, because they do not contain ANY data. They all LINK to the data, which at all times remains stored in the common "back-end" .mdb. Thus, there is just one .mdw workgroup file, that manages the record locking for all the users.

The linking is the key element. You can link to remote data sources with the File->Get External Data->Link Tables menu selection. You can manage your linked tables with the Linked Table Manager in the Tools->Database Utilities->Linked Table Manager menu selection.

If you have problems implementing this, return here and we'll try to help you get through it.

One other thing: a by-product advantage of splitting your database is that it makes it easier to introduce changes to forms, reports, etc. When that is needed, you can make changes and test them without interfering with use of the database. When changes have been tested, you can then replace each user's copy of the front-end. If it becomes necessary, you can even automate the upgrading of the front-ends, although this requires a bit of somewhat tricky programming.