Force all users to disconnect from 2010 Access backend database

Question

We have multi user frontend/backend MS Access 2010 application. We added a process that will close remote frontends when we want to do work on backend such as compact and repair. This is timer based check on table field that if has certain value will close the application.

I do two checks to see if users are connected to database:

• i have login/logout process and can see who is still logged in (its form based so is fallible eg they close form but frontend is still open).

• i used .ldb file viewer to see if anything is still connected

Two questions:

• is there any possibility that a connection to backed could exist if it wasn't viewable with ldb viewer?

• is there any bullet proof 100% certain way to forcefully disconnect all connections from backend?

Answer 1

Users put "locks" into an LDB. An LDB is a kind of database of locks for MS Access.

Users also put "locks" onto LDB's and MDB's. These locks are part of the native database primitives provided by Windows and Windows networking.

If there are no locks IN the ldb, the user is not writing into the MDB.

If there are no locks ON the mdb, the user is not using that Windows/Networking service.

To get exclusive locking on an MDB, you need both. MSAccess won't let you exclusive lock while the LDB has entries showing someone else is using the database, and Windows won't give you an exclusive lock while it has locks showing someone else is using the database.

To 100% bulletproof empty the LDB, you delete the LDB. That is why Access always attempts to delete the LDB on close. If Access crashes, or the network is disconnected, or the PC or Server is turned off, the LDB will have data ("locks") that have not been deleted. You get rid of them by deleting the LDB.

Access won't be able to delete the LDB if a user still has Windows/Network locks on the LDB. Since Access is ignoring existing "lock" entries to clear broken "lock" entries, this is how Access/Windows prevents Access deleting the LDB while another user is still using it.

If EITHER the Windows locks or the Access/LDB "locks" exist, you can't get exclusive ownership. If ONLY the Access/LDB "locks" exist, you can delete the LDB, and then get exclusive ownership.

If ANY Windows/Network locks exist, nothing you do with Access can allow you to disconnect another person from the network. You need to go to the Server/PC that hosts the file, and force the disconnection. Access is not a network administration program that does this for you.

Last I looked, Windows Server was set by default to time-out broken file connections 15 minutes after the network session was lost. So if you turn off a PC, wait 15 minutes, then the Windows/Network locks will go (default timing). Alternatively: Admin tools, Computer Management,System Tools,Shared Folders, Sessions. Select the file you wish to unlock, and delete the session.

Answer 2

A fix exists, and I have it in place for an MS-Access Application I maintain. However, it's not convenient, and it's not simple to implement.

First, some background on locking files and the locked file.

The locking file is a useful tool but it's an indicator, not the actual lock: you can edit it but that has no effect.

Live MS-Access sessions can keep a file lock on the .accdb or .mdb back-end database file if they edit data, change an object (eg: index a table) or perform a maintenance task. The last two items in that list are rare - it takes some effort to affect another database file outside the one you've opened in your session, but it is sometimes observed in the wild - but closing those sessions should release the lock and almost always will.

Crashed MS-Access sessions can keep a file lock on the .accdb or .mdb back-end database file. Forced restarts of the offending machines should release the lock, and mostly will.

You can trace those using the lock file (.ldb or .laccdb)

Third-party reporting applications using ODBC or ADO to your Access database should have their connections set read-only, so that they can't lock the file. If you're lucky, the system owners use a dsn file you control, and you can see that setting.

Some reporting apps, like QLikView, are opaque interfaces that demand the full filename and nobody knows what happens next: maybe it locks, maybe it doesn't. You have no way of knowing.

Some reporting apps - and Excel Pivot Tables - may link to your db, and the owners simply don't tell you. If their connections are badly configured, they can lock your table while retrieving: and terminating the client session can leave the lock in place. Bored Excel users watching a slow pivot 'refresh' do this all the time.

Worse, some third-party systems may well need to write data: this should be avoided - go through the Access client application! - but you don't always get to make that decision.

If you're lucky, the connection object is configured correctly, so the machine ID is in the connection string, ensuring it'll turn up in the locking file and allow you to locate the machine with the lock...

Maybe they do that: often they don't.

