Run Time Error 3734 On Timer Event

[jlgray0127]

I am using this code:

Private Sub Form_Timer()
Dim varRetVal
varRetVal = DLookup("SystemUp", "Qry_Sys_Status_T")
If Nz(varRetVal, 0) = 0 Then
DoCmd.OpenForm "frmSystemExit", acNormal
End If
End Sub

This is set up on the On Timer Event when the database opens and displays the main menu.
I run this code (and 15 other users are running this on their front end) to look at "SystemUp" from "Qry_Sys_Status_T".


The Table Sys_Status_T only has 1 record on it. SystemUp is a Yes/No field I use to shutdown the front end on all users PCs if I need to do maintenance on one of the backend databases.

The code is running every 20 seconds to see if I updated the SystemUp Field. If I updated it, a form pops up warning the user I am shutting down their front end. There is a timer event on the pop up form, which closes Access after 1 minute to allow them to finish their work.
I then let my users know, manually, they are able to reopen their front end once I have completed work in the back end. This is typically just me repairing and compiling the backend.

Every once in a while, at least 2-10 times a day, the users are receiving the run time error 3734 and have to click end to stop the code from running.
I am not sure what is creating the issue, as it is so erratic. I am not a VBA expert by any means, and am assuming there is an update I can do to my variable, but have not been able to locate it.

Once you close the debugger by ending the code, the database works just fine for a period, at times, several hours, before the debugger pops up again. I have 15 backend users and a typical day, there are 8 active users working at one time in the database.

Any assistance would be greatly appreciated!

[Welshgasman]

And 3734 is what error?

[jlgray0127]

Access error code 3734 “The database has been placed in a state by user ‘Admin’ on machine… that prevents it from being opened or locked"

As no reports, beyond the debugger opening, are coming from my team, I am assuming if anything is being locked, it is the Sys_Status_T Table. Using the qry and the Dlookup, there should not be anyone changing a record on the table, so I am unsure why it would trigger the error to begin with. I am the only user who knows about the table, record, or would open it, so really puzzled why the debugger is is even triggering to begin with. Thank you for any support you can offer!

[Micron]

I think you omitted an important detail. The error reports which pc has the lock on the table, yes? Perhaps 1 or more users are doing something you didn't expect and it's always the same users? AFAIK, DLookup by itself will not put a lock on a record.
The message states what table is that is being locked or no? This might be a red herring:

I am assuming if anything is being locked, it is the Sys_Status_T Table

[jlgray0127]

Correct to the user being stated, I omitted that. Apologies. It does not tell you what table, but if you "Debug" it takes you to the line of code I shared below.
The exact error is:

Run-Time Error '3734':
The database has been placed in a state by user "Admin" on machine XXXXXXXXXX that prevents it from being opened or locked.

XXXXXXXXX Can be any operator's PC including the user who's PC the debugger opened on. For instance, the debugger triggered this morning and stated I myself had it locked.

[Welshgasman]

You could allow for the error?

[Micron]

Wondering if you can raise the error again, then open the table to see if the record lock icon is there. There is always the option to not have any record locking. Handling/bypassing the error would not be my first choice, especially if that leaves the lock in place. 20 seconds seems far too often to me (not that I think it will solve the problem, but who knows yet). I did this once or twice before and I figured I could initiate the lockout and wait 2 minutes for anyone's timer event to run. 15 or so people hitting the server every 20 seconds seems a bit much to me, but that's just my opinion.