Prevent Access from Blocking ODBC Query Under Certain Conditions

1
votes

I'm using Access VBA to call an R script that builds some charts. This R script pulls some data from the Access database via an ODBC query. I'm using library(RODBC) to make the connection from R.

If I restart Access, or run Compact/Repair, the query will always run. However, if I make other changes in the database, I'll sometimes get the following warning:

Warning messages: 1: In odbcDriverConnect(sprintf("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s", : [RODBC] ERROR: state HY000, code -3810, message [Microsoft][ODBC Microsoft Access Driver] The database has been placed in a state by an unknown user that prevents it from being opened or locked.'

And the script fails to run, because the connection couldn't be made.

What's the best way to manage/set the state of the database so the query will always run? The issue isn't directly linked to whether a table is open or not - I can open a table, and close a table, and not have an issue, and even run with a table open, sometimes.

Edit: The error is caused by making any sort of change in a VBA module (this is unrelated to the actual VBA call of the script, I can run the same rscript call in the command line and replicate the error). Now that I understand that's the cause, I don't think it's a big issue. Saving the VBA module sometimes seems to correct the error, although not 100% of the time.

1
rms-accessrodbc
This doesn't answer your question, but does it work when using the odbc package instead of RODBC? – wibeasley
Is the accdb/mdb file ever opened when this warning is thrown? Is it a local drive, or file share? – wibeasley
Yes, I'd assume that if the accdb file was closed it would never be an issue, but it has to be open, because the script is called via a button in a form. I know I could split the DB into a front end and back end, and that may be one solution, but for the moment, I'd prefer to keep it consolidated. The accdb file is stored on OneDrive at the moment – Mako212
@wibeasley okay, see my edit, I think I'm satisfied that it's only changes to the VBA module that cause the error to be thrown, so it's not really a big deal. Thanks for the help. – Mako212
This is a very confusing process. Why are you calling R from MS Access VBA but then connecting via odbc to the very same database? Also, why is a VBA module being edited on the fly? Design changes of any objects should be final when code is being processed. – Parfait

1 Answers

2
votes

This is by design.

Making any design change to a VBA module, form or report sets an exclusive lock on an accdb file, which remains until the Access application that has made the change closes.

Just close and re-open the file after making any design change to a form, report or VBA module.

This is one of the reasons people recommend you split the database, since then you can change the design without locking people out of the data.