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
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));
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?
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?