UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Multiple Databases Trying To Delete The Same Records, Access 2016    
 
   
doctor9
post Jan 3 2018, 09:18 AM
Post#1


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Here's my setup: I have five instruments in my lab that are connected to five chillers. These instruments run very long tests that can go until the wee hours of the morning, after everyone has gone. I've created a database that periodically checks when the test on an instrument is complete, then shuts down the chiller assigned to that instrument. To make sure things are working smoothly, I write "status" text to a table like "Checked if test was running at 2am. Still running." or "Test was complete at 4am. Shut down chiller."

Since these status texts aren't really necessary after a few weeks, I've created a line of code that deletes any statuses that are older than a month:
CODE
CurrentDb.Execute "DELETE * FROM tblActivity WHERE DateDiff(""d"",Date(),[dteActivityDateTime])<-29;", dbFailOnError

Unfortunately, since all five instruments are opening five copies of the frontend and doing their checks at the same time, I get an error on the above line of code when they all try to delete essentially the same set of records. Should I change "dbFailOnError" to something else? Remove that bit entirely? Or do I need to use the "On Error Resume Next" for that line of code? Or something else I haven't thought of?

Thanks,

Dennis
Go to the top of the page
 
nvogel
post Jan 3 2018, 09:53 AM
Post#2



Posts: 869
Joined: 26-January 14
From: London, UK


Perhaps not a complete solution but maybe putting the DELETE into a sargable form will help:

DELETE FROM tblActivity WHERE [dteActivityDateTime]<=DateAdd("d",-29,Date());


Go to the top of the page
 
cheekybuddha
post Jan 3 2018, 10:16 AM
Post#3


UtterAccess VIP
Posts: 10,460
Joined: 6-December 03
From: Telegraph Hill


Why not implement a random delay of between say 5 and 10 seconds before calling the delete line?

Pseudo:
Write Status update
Pick a random number between 5 and 10
Sleep random number
Execute Delete

hth,

d
Go to the top of the page
 
doctor9
post Jan 3 2018, 10:43 AM
Post#4


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


David,

Rather than a random number generator, I think I'll implement a consistent delay along these lines:

chiller 1: Wait 0 seconds
chiller 2: Wait 10 seconds
chiller 3: Wait 20 seconds
chiller 4: Wait 30 seconds
chiller 5: Wait 40 seconds

That will eliminate the chance of overlap, I think.

I'm just wondering if there's a simpler, more elegant way of handling the error, though. I was only -told- about the error, so I don't know the exact text, but apparently it was along the lines of "The records were already deleted". But the error caused VBA to stop, thus leaving the chiller on overnight, which is not acceptable.

Thanks, though!

Dennis
Go to the top of the page
 
cheekybuddha
post Jan 3 2018, 12:09 PM
Post#5


UtterAccess VIP
Posts: 10,460
Joined: 6-December 03
From: Telegraph Hill


I'm surprised there is a problem if each chiller hooks in to its own front-end (which is how I understood your description).

If the records are gone nothing should be deleted by the other calls, so perhaps some more investigation of the precise error is required.

But if the calls are made at exactly the same time then it is conceivable that an error might occur due too a race that records existed as it started to delete but then couldn't find them.

Perhaps just handle the error!

hth,

d
Go to the top of the page
 
doctor9
post Jan 3 2018, 01:13 PM
Post#6


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


David,

> Perhaps just handle the error!

That's what I'm asking for help with. smile.gif

Dennis
Go to the top of the page
 
cheekybuddha
post Jan 3 2018, 01:45 PM
Post#7


UtterAccess VIP
Posts: 10,460
Joined: 6-December 03
From: Telegraph Hill


>> That's what I'm asking for help with. smile.gif <<

Oops! ohyeah.gif

Something like:
CODE
Sub YourProc()
On Error GoTo Err_YourProc

' ... some code

  CurrentDb.Execute "DELETE * FROM tblActivity WHERE [dteActivityDateTime]<=DateAdd('d',-29,Date());", dbFailOnError
  
