The database has been placed in a state...error

[RonRico]

I have an access database that opens and runs a macro (from a .bat file, from a scheduled Windows task) every 15 minutes. It runs fine 90% of the time, but every few days I get the 'The database has been placed in a state by an unknown user that prevents it from being opened or locked' error. When this happens overnight, the next day there may be 20 instances of the database open with that error as the Windows task continues to fire every 15 min.

I have searched the Internet and all of the solutions I have found relate to the database being opened by multiple users at the same time which is not the issue here. I do have error capturing in the macro and modules that fires off an email on error, but it never get's to that point.

Does anybody have any suggestions on what may be causing this issue and how it may be corrected. Note: The macro runs several VBA modules and then uses the menu exit command to close.


Windows Server 2008 R2 Access 2013

[ranman256]

It usu means the db did not close totally and the lock file is still there , .ldb,or .laccdb
even tho no one is in the db, it thinks it. Delete the lock file THEN open the db.

[RonRico]

Thank you, yes that works, but my issue is what is causing it to not close properly in the first place? This is a major issues since the Windows scheduled task that opens the database instance runs 24 hours a day, 7 days a week and if it happens after hours we can get a large number of instances of this error on the server when the database tries to open.

[ranman256]

Dont know about that one. Usu just closing the window ,exits the app.
Maybe put a EXIT button where onClick event:

docmd.quit

[RonRico]

Thanks for the reply, but this is 100% automated. There is no user interaction. Basically the macro imports and process files for email distribution. The files are raw data submitted from an Internet form.

[CJ_London]

how long can the process take? if more than 15 minutes then the db will still be open when the next timer event fires. Could try changing the frequency to 30 mins to see if that solves the problem - but then perhaps there is more data to process so it takes longer. Also check whether there is some part of the process which ends up in a wait state because perhaps there is a backup or other process in progress.

Something else to consider when time is of a constraint is to have two instances of the front end, one of the backend and set two schedules spaced 15 min apart so they run alternately

[Bulzie]

Could be something locking your tables like nightly network backups or something hanging the process and preventing the db from closing. Does it happen at the same time each night that it errors? Maybe add/store in table more check points in your process so you can see exactly where the process stalls?

Reread your post, can you explain again how this runs on the scheduler? The scheduler opens Access which has a macro that shells out to run a bat file? What does the bat file do?

[NoellaG]

Hi,
If you can upgrade to SQL server, you just can run a job to import the files. If the job is already running, SQL won't start it again. And you can make it send you an e-mail on error.
If you can't upgrade to the full version, try SQL express and use windows Scheduler and SQLCMD.

[RonRico]

The process usually takes less than a minute, never more that 2 or 3 minutes. I originally had the task set to fire every 5 minutes, but changed it to 15 minutes to no effect. When the database fails to close on the previous event it doesn't matter how long between events.

[RonRico]

Happens randomly, sometimes in the middle of the day, but we usually catch those. The Windows task runs the .bat file. The .bat file opens an instance of Access and runs the specified macro. The macro runs vb modules that imports data from .csv files, formats into a readable PDF report (separate one for each .csv file if found) and sends it as an email attachment.