-
Begin/Rollback/Commit Transaction for access
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?
-
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:
[VBA]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[/VBA]
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
-
Great thanks
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules