Connecting Access with SAS

0
votes

I have a decent amount of experience with SAS and Access, and I know how to call Access from VBA, but I am having a problem with table locks that I need help with.

Essentially what I am doing is running a proc freq in SAS, passing it to a table in Access so it can be read in a subform.

I have a form with some combo boxes where the user can select info about what variable they want the frequency of. Then they click a button and VBA passes those parameters on to SAS, and the subform populates with frequency data from SAS.

Everything works except the final part.

I have successfully taken the parameters from Access, passed them to SAS, and run the correct frequency. But during SAS's proc export back to Access, I get an error stating: "Exectute: The database engine could not lock table "FREQ" because it is already in use by another person or process."

The issue is that the table that SAS is exporting to is used in Access by the form that opens SAS.

Since I am obviously not using the form while SAS is processing, is there a way to have the form unlock or disconnect from the table so that SAS can access it, and then reconnect the table to the form after SAS finishes processing?

I tried splitting the database, but I got the same problem.

1
ms-accessvbasas
Could you close the form, have the table update, then reopen the form? – Gene
I still would have the same problem. I could tell it to close and reopen, but Access wouldn't know to wait for SAS to reopen and so it would just close and reopen while SAS is running and create the same problem – Carl
Do you have the SAS Add-in for Microsoft Office licensed, by chance? – Joe
Have your sas updates update a specific value in a temp table as it's last operation. Before you start the updates close your form and open a hidden form with an on-timer event to regularly check for the specific value in your temp table. Once the on-timer event finds the value, close the hidden form, open your main form back up. – Gene
Have SAS write the results to a CSV. Then import the CSV in access. Kinda ugly but should avoid the locking issue. – Robert Penridge

1 Answers

0
votes

The problem maybe was caused by the fact that the SAS was connecting with the ACCESS. First clear the connection(such as: libname libname_name clear), and then connect with Access.