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.

Bestir

Some notes on Access transactions: #1988196 – Cudbear

Access doesn't have transactions, because Access is not a database engine. Jet/ACE (Access's default db engine) has supported commit/rollback as long as I've used it (since Jet 2.x, e.g., 1996). It has never supported transaction logging and probably never will (I recognize that's not what you're asking about but many of those coming to Jet/ACE from server database backgrounds are rather foggy on the meaning of the term "transactions" and have a hard time grasping that Jet/ACE supports one and not the other). – Threecornered

Answer 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]

Seton

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

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. @Kraken – Ascariasis

Answer 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)

Threecornered

Answer 3

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.

Hoover

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. – Bestir

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

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) – Simultaneous

-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. – Threecornered

@Threecornered - 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. – Hoover

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. – Threecornered