Database locking

[Lhoj]

First of all, I don't know if that's the correct place for this post, if not any admin feel free to move it.

I have an Access 2013 database working great for the most part. I've recently written a VBA module to insert the data from Active Directory to the DB (mostly to include and/or update user data, emails/telephones and such). The module works great but after running it, the Access DB remains locked. It worked alright a couple of times (since I ran it a handful of times to retrieve and update all the info I wanted, changing target OUs and such) without any meaningful changes to the code (at least to the code that might be locking the DB) but most of the times it leaves the DB locked and unable to save changes until I close and reopen the DB again.


(Sorry it's in Spanish. It basically says "You don't have exclusive access to the database at this moment. If you continue making changes you might not be able to save them" or something in the line of that)

Now, the code is pretty simple. It opens a LDAP connection, retrieves the needed data through a LDAP query I edit to get the info I need, sets up a SQL transaction with all the SQL instructions required and it commits the transaction or rollbacks it if there's any error. In both cases LDAP connection is closed. Those are the two things that I guess could be leaving the DB locked and both are dealt with correctly. Here's an example of the code

Private Sub marcarUsuariosActivos()


    Set Conn = CreateObject("ADODB.Connection")
    Set iAdRootDSE = GetObject("LDAP://RootDSE")
    
    'especificamos el dominio y opcionalmente contenedor
    strDefaultNamingContext = ' domain and containers I want to check
    
    Conn.Provider = "ADsDSOObject"
    Conn.Open "ADs Provider"

    strQueryDL = "<LDAP://" & strDefaultNamingContext & ">;(&(objectCategory=person)(objectClass=user));samAccountName"
    Set objCmd = CreateObject("ADODB.Command")
    objCmd.ActiveConnection = Conn
    objCmd.Properties("SearchScope") = 2 ' Todo el arbol a partir del contenedor especificado
    objCmd.Properties("Page Size") = 500

    objCmd.CommandText = strQueryDL
    Set objRS = objCmd.Execute
    
    dao.DBEngine.BeginTrans
    On Error GoTo error_Trans
    sqlUpdate = "Update Usuarios set Activo = no"
    CurrentDb.Execute sqlUpdate, dbFailOnError

    'Recorremos los resultados
    While Not objRS.EOF

        'Activamos la casilla de activo para el usuario que coincida con el resultado leido de AD
         sqlUpdate = "Update Usuarios set Activo = yes WHERE Usuario_windows='" & objRS.Fields("samAccountName") & "'"
         
        CurrentDb.Execute sqlUpdate, dbFailOnError
        objRS.MoveNext
    Wend
    dao.DBEngine.CommitTrans
    Conn.Close
    Exit Sub

error_Trans:
    dao.DBEngine.Rollback
    MsgBox "Error en la transacción: " & Err.Description
    Conn.Close
    Exit Sub

End Sub

Any idea where the DB might be getting stuck? It's not that big of a deal as it happens with some function I only plan to launch manually and it fixes by reopening the DB (and the changes made by the transaction do apply and save) but I'd prefer to have it fixed.

Thank you so much

[Micron]

Probably not the issue but any object that you SET should be destroyed, as in Set obj = Nothing. I'd also advise to close record sets as part of the cleanup: rs.Close (then Set rs = Nothing).
If you get this message and you are the only one in the db, then it is likely that you've initiated a process that makes record changes, that process is not terminated properly and then you initiate another process. This can result in trying to edit records that now have a lock on them. You could try stepping through your code to see if that makes sense, but when posting code that triggers an error you should always report what line causes it in order to provide some clue. I suspect this post is related to another of yours about getting the logged on user name and if so, it looks like you elected to use a very complicated method instead of Environ suggestion that was made. There is also fOSusername function but that one might require a 64 bit version in some cases. Been a while since I've had to use it, so not sure.

[Lhoj]

First of all thanks for your answer Micron.

I haven't singled out any particular code line because the function runs ok, it ends and commits the transaction perfectly and the table gets updated. But after running it sometimes (and I say sometime because under the same circumstances and with the same code it might lock the DB or it might not) when trying to alter a table or even edit some VBA code the above error pops up, as if the DB file was locked by another user or process and couldn't be written afterwards, as if it was open in read-only mode.

The get user method you mention, I guess you're thinking about another person because yes, I barely mentioned a function to retrieve the user name in one of my threads about a different problem, but never asked about it nor did anyone provide solutions because it's working smoothly. Furthermore the above code doesn't make use of said function nor does it trigger any of the macros that call it, and it may still lock the DB freshly open without the getUser function ever being called, so it's out of the equation. Then again, if there's an Access environment variable storing the user working on the DB that can be accessed from a macro I'm more than happy to use it and get rid of the function, I just resorted to it because I didn't find any other method online and I'm not exactly an Access expert.

Back to the matter in hand, the above function is definitely the one causing the DB to lock. I had the rsObject closed before I posted the code but I took it out because it didn't resolve the issue. I tried to put it back in and 'reset' every set variable and it's still having the same behaviour. There's something else besides the function interfering though, because with the DB freshly open launching the above (or any variation closing objects or not) it will lock the DB maybe 2 out of 3 times.

I know it's hard to track and tackle the issue without having more info or access to the whole DB (which I'm not posting because it would take me too much time to take out all the sensible information) but if anyone knows why might that be happening I'd welcome any tip. If not well, it's a function I'm just going to run manually every now and then so I can always reload the DB afterwards. I'd rather fix it and above all, learn what causes that behaviour though.

Thank you all!

[Micron]

As long as no one else is in the db when the error happens, all I can think of at this point is try inserting a pause of 2 or 3 seconds between these updates -

"Update Usuarios set Activo = no" and sqlUpdate = "Update Usuarios...

or perhaps a half second or so between the .Execute in the loop. I'd use a function for this because it is quite handy to have for any time I might need a pause to eliminate such errors or other issues. Usually this function is found showing intSecs As Integer, but I've used my adaptation to add decimal seconds and it works. A half second over several hundred loops can start to add up to some time savings.

Public Function Pause(sngSecs As Single)
Dim Start As Variant
Start = Timer
Do While Timer < Start + sngSecs
    DoEvents
Loop
End Function

Lastly, creating an error log might clue you in to when it's happening. At the start, a string holds a message indicating the first step an action is taken. If you move on to the next step, alter the string accordingly. If doing so in a loop, use a counter so that the message indicates how far you got in the loop. Write the string to a text file within the error handler.