Transaction in DAO-

[Loralee]

I am working on a transaction and am testing the FAIL portion of the
transaction. In order to provoke failure (to test the failure branch- the
success branch works) I changed the name of the table it is to save in. (I
want the error to and failure to be reported and the transaction to be rolled
back.) But my errorhandler is not firing on error. Access is stepping in
and using it’s own error message.

1) Is dbFailonError the wrong argument to make this happen? (What should I
use instead?) When I comment out dbFailOnError I get the exact same results-
Access standard error message.
2) Did I mess up somewhere else?

Thanks-
Loralee

I am working in Access 2000 and running XPPro
******************
Private Sub cmdTrack_Click()
'track the date and PM160 number to record work done for time study
' will only track during time study month so will manually increment via click

Dim wrk As DAO.Workspace
Dim db As DAO.Database

Dim lngID As Long
Dim strPM160num As String
Dim dtmDate As Date
Dim strSQL As String
Dim dtmTime As Date

Set wrk = DBEngine(0)
Set db = CurrentDb
On Error GoTo TransErrorHandler

dtmDate = Date
dtmTime = time
lngID = Me.ID

If Not IsNull(Me.PM160) And Me.PM160 <> "" Then
strPM160num = Me.PM160
Else
strPM160num = "no number"
End If

'begin transaction
wrk.BeginTrans
strSQL = " INSERT INTO tblTrackWork(pm160num, ptid, workdate,
worktime) " & _
"Values('" & strPM160num & "', " & "'" & lngID & "', " & "'"
& dtmDate & "', '" & dtmTime & "')"

db.Execute strSQL ', dbFailOnError
MsgBox "Updated Tracking"
'DoCmd.RunSQL (strSQL)

wrk.CommitTrans dbForceOSFlush 'dbflushoscachewrites per KB
dbForceOSFlush works with Windows 95 or NT but not in Novel environment



TransExit:
'clean up
wrk.Close
Set db = Nothing
Set wrk = Nothing
Exit Sub

TransErrorHandler:
MsgBox "Transaction failed. Error: " & Err.Number & " Description: " &
Err.Description
wrk.Rollback

Resume TransExit

End Sub

 

[Douglas J. Steele]

Your code looks okay to me (assuming you keep the dbFailOnError in). What
does the error message you get say?

 

[Loralee]

The error message is coming from VBA-
"Runtime Error 3192 could not find output table tblTrackWorkn"

To provoke failure I changed the SQL to say "FROM tblTrackWorkn" when the
table is really tblTrackWork. It doesn't even run my custom handler

 

[Loralee]

Oops!
I had "Break on all errors" checked instead of "Break on UNHANDLED errors".

My failure branch is working.

THANKS!

 

[Tom van Stiphout]

On Sat, 5 Jan 2008 15:42:02 -0800, Loralee

I am assuming your back-end database is an MDB?
If so, you need to wrap your date values with #-signs.

Still, that's no excuse for the error you are receiving. Something
else funny is up. Can you reproduce with a simple query in the
Northwind sample database?

-Tom.