DAO/ADO timeouts can't be caught?

  • Thread starter Thread starter Maury Markowitz
  • Start date Start date
M

Maury Markowitz

I have a table that mirrors a table in an external data source - it is
a duplicate in every way. Every day we run a script to compare the
local copy with the external, and that way we find changes between the
two. When these changes are handled, we update the local copy.

One of the updates is deleting rows. Somewhere in my code I'm locking
the local table and deletes most often fail. I have looked everywhere
trying to find the lock, but so far I've failed. Instead, I've decided
to simply write down a notice in a little scratch table, so the user
can go back and manually delete it later (which, oddly, never fails).
So I simply wrapped the DELETE in an On Error, and added a handler.

But what I'm finding is that in most cases, the On Error does not
fire! Instead, the timeout fails, and a dialog comes up asking you if
you want to debug. Can anyone offer any insight here? No, there are no
other error handlers in this procedure. Is it the Goto 0?

Here's my code...

Set rstSource = db.OpenRecordset("qryCashChange", dbOpenSnapshot,
dbReadOnly)
While rstSource.EOF = False
If Not IsNull(rstSource!postNum) Then
Set rstCashCache = New ADODB.Recordset
On Error GoTo GOTTIMEOUT
rstCashCache.Open "DELETE FROM tblmatch WHERE postnum=" &
rstSource!postNum, cnn, adOpenStatic, adLockReadOnly
On Error GoTo 0
GoTo DIDNOTGETTIMEOUT

GOTTIMEOUT:
Call WriteWarning("PAM Import", "Timed out while trying to delete
postnum " & rstSource!postNum & " from tblpammatch. Do it manually.")

DIDNOTGETTIMEOUT:
End If
rstSource.MoveNext
Wend
 
Maury Markowitz said:
I have a table that mirrors a table in an external data source - it is
a duplicate in every way. Every day we run a script to compare the
local copy with the external, and that way we find changes between the
two. When these changes are handled, we update the local copy.

One of the updates is deleting rows. Somewhere in my code I'm locking
the local table and deletes most often fail. I have looked everywhere
trying to find the lock, but so far I've failed. Instead, I've decided
to simply write down a notice in a little scratch table, so the user
can go back and manually delete it later (which, oddly, never fails).
So I simply wrapped the DELETE in an On Error, and added a handler.

But what I'm finding is that in most cases, the On Error does not
fire! Instead, the timeout fails, and a dialog comes up asking you if
you want to debug. Can anyone offer any insight here? No, there are no
other error handlers in this procedure. Is it the Goto 0?

Here's my code...

Set rstSource = db.OpenRecordset("qryCashChange", dbOpenSnapshot,
dbReadOnly)
While rstSource.EOF = False
If Not IsNull(rstSource!postNum) Then
Set rstCashCache = New ADODB.Recordset
On Error GoTo GOTTIMEOUT
rstCashCache.Open "DELETE FROM tblmatch WHERE postnum=" &
rstSource!postNum, cnn, adOpenStatic, adLockReadOnly
On Error GoTo 0
GoTo DIDNOTGETTIMEOUT

GOTTIMEOUT:
Call WriteWarning("PAM Import", "Timed out while trying to delete
postnum " & rstSource!postNum & " from tblpammatch. Do it manually.")

DIDNOTGETTIMEOUT:
End If
rstSource.MoveNext
Wend
Click to expand...


Hi, Maury -

I see several problems with your code, but I think the probable cause of
your error-handler not being triggered is that you never execute a Resume
statement. An error-trap is not raised from within itself; rather, the
error is passed up to the next-higher error-handler.

I also don't see any point in using a recordset object (rstCashCache) to
execute your delete, but having done so, you don't ever close it. I don't
like relying on the default cleanup mechanism of the recordset.

Try your code like this instead:

'----- start of revised code -----

' ... somewhere up here you set define and set cnn ...

Set rstSource = db.OpenRecordset( _
"qryCashChange", dbOpenSnapshot, dbReadOnly)

While rstSource.EOF = False

If Not IsNull(rstSource!postNum) Then

On Error GoTo GOTTIMEOUT

cnn.Execute _
"DELETE FROM tblmatch WHERE postnum=" & _
rstSource!postNum, _
Options:=adCmdText Or adExecuteNorecords

On Error GoTo 0

End If

rstSource.MoveNext

Wend

' ... maybe you have extra code that goes here ...

Exit_Point:
Exit Sub

GOTTIMEOUT:
Call WriteWarning( _
"PAM Import", _
"Timed out while trying to delete postnum " & _
rstSource!postNum & _
" from tblpammatch. Do it manually.")

Resume Next

End Sub
'----- end of revised code -----

I'm assuming that your connection object cnn represents an external
connection, and is not a connection to the same database where this is
running. Otherwise, why not use DAO and Currentb instead?\

I also think it would behoove you to find out what is locking the table and
causing your updates to fail.
 
I see several problems with your code, but I think the probable cause of
your error-handler not being triggered is that you never execute a Resume
statement.  An error-trap is not raised from within itself;  rather, the
error is passed up to the next-higher error-handler.
Click to expand...

Can you explain this a little more? I have MANY examples of code like
this, and the errors are always trapped - as far as I know!

Maury
 
Maury Markowitz said:
Can you explain this a little more? I have MANY examples of code like
this, and the errors are always trapped - as far as I know!
Click to expand...


When an error is raised, control is passed to the most recently established
error-handler. The scope of that error handler is terminated when a Resume
statement is executed, or (I think) when the procedure containing the
error-handler is exited. If an error occurs *within the error-handling
code*, before a Resume statement is executed, then control is transferred to
the next most recently established error-handler, not to the error handler
within which the error was raised. Thus an infinite loop is avoided, and it
is possible to pass errors "up the line" to a different handler that is
better able to deal with them.

Try stepping through the code in this demo routine to see what happens:

'----- start of code -----
Sub DemoErrorHandling()

Dim I As Integer, J As Integer
Dim Iteration As Integer

On Error GoTo ERR1

I = 1
J = 0

I = I / J
Debug.Print "I = "; I
NEXT_LINE:
I = I / J
Debug.Print "NEXT_LINE: I = "; I

Exit_Point:
Debug.Print "Exit"
Exit Sub

ERR1:
Debug.Print "ERR1: Error = "; Err.Number, Err.Description
Iteration = Iteration + 1
If Iteration < 2 Then
Resume NEXT_LINE
Else
GoTo NEXT_LINE
End If

End Sub

'----- end of code -----
 
Back
Top