Solved: access error "the database has been placed in a state by user......

[phill952000]

Hi,

i am occasionally getting an error that states,

"the database has been placed in a state by user admin which prevents it from being opened or locked"

this occurs when i try to add a new record using a macro from form fields in word.

however, if i shut down the database when i get this error, then open it up again (or leave it closed), then the macro works and the new record is applied.


my situation is i have a database on a shared drive and all other users link to the database in a way where at the start of the day, each user will right click on the master database and click send to my documents. this then copies the database to their area.

the error i am getting is on my machine where i open and modify the master database.

can anyone help.

Cheers

phill952000

[CreganTur]

this occurs when i try to add a new record using a macro from form fields in word
How are you adding this new record from Word? Are you using a DAO or ADO connection, or are you using a different method?


my situation is i have a database on a shared drive and all other users link to the database in a way where at the start of the day, each user will right click on the master database and click send to my documents. this then copies the database to their area.


Have you considered splitting your database into 2 different portions, a frontend and backend? The backend would hold nothing but your tables- it would be on your shared drive. The front-end would contain everything that the user needs to interact with the backend. You would have the frontend use linked tables to connect with the backend database. This would prevent your users from having to copy the database.

It seems to me that if everyone is working off of their own copy, then you are going to run into a lot of issues with different copies having different records.

[phill952000]

my connection to the database is ADO;

Dim connect As ADODB.Connection


As for everyone having different copies of the database and records, this is not really an issue as i make the changes on a night time and have them update it every morning. I appriciate what you are saying though regards=ing this.

is it a difficult task splitting tha database into a front end and a back end?

[CreganTur]

is it a difficult task splitting tha database into a front end and a back end?
No, it is very simple. Access has a built in tool to do it for you, if you choose to use it. It'll walk you through the process.

Or you can do it manually: just create a new blank database and import your tables into it. This will be your backend that you keep on your shared drive.

Then you delete the tables out of your front-end database. Use the Link Tables tool to create a link to the tables in your backend database. Since it is on a share ddrive, be sure to use your explicit filepath- serverName/DriveName/Filepath

Do this instead of just referencing the drive letter. The reason for this is because shared drives can have different letters for different users sometimes.

This should eliminate the issue you are having, because no one will be directly opening the backend database.

Let me know if you need more help :thumb

[hansup]

i am occasionally getting an error that states,

"the database has been placed in a state by user admin which prevents it from being opened or locked"

this occurs when i try to add a new record using a macro from form fields in word.

however, if i shut down the database when i get this error, then open it up again (or leave it closed), then the macro works and the new record is applied. You may not need to close down the database to clear that error condition. Try compacting instead. (From the Access 2003 menu, Tools -> Database Utilities -> Compact and Repair Database)


my situation is i have a database on a shared drive and all other users link to the database in a way where at the start of the day, each user will right click on the master database and click send to my documents. this then copies the database to their area.

the error i am getting is on my machine where i open and modify the master database.
Do your other users ever see that error, or is it only you? And do you only see it during sessions when you've changed code?

Your situation sounds similar to one discussed in here previously.
Check this link (http://www.vbaexpress.com/forum/showpost.php?p=177363&postcount=57)

If that is what you're dealing with, I don't understand exactly why it happens. But, for me, it has been only an inconvenience during development sessions, and doesn't result in lasting damage and the end users never see it.

Hans

[phill952000]

Thanks alot for that that has worked a treat, the database is now split into a fron end and a back end

much appriciated

phill952000