topic Re: Libname pcfiles Access database, doesn't work if database form open. in SAS Data Management

Libname pcfiles Access database, doesn't work if database form open.

ucdcrush — Wed, 04 Nov 2015 23:10:19 GMT

Hello, I am using the following code to create a connection to an Access database:

libname elrdb PCFILES path="C:\mlt.accdb" scan_text=no usedate=yes;

This line fails to work if I have a form open in the database in design view. The message is:

ERROR: CLI error trying to establish connection: [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.
ERROR: Error in the LIBNAME statement.

---

What is the recommended solution to this? I have added to the SAS code to quit SAS if one of the datasets created from a table in database contains 0 rows (which would indicate that a connection was not made and I should not continue processing).. but I would like to avoid putting these traps in throughout the code.

Is there a way to prevent an Access database from being opened such that SAS cannot get the access it needs in order to make the libname connection?

Thank you.

Re: Libname pcfiles Access database, doesn't work if database form open.

SASKiwi — Thu, 05 Nov 2015 23:12:39 GMT

A simple way of preventing this would be to set the Access database file as read-only via the Windows file properties.

Please note Access is not designed to be a full multi-user database, so as a general rule it is safest for no-one else to be using it while you are reading it.

Re: Libname pcfiles Access database, doesn't work if database form open.

ballardw — Fri, 06 Nov 2015 15:42:47 GMT

Since just opening an Access database in Access locks it, at least in my system, another application cannot get lock.If you look at the folder with the database open you'll see a file with the same name as your database but the extension of laccdb (for Access 2007 and later). The L stands for lock and you'll likely see a little padlock on the icon.

You'll see a similar error trying to read an Excel file open in Excel.

Quickest solution: close the Access database.

Re: Libname pcfiles Access database, doesn't work if database form open.

LinusH — Sat, 07 Nov 2015 07:27:25 GMT

What is the use pattern for this db?
If you need multi user access move the data to SQL Server. You could still use the MS Access forms in this scenario.