topic Re: Export to MS Access triggering ERROR: Execute: The database engine could not lock table 'ZZZ'... in SAS Programming

Export to MS Access triggering ERROR: Execute: The database engine could not lock table 'ZZZ'...

Ryanb2 — Mon, 22 Apr 2019 20:46:03 GMT

I created an Access database so users can look up records by ID from a table generated in SAS then exported to Access each morning. However, I anticipate many users will have the tool open continuously. When I export a data file and the form accessing the table or query is open I get an error message in SAS reading, "ERROR: Execute: The database engine could not lock table 'EAB_status_2018' because it is already in use by another person or process." What is the best way to update the table in Access and still allow users to have the query form open?

PROC EXPORT DATA=TABLE_OF_RECORDS
OUTTABLE='TABLE_IN_ACCESS'
DBMS=ACCESS REPLACE;
DATABASE='C:\TEST\NAME_OF_ACCESS_DB.accdb';
RUN;

Re: Export to MS Access triggering ERROR: Execute: The database engine could not lock table 'ZZZ'...

ChrisNZ — Mon, 22 Apr 2019 22:06:26 GMT

You are not doing an update here, you are replacing the table. This requires the whole table to be unlocked.

If the table is locked, you can try to update records.

Access seems to have this capability, though I am unsure how it looks from the SAS side.

Re: Export to MS Access triggering ERROR: Execute: The database engine could not lock table 'ZZZ'...

Ryanb2 — Mon, 22 Apr 2019 22:58:20 GMT

Thanks for the clarification, Chris. Yes. This is a full replacement of the table. I'll look into updating the records rather than the full replacement, although I'd rather find a solution that includes the full replacement.

This database is for reference only. Users will not be adding, removing, or modifying records. I noticed the problem remains even when I link to another Access database that is read-only. Has anyone ever tried linking an Access database to another type of file (e.g., .csv, database file...) that would allow a full replacement? I tried the csv method but the table is very large and the interface became very sluggish.

Re: Export to MS Access triggering ERROR: Execute: The database engine could not lock table 'ZZZ'...

ChrisNZ — Mon, 22 Apr 2019 23:20:17 GMT

Full replacement always requires that the table is closed afaik. Read-only is not enough.
Maybe what you could do is that the users access a different table at odd and even hours? It might not help if a user just leaves the screen open though.

Re: Export to MS Access triggering ERROR: Execute: The database engine could not lock table 'ZZZ'...

SASKiwi — Tue, 23 Apr 2019 02:06:22 GMT

MS Access isn't really designed for simultaneous multi-user use. A better option would be a multi-user database like SQL Server. If you have SAS/ACCESS to ODBC or the SAS/ACCESS product for your chosen database licensed then you are good to go.