Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
79 views
in Technique[技术] by (71.8m points)

c# - Do we have transactions in MS-Access?

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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)


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...