Do we have transactions in MS-Access?

by

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)

More Related Contents:

  1. Is it necessary to manually close and dispose of SqlDataReader?
  2. Check if a SQL table exists
  3. How can I add user-supplied input to an SQL statement?
  4. ExecuteReader requires an open and available Connection. The connection’s current state is Connecting
  5. What is the syntax for an inner join in LINQ to SQL?
  6. MetadataException: Unable to load the specified metadata resource
  7. SQLite Database Locked exception
  8. SqlConnection SqlCommand SqlDataReader IDisposable
  9. DropdownList DataSource
  10. SqlBulkCopy from a List
  11. Exception when AddWithValue parameter is NULL
  12. Insert into C# with SQLCommand
  13. Does using parameterized SqlCommand make my program immune to SQL injection?
  14. Executing query with parameters
  15. How to fill Dataset with multiple tables?
  16. SqlConnection.Close() inside using statement
  17. DataTable equivalent in Java [duplicate]
  18. Join and Include in Entity Framework
  19. How do I re-write a SQL query as a parameterized query?
  20. Get affected rows on ExecuteNonQuery
  21. How do I translate a List into a SqlParameter for a Sql In statement? [duplicate]
  22. Add WHERE clauses to SQL dynamically / programmatically
  23. Issues Doing a String Comparison in LINQ
  24. How to create Microsoft Access database in C# programmatically?
  25. Passing datatable to a stored procedure
  26. Use SQL Server time datatype in C#.NET application?
  27. What is the difference between dataview and datatable?
  28. .net connection pooling
  29. Generating sql code programmatically
  30. How to give ADO.NET Parameters

Leave a Comment