How do I close an Access database located on a windows pc or local network using SAS 9.4

[Ryanb2]

I can't seem to find any simple code that closes an open file.  I was hoping to find an x command like I use routinely to delete files.  I'm trying to make sure an Access database is closed before writing to it.

options noxwait;
x close "C:\TEMP\test.accdb";

[RichardAD]

It depends on how or who has the data base file open.  If you are using the Access library engine and a SAS library reference to read from the data bases tables, clearing the libref will close the file handles.  Same if you are using ODBC, clearing the libref will disconnect from the data base and free up file locks.

libname accdb access "c:\temp\test.accdb" ... ;
...
libname accdb ; 

If on the local network it is possible another person or process has the data base open which would prevent operations such as deleting the db file.

[Ryanb2]

Thanks for your response, Richard.  I'm using proc export doing a full replace of a table (see code below).  I think this will let me export the file even if the database is open, but that seems risky, and if a form is open then I think it will kick out an error.  I'd like each user to run an automated job every evening to close the database in case they left it open.  But I can't seem to find code that will do that.  

PROC EXPORT DATA= sashelp.class 
            OUTTABLE= "CLASS" 
            DBMS=ACCESS REPLACE;
	DATABASE="C:\TEMP\test.accdb";
RUN;

[RichardAD]

An end-of-day scheduled job can run a powershell script that contains.

Get-Process | where name -EQ 'msaccess' | Stop-Process

If a user would not be active and has accidentally left Access open and is connected to the data base in question, shutting down Access will disconnect and free up its locks on the data base file.  It would have to be scheduled on each client machine that would be running Access.

[Ryanb2]

This looks like a promising solution, Richard.  Thank you!  I'm not as familiar with running powershell scripts but I'll look into this and get back to you.

[SASKiwi]

IMHO, if you are having to use workarounds like this then you have chosen the wrong tool for the job. Does your company have other multi-user database software to handle your requirement? Loading your data into a relational database like SQL Server seems like a better option to me as multi-user functionality is built-in. With SAS/ACCESS to ODBC you can work with any ODBC-compliant database.

[Ryanb2]

Thanks for your post. I should probably open another post about this to discuss further. I've worked with our IT department and with our SAS contact on alternatives. If it was just data storage then we would do something different, but it's everything else Access does that's really useful. We have several databases with user interfaces that allow for data entry, filters, quick links... Our IT department could probably create a GUI interface using another application but it makes us less self sufficient. Some are very simple and some are very complex. IT has their own timelines, versioning schedules, and they're expensive. You got any ideas?

[RichardAD]

You will find a bounty of information simply searching "migrate access forms to use SQL server".  Should be enough to guide you through sandboxing a pilot you can present to your IT guidance.  You might need to alter/update your SAS license in order to export your SAS data sets to SQL Server.

[Ryanb2]

Thanks, Richard. What's the learning curve for creating a user interface to facilitate data entry, with filters and quick links in SQL server so we can do this ourselves? I know our IT department can create something like this but for the reasons already mentioned it's just not going to work.

[SASKiwi]

@Ryanb2 - Perhaps you could consider a hybrid approach of continuing to use MS Access for data entry, but then use SQL Server for the back-end data storage where SAS can easily access it. That way involves a lot less change.

[RichardAD]

Exactly, just change the data source/ connection string behind the form

[Ryanb2]

Thanks, SASKiwi. I'll think about that but I still think I would need to close Access to remove the lock in this scenario.

[SASKiwi]

@Ryanb2 - But locks apply only to Access databases, and my suggestion is to move the databases / tables to SQL Server which doesn't have locking problems. Access would only be used for data entry etc.

[RichardAD]

A SQL Server administrator should be able to force remove any locks a client application (your Access data entry forms) may have gotten on a SQL Server table.