Resume_Here:
' ... rest of code

Exit_YourProc:
  Exit Sub

Err_YourProc:
  Select Case Err.Number
  Case 12345   ' <-- Use actual error no!
'   Doh! Another chiller beat me to it.
    Resume Resume_Here
  Case Else
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
           "Description: " & Err.Description & vbNewLine & vbNewLine & _
           "Sub: YourProc" & vbNewLine & _
           IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
           "Module: basYourModule", , "Error: " & Err.Number
  End Select
  Resume Exit_YourProc
  
End Sub


hth,

d
Go to the top of the page
 
doctor9
post Jan 3 2018, 02:38 PM
Post#8


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


David,

Well, I just need to figure out how to replicate the error on purpose so I can trap which error code is being generated, I guess.

Thanks,

Dennis
Go to the top of the page
 
cheekybuddha
post Jan 3 2018, 04:06 PM
Post#9


UtterAccess VIP
Posts: 10,460
Joined: 6-December 03
From: Telegraph Hill


If you have difficulty reproducing the error to find out its number you can do something like:
CODE
Sub YourProc()
On Error GoTo Err_YourProc

' ... some code

' Turn off error handling
  On Error Resume Next

  CurrentDb.Execute "DELETE * FROM tblActivity WHERE [dteActivityDateTime]<=DateAdd('d',-29,Date());", dbFailOnError
  If Err Then
    Debug.Print Format(Now, "yyyy-mm-dd hh:nn:ss"), "DELETE ERROR: ", Err.Number  ' Or write to db if the frontend closes before you get to it
    Err.Clear
  End If

' Turn error handling back on
  On Error GoTo Err_YourProc
  
' ... rest of code

Exit_YourProc:
  Exit Sub

Err_YourProc:
  Select Case Err.Number
  Case Else
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
           "Description: " & Err.Description & vbNewLine & vbNewLine & _
           "Sub: YourProc" & vbNewLine & _
           IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
           "Module: basYourModule", , "Error: " & Err.Number
  End Select
  Resume Exit_YourProc
  
End Sub


hth,

d
Go to the top of the page
 
doctor9
post Jan 3 2018, 04:17 PM
Post#10


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


David,

that's a good idea. That way, as long as the users don't close the database after an error, I can just check the Immediate Window for the details.

Thanks again!

Dennis
Go to the top of the page
 
kfield7
post Jan 3 2018, 04:24 PM
Post#11



Posts: 891
Joined: 12-November 03
From: Iowa Lot


Have you looked here?

I would think one of these is the culprit and you could probably safely error out of all of these.

3109 Record(s) cannot be deleted; no delete permission on '|'.

3167 Record is deleted.

3329 Record in table '|' was deleted by another user.

3643 One of the records in the recordset was deleted by another process.
Go to the top of the page
 
doctor9
post Jan 3 2018, 04:36 PM
Post#12


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


kfield7,

Thanks, I had not looked there. Based on the description I was given, I think 3643 or 3329 are the likely culprits.

I've implemented a call to my status subroutine with the error code if it happens again. I've also done this right before the deletion attempt:

CODE
Sleep CInt(ChillerNum() * 1000)


Sleep is a simple "pause x amount of milliseconds" thing. ChillerNum is a user-defined function that returns the number between 21 and 25 as text, so I convert it to a number then wait that number of seconds, depending on which instrument's frontend is running the code. We'll see if that's enough one way or another.

Frankly, this error hasn't come up since my last update a few weeks ago, but any error that halts the process needs to be addressed in some way.

Dennis
Go to the top of the page
 
cheekybuddha
post Jan 3 2018, 04:45 PM
Post#13


UtterAccess VIP
Posts: 10,460
Joined: 6-December 03
From: Telegraph Hill


Nit:
CODE
Sleep CInt(ChillerNum()) * 1000


No point casting after an implicit conversion!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st October 2018 - 09:22 AM