how to write TRANSACTION in access

[E_Gold]

hi
i try this:

BEGIN TRANSACTION
INSERT INTO TermNumTbl(TermNum) VALUES ('5')
COMMIT TRANSACTION

but i get error

how to write TRANSACTION in access ?

 

[Banana]

I doubt Access database engine can use the keyword "BEGIN TRANSACTION", "ROLLBACK" and "COMMIT" because the database engine doesn't support multiple statement in a single batch. What you'd do instead is to use either DAO or ADO to establish the transaction context.

Example:

Dim ws As DAO.Workspace
Dim db As DAO.Database

On Error GoTo ErrHandler

Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(CurrentDb.Name)

With db
  ws.BeginTrans
  On Error GoTo TransErrHandler
  .Execute "INSERT INTO TermNumTbl(TermNum) VALUES (5);
  ws.CommitTrans
  On Error GoTo ErrHandler
End With

ExitProc:
  On Error Resume Next
  Set db = Nothing
  Set ws = Nothing
  Exit <procedure type>
TransErrHandler:
  ws.Rollback
ErrHandler:
  Select Case Err.Number
     Case ...
     Case Else
        <generic error message
  End Select
  Resume ExitProc
  Resume

 

[jdraw]

Not sure if I understood Banana correctly, but I did the following based on his example to show you can have multiple statements within the Transaction.

'---------------------------------------------------------------------------------------
' Procedure : Banana1
' Author : Jack
' Date : 07/08/2011
' Purpose :From AWF (Banana) - using Transactions in Acc 2003 with multiple
' .executes within the scope of the transaction
'---------------------------------------------------------------------------------------
'

Sub Banana1()


Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim i As Integer
On Error GoTo TransErrHandler

Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(CurrentDb.name)

With db
ws.BeginTrans
On Error GoTo TransErrHandler
For i = 1 To 29
.Execute "INSERT INTO TermNumTbl(TermNum) VALUES (" & i & ");", dbFailOnError
Debug.Print i & " - " & Now
Next i

.Execute "Update termnumtbl set termNum = (10 * termNum) where termNum between 10 and 20", dbFailOnError
Debug.Print "Update completed " & Now
' another query
.Execute "DELETE FRom TermNumTbl where ID = 11", dbFailOnError
Debug.Print "Delete where ID = 11"

' Raise a custom error. and force a rollback by uncommenting next 2 lines
'========================================================================

' Err.Raise number:=vbObjectError + 1000, _
Source:="Banana1", _
Description:="My custom error description-- Forced Rollback"
' Debug.Print "My custom error description-- Forced Rollback"
ws.CommitTrans

On Error GoTo TransErrHandler
End With

ExitProc:
On Error Resume Next
Set db = Nothing
Set ws = Nothing
Exit Sub
ErrHandler:
TransErrHandler:
ws.Rollback

Banana1_Error:
If Err.number = vbObjectError + 1000 Then
Debug.Print "Custom error raised jed trans rolled back"
Else
MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure Banana1 of Module AWF_Related"
End If
Resume ExitProc

End Sub

 

[Banana]

jdraw, maybe I could have phrased my statement better - when I said Access doesn't support multiple statements in a single batch, I was referring to the fact that you cannot do something like this:

db.Execute _
  "INSERT INTO a (b, c) VALUES (1, 2); " & _
  "INSERT INTO a (b, c) VALUES (3, 4); " & _
  "INSERT INTO a (b, c) VALUES (5, 6); ", dbFailOnError

That is not valid for Access database engine and will cause errors. This is because there's 3 statement to a batch and that's not supported. You have to instead:

db.Execute "INSERT ..."
db.Execute "INSERT ..."
db.Execute "INSERT ..."

which is a single statement per a batch and thus valid for Access database.

The OP was using keywords "BEGIN TRANSACTION", "COMMIT" and "ROLLBACK" which are valid SQL keyword for controlling transaction and my original thought was that those keywords are not recognized in Access due to the fact that we cannot have multiple statements in a batch.

However, I need to correct myself because I went and test and found that the database engine does indeed in fact recognize those keywords but here's the catch - it's only recognized in ADO.

CurrentProject.Connection.Execute "BEGIN TRANSACTION;" 'Ok
CurrentProject.Connection.Execute "ROLLBACK;" 'Ok
CurrentDb.Execute "BEGIN TRANSACTION;" 'Errors, not valid SQL

So my assertion that the lack of support for multiple statements is irrelevant to Access' not recognizing the keywords OP was trying to use. It's simply just an ADO-only thing.