How to avoid (undesired, intermittently-occurring) exclusive locks?

[sueb]

I have a database whose back end sits on a server, and each user has their own front end on their computer's desktop. Periodically (infrequently), for the first user to open their front end in the morning, Access writes an exclusive lock on the server, and no one else is able to get in until that user exits.

Is there a way for me to put into the front end something that would look at the lock and, if it were exclusive (and assuming Access can detect this internally) to have the front end close itself ("Application.Quit")? So far, it's never done this immediately a second time (personally, I think it's an Access bug), so I think this would solve this problem for us.

Any other suggestions about this will be eagerly accepted, as well!

[NeoPa]

No time now for full research, but you could start by looking into Application.SetOption("Default Record Locking", ...).

[sueb]

Thanks, NeoPa! I'll certainly do that, and will post back anything useful I discover.

[sueb]

Here's something:

On the Microsoft support site, there is this paragraph in the article "ACC: Introduction to .LDB Files (1.x/2.0)":

"However, the .LDB file does not give Microsoft Access information about who has a database opened exclusively, because this is handled by SHARE.EXE (or VSHARE.386 in Windows 3.1). Those programs do not return information to Microsoft Access about who has the database open."

So it begins to look as though I actually will not be able to get at the type of lock that has been created. Still researching, tho...

[NeoPa]

Interesting Sue, but if each instance of the opened database is checked for the sharing mode and curtailed if exclusive, then that may still get around the issue. It's worth progressing with I'd say.

[sueb]

The problem is that I don't (so far) see any way of actually finding out whether a lock is shared or exclusive.

[NeoPa]

I think you're going to wish you hadn't asked that one Sue. Try :

Application.GetOption("Default Record Locking")

[sueb]

:) Well, I already know what the "default" is; will this give me what the "current" is?

[NeoPa]

First off, I was looking at the wrong setting in my first reply. I was in a bit of a rush. It should have been the Default Open Mode option. That said, the parameter value for that is not "Default Open Mode", and I was unable to find what it was anywhere :-(

This only sets and shows the default mode. Not the current mode. The Default Open Mode can be overridden by the command used to invoke Access, either from a Command Line or a Shortcut.

The Option displayed is simply the Option (Default). There is no Option for the current setting of the Open Mode.

The following code determines this for you. As it stands it simply displays the value in the Immediate Pane of the VBA IDE, but can easily be changed to suit your needs :

Private Sub Form_Open(Cancel As Integer)
    Dim intFileNo As Integer
    Dim strOpenMode As String
    On Error Resume Next
 
    intFileNo = FreeFile
 
    Open CurrentProject.FullName For Input Access Read Shared As #intFileNo
    Close #intFileNo      ' Always close the file.
    strOpenMode = IIf(Err = 70, "Exclusive", "Shared")
    Debug.Print strOpenMode
    '------------------------------------------------------------------------
    '  If this file was opened and closed successfully, then it
    ' was opened in shared mode.
    '------------------------------------------------------------------------
End Sub

[sueb]

Okay, that's interesting. Just so I understand what's happening: first it Opens the current project to get the filename, then it attempts to Close it, which (I gather) gives an error, and this error will tell me whether the mode is "Exclusive" or "Shared".

That means, does it not, that if it came back "Exclusive", I could have the application shut itself down? That would be wonderful!

[NeoPa]

Actually, I believe the Open triggers the error. The Close is simply there to keep the code tidy (If the Open succeeded and it wasn't closed you'd leave an open table doing nothing but taking up resources and potentially even locking the file). Essentially though, yes you should be able to use it that way.

[sueb]

Lovely, NeoPa! I'll put that in and see if it doesn't do the trick for me. Thanks!

[sueb]

NeoPa, naturally, your answer in #9 was perfect. Thanks so much!

[NeoPa]

Always a pleasure Sue. Happy coding :-)