Receiving a file already in use where file resides

[KAS25]

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

[EyalGonen]

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...

[andreas_lds]

Does the library ABCOSPPS point to S:\sas\\access\ABCOSPPS_Current.accdb? Proc export needs exclusive access to the access-file.

[KAS25]

YES  I believe everything is in order with that.  We just can't find anything that has that file in use already.

[EyalGonen]

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...

[andreas_lds]

---

@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.

[a_SAS_sin]

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

[Michelle]

Also started having this issue at my organization this week.

[KAS25]

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.

[cb4jc4]

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 😞

[a_SAS_sin]

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?

[Michelle]

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.

[cb4jc4]

Thanks for the update! I actually noticed the file location factor - I’ve
been swamped and haven’t tested the workaround ye but realized .accdb files
on a shared g drive versus the network share did not have the “file in use”
error message. I’ll hopefully find some time to further explore this
evening but solid workaround if it works. The core issue for us seems to
have started 12/31/2021 which has me confused on what could be the problem
but I wonder the same thing. I’ve reached out to our network team and will
hopefully have more information soon.

[JuanGSR]

We are also experiencing the same problem - working on some machines but not on others.

[KAS25]

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.