Begin/Rollback/Commit Transaction for access
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?
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:
Dim conn As ADODB.Connection
On Error GoTo ErrorHandler
Set conn = New ADODB.Connection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\Acc07_ByExample\Northwind.mdb"
'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)"
MsgBox "Both inserts completed."
Set conn = Nothing
If Err.Number = -2147467259 Then
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