Transaction in DAO-



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?


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
strPM160num = "no number"
End If

'begin transaction
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

'clean up
Set db = Nothing
Set wrk = Nothing
Exit Sub

MsgBox "Transaction failed. Error: " & Err.Number & " Description: " &

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?


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


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

My failure branch is working. :)


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?


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question