topic Re: How do I close an Access database located on a windows pc or local network using SAS 9.4 in SAS Programming

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

Ryanb2 — Mon, 30 Dec 2024 23:28:20 GMT

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";

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

RichardAD — Tue, 31 Dec 2024 19:04:53 GMT

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.

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

Ryanb2 — Tue, 31 Dec 2024 19:39:54 GMT

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;

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

RichardAD — Tue, 31 Dec 2024 20:02:14 GMT

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.

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

Ryanb2 — Tue, 31 Dec 2024 20:07:11 GMT

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.

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

SASKiwi — Tue, 31 Dec 2024 23:19:38 GMT

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.

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

Ryanb2 — Fri, 03 Jan 2025 17:44:17 GMT

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?

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

RichardAD — Fri, 03 Jan 2025 20:01:48 GMT

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.

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

Ryanb2 — Fri, 03 Jan 2025 20:21:05 GMT

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.

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

SASKiwi — Sat, 04 Jan 2025 00:03:33 GMT

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

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

RichardAD — Sat, 04 Jan 2025 01:14:12 GMT

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

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

Ryanb2 — Sat, 04 Jan 2025 02:08:12 GMT

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

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

SASKiwi — Sat, 04 Jan 2025 21:19:36 GMT

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

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

RichardAD — Mon, 06 Jan 2025 15:05:27 GMT

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.