Do we have transactions in MS-Access?

Question

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.

Solution 1

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:

BEGIN TRANSACTION

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]

Solution 2

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)
    With wrk
      .BeginTrans
      db.Execute "INSERT INTO tblInvoice (CustomerID) VALUES (123);", dbFailOnError
      lngInvoiceID = db.OpenRecordset("SELECT @@IDENTITY")(0)
      db.Execute "INSERT INTO tblInvoiceDetail (InvoiceID) VALUES (" & lngInvoiceID & ")", dbFailOnError
      .CommitTrans
      Debug.Print "Inserted Invoice header and detail for Invoice " & lngInvoiceID
    End With

  exitRoutine:
    If Not (db Is Nothing) Then
       db.Close
       Set db = Nothing
    End If
    Set wrk = Nothing
    Exit Sub

  errHandler:
    MsgBox Err.Number & ": " & Err.Description, vbExclamation, "Error in transaction"
    wrk.Rollback
    Resume exitRoutine

(code tested and working within Access)

Comments

[Vaibhav Jain]

I would have used sqlserver, but my client requires to run this application using a pendrive. Direct plug and play. He don't want to install any software on his PC.

[David]

SQL Server Compact edition is embeddable like Access, while still behaving like SQL Server, albet with some features unavailable.

[Admin]

okk, that would be great for my application. You mean I don't have to install any component of .net on client pc. (except .net framework)

[David-W-Fenton]

-1 for the SQL Server Express suggestion. Why would you assume that "much more powerful than Access" is relevant here (or anywhere)? Certainly there are scenarios where SQL Server is going to be more appropriate than Jet/ACE, but that is completely orthogonal to the issue of transaction support.

[Randy Minder]

@David-W-Fenton - I just checked your website. Now I see why you didn't like my comment. You're an Access consultant! Well, I stand by my comment. On almost every level, SQL Server Express, or Compact Edition, is going to be a better choice than Access.

[David-W-Fenton]

As an Access consultant, I have clients where the data is stored in SQL Server or SQL Server Express. The key is that in some case it's appropriate and in others it's not. You seem to have a SQL Server-everywhere attitude, and it's not helpful or useful unless you specify exactly what the considerations are that should cause one to switch. You sound like an Access bigot, which may not be correct, but your original answer and your comment here don't make it sound like you have rational reasons for recommending switching from a Jet/ACE back end.

[Jamie Deakin]

Okay so Access 2007 supports it, but what about Access 2003?

[Luis Siquot]

begin transaction, commit and rollback are suported by jet engine, so if you use a .mdb file through jet (called from ADO in a program) it can be access97 or above. @leeand00