ACK! What's wrong with this error trapping?

M

Maury Markowitz

We have a bunch of very similar tables that we periodically clean out. I
wanted to place all the clearing out code in one place, erroring over the
ones that don't work (see below).

However this only works for the first attempt, on the second one it still
returns the error. Why? I've tried lots of things like ERR.CLEAR, ON ERROR 0,
ON ERROR RESUME NEXT, etc., but nothing helps.

Why will this only trap once?

On Error GoTo tryAgain1
Set rsttemp = New ADODB.Recordset
rsttemp.Open "DELETE FROM " & tempname & " WHERE lastModifiedBy = '" &
loggedInUserId & "'", cnn, adOpenForwardOnly, adLockOptimistic
GoTo exitOut
tryAgain1:
On Error GoTo tryAgain2
Set rsttemp = New ADODB.Recordset
rsttemp.Open "DELETE FROM " & tempname & " WHERE lastModifiedById = '" &
loggedInUserId & "'", cnn, adOpenForwardOnly, adLockOptimistic
GoTo exitOut
tryAgain2:
On Error GoTo tryAgain3
Set rsttemp = New ADODB.Recordset
rsttemp.Open "DELETE FROM " & tempname & " WHERE modifiedBy = '" &
loggedInUserId & "'", cnn, adOpenForwardOnly, adLockOptimistic
GoTo exitOut
tryAgain3:
exitOut:
Err.Clear
On Error GoTo 0
 
J

Jeff Boyce

Maury

I'm not clear on why you are using code to empty the table(s).

Can you create a Delete query that does the job?

Good luck

Jeff Boyce
<Access MVP>
 
M

Maury Markowitz

I'm not clear on why you are using code to empty the table(s).
Can you create a Delete query that does the job?
Click to expand...

This appears to be either a hidden oddity of VB, or an outright bug. It
doesn't matter what I am trying to do, this is a general question about error
handling in VB. Unless I misunderstand the way VB handles errors, this code
SHOULD work.

Maury
 
J

Jeff Boyce

Maury

I understand that you want it to work.

What happens when you build and run a delete query instead of a in-code SQL
statement?

Consider this a "trouble-shooting" question, trying to uncover under which
conditions your effort fails, and with what specific error message(s).

Jeff Boyce
<Access MVP>
 
D

Dirk Goldgar

Maury Markowitz said:
We have a bunch of very similar tables that we periodically clean
out. I wanted to place all the clearing out code in one place,
erroring over the ones that don't work (see below).

However this only works for the first attempt, on the second one it
still returns the error. Why? I've tried lots of things like
ERR.CLEAR, ON ERROR 0, ON ERROR RESUME NEXT, etc., but nothing helps.

Why will this only trap once?

On Error GoTo tryAgain1
Set rsttemp = New ADODB.Recordset
rsttemp.Open "DELETE FROM " & tempname & " WHERE lastModifiedBy = '" &
loggedInUserId & "'", cnn, adOpenForwardOnly, adLockOptimistic
GoTo exitOut
tryAgain1:
On Error GoTo tryAgain2
Set rsttemp = New ADODB.Recordset
rsttemp.Open "DELETE FROM " & tempname & " WHERE lastModifiedById
= '" & loggedInUserId & "'", cnn, adOpenForwardOnly, adLockOptimistic
GoTo exitOut
tryAgain2:
On Error GoTo tryAgain3
Set rsttemp = New ADODB.Recordset
rsttemp.Open "DELETE FROM " & tempname & " WHERE modifiedBy = '" &
loggedInUserId & "'", cnn, adOpenForwardOnly, adLockOptimistic
GoTo exitOut
tryAgain3:
exitOut:
Err.Clear
On Error GoTo 0
Click to expand...

I think it has to do with the fact that you never Resume from the
error-handlers. When you're in an error-handling routine, the previous
On Error statement is effectively suspended. Try something like this,
if you insist on writing your code that way:

Sub TestErr()

On Error GoTo tryAgain1
Set rsttemp = New ADODB.Recordset
rsttemp.Open _
"DELETE FROM " & tempname & _
" WHERE lastModifiedBy = '" & loggedInUserId & "'", _
cnn, adOpenForwardOnly, adLockOptimistic
GoTo exitOut
tryAgain1:
Resume tryAgain1A
tryAgain1A:
On Error GoTo tryAgain2
Set rsttemp = New ADODB.Recordset
rsttemp.Open _
"DELETE FROM " & tempname & _
" WHERE lastModifiedBy = '" & loggedInUserId & "'", _
cnn, adOpenForwardOnly, adLockOptimistic
GoTo exitOut
tryAgain2:
Resume tryAgain2A
tryAgain2A:
On Error GoTo tryAgain3
Set rsttemp = New ADODB.Recordset
rsttemp.Open _
"DELETE FROM " & tempname & _
" WHERE lastModifiedBy = '" & loggedInUserId & "'", _
cnn, adOpenForwardOnly, adLockOptimistic
GoTo exitOut
tryAgain3:
exitOut:
Err.Clear
On Error GoTo 0

End Sub
 
Top