The database has been placed in a state by user error

Hello everyone,

Long time lurker, first time poster...be kind

I do not consider myself an Access expert, much closer to a Moderate user.

I'm having trouble creating a split database capable of allowing multiple users to enter data at the same time. I created a database on a shared network folder, which everyone has read/write access. I manually split the database by:
1. Making a copy of the original
2. Deleting all the queries/forms/reports/modules inside the Copy, leaving just the table, thus creating the back end (BE). I also put a password protect on the BE
3. Deleting the tables in the original, and re-linking the tables to the BE, thus creating the front end (FE)
4. I distributed the FE to multiple users

Multiple users can open the FE database, but when a user opens a form to add/edit existing data in a table, it prevents other users from opening the FE database and gives the "The database has been placed in a state by user 'Admin' on machine...".

When the first user opens their FE file, the BE on the network creates a .laccdb file and prevents others from opening the BE for changes.

I verified that the form properties has Record Locks to "No Locks". The Access Options - Client Settings is also set to "No locks" and default open mode is "Shared"

Can anyone please help troubleshoot why this is happening?? Thank you in advance for your help!

Are your forms bound to tables or queries of tables. It can make a difference. It should be a query.

RuralGuy said:

Are your forms bound to tables or queries of tables. It can make a difference. It should be a query.

Click to expand...

They are bound to tables. Is that what is causing the error??

I did a test earlier by creating a 'simple' database with just 1 table and 1 form bound to that table. I then split that table (using Access Splitter) and discovered that my co-worker and I could add/edit data through the form at the same time. The difference between the two scenarios is I manually split my 'complex' database instead of using Access's splitter. I had to manually split because the Access splitter was giving me a "Subscript out of range" error that I couldn't trace down

Queries eliminate a lot of Locked Table issues. Give it a try.

Concur with the others on using queries for everything. I took it so far as to have a single-table query for EVERY table in the BE and control the query's lock characteristics to either no locks or optimistic locks. When I still used direct form-to-table connections, I would sometimes get strange messages about not being able to find the table even though the links were correct. When I switched to full-on query linkage, it started purring like a kitten.

As to the Access splitter having trouble, that is a sign that something else is wrong that really SHOULD be addressed. I'm surprised you haven't gotten whacked by a code error somewhere when you compile the code or when you run it. It is not accidental that the splitter is kvetching at you.

Hi everyone - Just wanted to give an update with what happened in case it is able to help others who may be running into the same issue:

1. Started from square 1 before 'splitting' the database by taking my most recent backup and made a copy of the backup
2. I then deleted all of the queries/forms/reports/modules/macros from the Copy, leaving only the tables
3. I used Access' Splitter to split the database, putting the BE on the network drive
4. I then imported all of queries/forms/reports/modules/macros into the FE

I have no idea why this now allows multiple users to add/edit using the FE without locking the BE. I don't know how this was any different than when I manually split the database other than using Access' splitter tool. Thank you to everyone to responded. I will definitely adjust the database to bind the forms to queries instead of tables as recommended.