Correct way to handle two front ends trying to write to back end simultanously (1 Viewer)

InstructionWhich7142

Registered User.
Local time
Today, 22:45
Joined
Feb 24, 2010
Messages
199
I have Page level locking on if that makes any difference to the below

I'm trying to make multiple "worker" databases process data in a table (current in an ACCDB on a server, to be moved to SQL server at some point) there's too much data for 1 to process in a timely manner (as real-time as possible) so multiples are the way, I just need to make sure they all pick a record to process, identify they're going to process it then get on with it (will add error handling and expiration checks etc later)

Basically my idea in pseudocode is - "Update Top 1 where Running is False set UID to myUID" then run a second query to see if there is a record with myUID in and if so process it, rinse and repeat

I added a log table where each process writes it's own UID and the UID in the record it's processing to make sure there are no errors where a record was processed by the wrong worker or anything, code as below

Code:
Public Function speedtest()
DoCmd.SetWarnings False
Dim loopy As Long
Do Until loopy > 300
loopy = loopy + 1

Q2:
On Error GoTo Q2
DoCmd.OpenQuery "query2"


Query2
'UPDATE Query1 SET Query1.runby = userfx(), Query1.[time] = Now();

Query1
'SELECT TOP 1 Table1.ID, Table1.runby, Table1.Time 'FROM Table1 'WHERE (((Table1.runby) Is Null));


Code:
Sleep 20
Q3:
On Error GoTo Q3
DoCmd.OpenQuery "query3" [I][B]'append to LOG table to validate what was done[/B][/I]

Q4:
On Error GoTo Q4
DoCmd.OpenQuery "query4" [B][I]'clear out table1 ready for next loop[/I][/B]
Loop
DoCmd.SetWarnings True

MsgBox "done"
End Function
That all works ok 1 at a time, however the workers clash no end and even "on error" loops aren't keeping things moving, I get runtime error 3045:

Could not use file test.accdb file already in use,
if I debug and continue it's 50/50 if that worker will error again or if it'll be the second one, while it is waiting for me the other worker will finish 300 loops without issue.

How should I handle these errors better?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:45
Joined
Feb 28, 2001
Messages
27,001
This error says that your users are not correctly opening the database for sharing. If you have distributed front-ends, they have the same problem and it is an easy fix.

First, on a front-end file that you want to have shared access, open it. From the ribbon's File tab, find Options. Somewhere in the "Current Database" options you can set whether the database is opened exclusively or not. Error 3045 USUALLY says "was set for exclusive" and thus you get the conflict.

Second, to make this work smoothly, ANY TIME you have an update planned via query OR via a form that is bound to a table and that will save data, you must be sure to set everything for either OPTIMISTIC locking (forms and action queries) or NO LOCKS (reports and SELECT queries that will not be used indirectly for updating.) This will of course require you to interact with the property sheets for each query and form. However, if you do that once in one Front End file, you can copy that to as many folks as needed. You should get better results.
 

InstructionWhich7142

Registered User.
Local time
Today, 22:45
Joined
Feb 24, 2010
Messages
199
Thanks, I've checked the first point and I can only see an option in "Client Settings" for default open mode and that's set to Shared :)

Looking at the queries Properties they're all set to "Edited Record" I've just changed Query1 which was a select query to Edited Records and the issue persists
 

isladogs

MVP / VIP
Local time
Today, 22:45
Joined
Jan 14, 2017
Messages
18,186
Doc mentioned this in his response but I'm going to ask you explicitly.
Does each user have their own copy of the FE on their own hard drive OR is each user sharing the same copy of the FE stored somewhere on the network?
 

isladogs

MVP / VIP
Local time
Today, 22:45
Joined
Jan 14, 2017
Messages
18,186
OK, the next thing is to check the record locks both in Access options and on each form as explained by the Doc Man.
I would suggest setting to No Locks and then run an experiment where two users deliberately try to edit the same record on the same form at approximately the same time. Then repeat but with different records on the same form. See what happens in each case and report back.

You should also step through the code shown in your earlier post whilst running those tests. Identify which line triggers the error. In general running on error Goto ...such that the same lines of code can be run repeatedly is not a good idea. You have no 'escape route' so I think a redesign is advisable
 

InstructionWhich7142

Registered User.
Local time
Today, 22:45
Joined
Feb 24, 2010
Messages
199
It's actually an Update Query that throws the error despite the error handle statement and there's not a "No Locks" option on an update query,

This code needs to wait until it can run, it shouldn't escape
 

isladogs

MVP / VIP
Local time
Today, 22:45
Joined
Jan 14, 2017
Messages
18,186
Yes but you are running the query from a form.
All code needs to allow an escape route to prevent endless looping which will lead to the database crashing. You do not appear to have any error handling built in other than On Error GoTo the previous line
 

InstructionWhich7142

Registered User.
Local time
Today, 22:45
Joined
Feb 24, 2010
Messages
199
I'm running the query from VBA, literally using the green play button at the moment for testing, later it'll be fired by a form timer event or directly from AutoExec possibly,

I know better error handling is normally the case but these databases I'm experimenting with will be running automatically 24/7 and updating automatically, i'm basically making a task-scheduler type system to process data, there's no situation where I want them to stop running, go hard or go home :)
 

isladogs

MVP / VIP
Local time
Today, 22:45
Joined
Jan 14, 2017
Messages
18,186
Of course you don't want your application to crash but the reality is that no code is 100% guaranteed to be protected against all possible bugs no matter how thoroughly it is tested.
I have many commercial apps that run 24/7 and to help ensure the chances of failure are negligible, I have very robust error handling.
You NEED proper error handling to manage this application.

One more setting to check. In the VBE, go to Tools, Option, General and check your Error Trapping option.is set to Break on Unhandled Errors
 

InstructionWhich7142

Registered User.
Local time
Today, 22:45
Joined
Feb 24, 2010
Messages
199
How exactly do you mean proper error handling? what would that look like? I feel like for an automated app that "automatically retry" is about the best, along with "restart from the beginning"
 

isladogs

MVP / VIP
Local time
Today, 22:45
Joined
Jan 14, 2017
Messages
18,186
Hi Chris
Sorry about the delay in replying. I've had major problems posting messages on the site today

Each developer has their own preferred approach to error handling.
In my case, I add error handling similar to this to almost all procedures (with the exception of one line functions)

Code:
Private Sub lblWebsite_Click()

On Error GoTo Err_Handler

    Application.FollowHyperlink "http://www.mendipdatasystems.co.uk"
    
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in lblWebsite_Click procedure : " & Err.description
    Resume Exit_Handler

End Sub

In cases where there known errors that need to be handled, i do so with code similar to this:

Code:
Private Sub cmdPrint_Click()

On Error GoTo Err_Handler

    'your code here
    
Exit_Handler:
    Exit Sub

Err_Handler:
    If Err <> 2501 Then 'err 2501 - user cancelled printing
        MsgBox "Error " & Err.Number & " in cmdPrint procedure : " & Err.description
    End If
    Resume Exit_Handler
End Sub

In that case the error handling is used to suppress an unwanted message.
Very occasionally I will instead do a On Error Resume Next or On Error GoTo ..... but both should be avoided as far as possible as their use can make it difficult to control program flow and potentially cause endless loops or other issues
 

Users who are viewing this thread

Top Bottom