Solved Programmaticaly lock your database "You do not have exclusive access to the database at this time" (1 Viewer)

[monheimx9]

A new day, a new Access Error
But I can't blame Access, it was 100% my fault

And I want to share the experience with you because it took me no less than 5 hours to figure it out

To give a little bit of context:
I was refactoring my User class because it was (and it still is) badly written, but I also dit other small refactorisations on other places, to the extend that I forgot a few things I did
So yesterday after a hard day of work, I exported my sources with the beautiful pluggin https://github.com/joyfullservice/msaccess-vcs-addin as I always do, git commit, git push and called it a day

This morning I wanted to continue the refactorisation before attacking a new feature in my base, and guess what?
You do not have exclusive access to the database at this time

My first thought was, "Oh my god, did a new update messed up with my base again?"

I started to invastigate, try to rollback a few versions from Office... nothing change
Everytime I reached the login screen from the Front-End, I couldn't do any edit without triggering the error.
(It would work with the Shift-key pressed at the start tho)

Then I started to look what my login screen do at load

Private Sub Form_Load()
    TempVars("timetorefresh").Value = Now()
    TempVars("timeWhenOpen").Value = Now()
    TempVars("Environement") = 0                 'environement par défaut : prod
    TempVars("user_id").Value = 0
    DoCmd.OpenForm "FRM_ActiveConnections", acNormal, , , acFormReadOnly, acHidden
    Set cUSR = New cUser   
End Sub

The culprit was on the two last lines, because the cUser class is instanciated on the login form as well in the form FRM_ActiveConnections
Once these 2 lines were commented, the error got away, that's some progress I dare to say

I will spare you everything I did to find the error, I will now go straight to the point.

This code doesn't lock the database:

Private Property Get loadRS(ByVal strsql As String) As ADODB.Recordset
    God.Add_Call "cUser", "loadRS", "strsql = " & strsql
    On Error GoTo loadRS_Error
    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    With rs
        .source = strsql
        .ActiveConnection = cnn
        .CursorType = adOpenDynamic
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open
    End With
    Set loadRS = rs
    On Error GoTo 0
    Exit Property
loadRS_Error:
    God.Mistake "cUser", "loadRS", Err
End Property

But this one does:

Private Property Get loadRS(ByVal strsql As String) As ADODB.Recordset
    God.Add_Call "cUser", "loadRS", "strsql = " & strsql
    On Error GoTo loadRS_Error
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    With rs
        .source = strsql
        .ActiveConnection =  CurrentProject.Connection.ConnectionString
        .CursorType = adOpenDynamic
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open
    End With
    Set loadRS = rs
    On Error GoTo 0
    Exit Property
loadRS_Error:
    God.Mistake "cUser", "loadRS", Err
End Property

See the difference?
Yes, this is obviously the "ActiveConnection" property
If you pass the connection string instead of the object, it would everytime lock the database by the "Admin" user

It drove me nuts, because it was not "that obvious" for me to tell this function was the culprit

That's all, thank you for reading

 

[The_Doc_Man]

Thank you for your contribution!