SAS stored macro - VBA - "lock is not available" error

[gabriel_k]

Hello,

working on an application in Excel that generates charts and tables from the tables previously analysed by SAS. I use VBA (Windows client and IOM Bridge connection) to run SAS stored macro and then getting results with ADODB. Once running the code SAS tables are locked even if the Workspace was closed and i'm getting  "lock is not available" error. How can i solve this problem ? (basicly i need to unlock the tables just to delete them).

[Kurt_Bremser]

I guess you meant "stored process".

Do you really need to update persistent files from the stored process?

Or do you simply create tables for transfer to Excel in a location other than WORK? In which case, I'd move that to WORK to avoid unnecessary collisions.

[gabriel_k]

I use stored compiled macros:

http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a001328775.htm

But You have a reason, i'll store my tables in Work library, i just wanted to have a possibility to look on tables thrue the SAS Guide.

There are some other tables i use to retrieve information from different libraries and change rows if needed, so i need to drop the lock anyway. 

[Kurt_Bremser]

---

@gabriel_k wrote:
..... i just wanted to have a possibility to look on tables thrue the SAS Guide.

---

This is probably the source of your problems. Viewing a dataset through EG will always prevent the lock that is needed for update.

If you just need to update specific parts of datasets, SAS/SHARE might help (if you have it licensed and set up).

My method to prevent ERRORs caused by open datasets is specific to UNIX. To update a dataset, I do the update in WORK, then I use the operating system to remove the link (directory entry) to the file (UNIX rm command), and then recreate the dataset from the table in WORK.

This is possible because the rm removes the reference, but not the file. Only when a file is found to have no further reference defined, the physical data is removed once the last handle closes.