I am developing a small desktop application using C#.NET and MS-Access. I don't have any prior experience of MS-Access. I want to know if we can use transactions in Ms-Access or not.
I have the below mentioned situation.
Insert in Tbl1
Insert in Tbl2
I want to insert in tbl2 only when insertion in tbl1 is successful. And if there is some exception during insertion in tbl2, I want to rollback the insertion in tbl1.
I know this can easily be achieved in sql-server, but in case of ms-access, How should I manage this. Please Help, Thanks in advance.
It looks like we do: MSDN - TRANSACTION Statement (Microsoft Access SQL)
Transactions are not started automatically. To start a transaction, you must do so explicitly using:
Conclude a transaction by committing all work performed during the transaction:
COMMIT [TRANSACTION | WORK]
Conclude a transaction by rolling back all work performed during the transaction:
ROLLBACK [TRANSACTION | WORK]
Nobody has actually given you any code examples here in the answer or even cited an example (the Access help files do include examples, though). The key issue to keep in mind is that in Jet/ACE (Access does not support transactions itself -- it depends on whatever database engine you're using for that) that the transaction is controlled at the workspace level. You can create a new workspace for your transaction or create a new one. Here's some sample code:
On Error GoTo errHandler
Dim wrk As DAO.Workspace
Dim db As DAO.Database
Dim lngInvoiceID As Long
Set wrk = DBEngine.Workspaces(0)
Set db = wrk.OpenDatabase(CurrentDb.Name)
db.Execute "INSERT INTO tblInvoice (CustomerID) VALUES (123);", dbFailOnError
lngInvoiceID = db.OpenRecordset("SELECT @@IDENTITY")(0)
db.Execute "INSERT INTO tblInvoiceDetail (InvoiceID) VALUES (" & lngInvoiceID & ")", dbFailOnError
Debug.Print "Inserted Invoice header and detail for Invoice " & lngInvoiceID
If Not (db Is Nothing) Then
Set db = Nothing
Set wrk = Nothing
MsgBox Err.Number & ": " & Err.Description, vbExclamation, "Error in transaction"
(code tested and working within Access)
Yes Microsoft Access supports Transactions and they work quite well. I built a commercial POS application using Access as my database several years ago, and the transaction support worked very well.
Even so, if possible, I would use SQL Server Express. It's free and much more powerful than Access.
Example for OleDbTransaction on MSDN is here http://msdn.microsoft.com/en-us/library/93ehy0z8.aspx