Begin/Rollback/Commit Transaction for access

[Blasphemer]

Hi,

Anyone knows if these exist in access, could you point at a piece of code for this?

Info: These are SQL transactions which are a bunch of queries, they change your database however if any of the queries fails the whole database reverts back to original state.

Does anyone know of a way for doing it?

[CreganTur]

Transaction processing is handled in Access via ADO (ActiveX Data Objects) connections. Here's an example of an ADO transaction from the Access 2007 Programming By Example book:

Sub CreateTransactionADO()
Dim conn As ADODB.Connection
On Error GoTo ErrorHandler
Set conn = New ADODB.Connection
With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\Acc07_ByExample\Northwind.mdb"
.Open
.BeginTrans

'insert a new customer record
.Execute "INSERT INTO Customers " & _
"Values ('GWIPO','Gwiazda Polarna'," & _
"'Marcin Garnia', 'Sales Manager', 'ul.Majewskiego 10'," & _
"'Warsawa', Null, '02-106', 'Poland', '0114822230445', Null)"

'insert the order for that customer
.Execute "INSERT INTO Orders" & _
" (CustomerId, EmployeeId, OrderDate, RequiredDate)" & _
" Values ('GWIPO', 1, Date(), Date()+5)"
.CommitTrans
.Close
MsgBox "Both inserts completed."
End With
ExitHere:
Set conn = Nothing
Exit Sub
ErrorHandler:
If Err.Number = -2147467259 Then
MsgBox Err.Description
Resume ExitHere
Else
MsgBox Err.Description
With conn
.RollbackTrans
.Close
End With
Resume ExitHere
End If
End Sub

You can get more info from Access Help, as long as you have a reference set to the ADO Object Library.

Also, to create a connection to your current open database (the database you're writing the code in) with ADO you would use: CurrentProject.Connection

HTH:thumb

[Blasphemer]

Great thanks :D