...Note that row-locking queries, page-locking queries, and table-locking queries are functionally identical for the issue that matters to you: if you need to do something (like compact and repair) at the 'file' level, the file is locked against you. Until that lock is released, you're locked out.

You may or may not be able to identify the machine. You may or may not be able to force them off the network. They may or not release the lock successfully in normal operation, and they almost certainly won't if they crash out.

So: it's not a pretty picture.

Your Best solution, in a corporate environment, is to have a direct line to a network administrator with the 'finger' tool to identify who is locking the database file, the ability to contact the user, and the ability to disconnect the offending session.

The modes of failure are:

• If your organisational structures do not allow you to establish that line of communication, you're screwed.
• If your organisation's network administrators do not have the ability or the authority to do that in a useful timeframe, you're screwed.
• If your organisation's network administrator chooses not to do it, you're screwed.

However, I do have a fix in place for my MS-Access Application:

The workaround I use is extremely aggressive - not quite the 'nuclear option' of putting a shovel through the building's power supply, or giving in to the sysadmin's demands for human sacrifice, but deeply unpleasant and a ridiculous amount of work to implement:

1. Configure the clients - your MS-Access front-end applications - to switch between named back-end databases easily.
2. Likewise, the dsn files you have written and allowed other apps to use should be accessible to a pre-existing script to edit the 'DBQ='or 'Data Source=' clause to a new filename.
3. Grab a copy of the database file, take it down to a local temp folder for the maintenance work you needed to do, and post it back up to your application's 'Data' folder under a new name.
4. Now run the 'Change back-end' script or VBA function...
5. ...And to hell with anyone else who's using the old, locked, back-end file.

Hint: implementing your front-end application so that it's regularly polling the back-end database (or some other resource, your worst case scenario is a total lockout) for a 'Maintenance shutdown' or 'Change database' instruction is a smart thing to put in your 'Main Menu' form's timer event.

Also, there's code out there to automate switching the source db for linked tables.

Answer 3

is there any possibility that a connection to backed could exist if it wasn't viewable with ldb viewer?

Yes, I have had on numerous occasions had issues where the the backend was locked but I could not see anything with an ldb viewer. In these cases, we have even resulted in doing hard-shutdown on the machines that users access the DB from. Awful solution but in times of need you are sometimes desperate.

is there any bullet proof 100% certain way to forcefully disconnect all connections from backend?

No, I have not been able to find a 100% way to force everyone out of the backend DB. The closest thing that I found to perfect was using Chris Symonds Timeout-Force Out code located on Utter Access

This database accomplishes several tasks:

1. Allows developer to go to any PC using db and force all users off a split db so that the developer can open db exclusively to modify/maintain db.
2. Kicks users off db if they haven’t used db in a specified number of minutes set by developer.
3. Allows developer to specify a time each day to force all users off split db and close the db for backup, compile or whatever.
4. All functions are optional and may be bypassed with no extra coding, use only what you need for each db.

I implemented his code and it works about 99% of the time, but there are still those occasions where MS Access gets a bit touchy and I cannot see why the DB is locked or force-everyone out.

Answer 4

"I do two checks to see if users are connected to database"

If you need to open the db exclusively, you can skip the other checks and just check whether you can do it.

Public Function CheckExclusive(ByVal pFullPath As String) As Boolean
    Dim blnReturn As Boolean
    Dim cn As Object
    Dim strConnection As String

On Error GoTo ErrorHandler

    strConnection = "Provider=" & _
        CurrentProject.Connection.Provider & _
        ";Data Source=" & pFullPath & ";"
    Set cn = CreateObject("ADODB.Connection")
    cn.Mode = 12& ' adModeShareExclusive '
    cn.Open strConnection
    blnReturn = True
    cn.Close

ExitHere:
    On Error Resume Next
    Set cn = Nothing
    On Error GoTo 0
    CheckExclusive = blnReturn
    Exit Function

ErrorHandler:
    blnReturn = False
    GoTo ExitHere
End Function

Then call that function with the full path to your db file.

If CheckExclusive("C:\SomeFolder\YourDb.mdb") = True Then
    ' do what you need here which requires exclusive access: '
    '     make backup; compact; whatever '
End If