Good evening I am trying to resolve an issue with export to a file which is an access database file. They execute the SAS script that updates this accdb file on the :drive but the user is receiving file already in use. I tried to use a backup of the file but getting the same results. Also trying to help the guys who made some security changes to the server figure out if it's something that locked the file. Any thoughts or assistance would be greatly appreciated. when I click on the file the Access database opens fine. Seem like we just can't update it/export to it. Error below:
NOTE: There were 101 observations read from the data set ABCOSPPS.SADJ_DDCT.
NOTE: The data set ABCOSPPS.SADJ_DDCT_BASIC has 101 observations and 14 variables.
NOTE: DATA statement used (Total process time):
real time 0.10 seconds
user cpu time 0.01 seconds
system cpu time 0.04 seconds
memory 662.28k
OS Memory 15760.00k
Timestamp 12/31/2021 11:12:16 AM
Step Count 7 Switch Count 0
NOTE: %INCLUDE (level 1) ending.
1468 PROC EXPORT DATA= ABCOSPPS.Sadjustment
1469 OUTTABLE= "Adjustment"
1470 DBMS=ACCESS REPLACE;
1471 DATABASE="S:\sas\\access\ABCOSPPS_Current.accdb";
1472 RUN;
ERROR: Connect: Could not use 'S:\sas\\access\ABCOSPPS_Current.accdb'; file
already in use.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 1.40 seconds
user cpu time 0.09 seconds
system cpu time 0.21 seconds
memory 1055.15k
OS Memory 16016.00k
Timestamp 12/31/2021 11:13:23 AM
Step Count 8 Switch Count 0
This message typically means someone or some process it using this file in parallel to SAS and is locking it and so SAS cannot update this file till this other user/process releases the lock.
You or your IT can find who is locking this file by using 3rd party tools like "Process Explorer" from Sysinternals: https://www.mysysadmintips.com/windows/clients/593-find-what-is-locking-a-file-using-sysinternals-pr...
Does the library ABCOSPPS point to S:\sas\\access\ABCOSPPS_Current.accdb? Proc export needs exclusive access to the access-file.
YES I believe everything is in order with that. We just can't find anything that has that file in use already.
Have you seen what I suggested? Try 3rd party tools like "Process Explorer" from Sysinternals to find out who is locking this accdb file: https://www.mysysadmintips.com/windows/clients/593-find-what-is-locking-a-file-using-sysinternals-pr...
@KAS25 wrote:
YES I believe everything is in order with that. We just can't find anything that has that file in use already.
Sorry for the late reply.
Is it possible to open the access-file after all sas processes are stopped?
If yes, then, as i have mentioned, proc export needs exclusive access. This means that you can't use proc export to write to the same file you have opened via libname statement. Btw: why not use proc copy to write the dataset to the access-file? I have hardly used access in the last 15 years, so i am not sure this will work.
Windows server admins don't need the process explorer from sysinternals, the os has a tool showing all open files.
Hi
In our SAS environment we also started getting the same error with code that worked perfectly fine before.
The only SAS updates we have done recently was to apply the changes to fix the Log4j Vulnerabilities as recommended here: SAS Help Center: Introduction
The weird thing is that other MS access databases still work. I am not sure if that is related with the fact that on our local machines we have Office 365 and on the SAS server we have Office 2016?
We will contact IT Support and if we find a solution we will let you know.
Best Regards
Thanks for the reply and Yes please let us know if you find out anything. We still trying to resolve. This is our only SAS that updates MS ACCESS DB.
Yes please share any findings - Im having a similar issue with a SAS script that has been running a few years that updates a single table in an .accdb 😞
Just an update, we have not yet fixed the error but we found a workaround. In our organisation, we noticed that the issue was only happening when accessing MS access databases on network locations, hence the workaround was to copy the files to the server and updated the code to use this location. This solution is, nevertheless, temporary thus we are still investigating and because it is happening with many of you it is making us doubt if the issue is just related with our infrastructure or with some more generic SAS or Microsoft update?
I also wonder if it's a broader issue with SAS and/or Microsoft. SAS Admin at my org contacted SAS since it's not just our org, and waiting on response.
We are also experiencing the same problem - working on some machines but not on others.
Thanks all. No we still haven't found a resolution yet. We have a temporary workaround the seems to satisfy the user for now. SAS support hasn't provided us with anything of significance yet.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: