If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Can Access make a transaction query?
Hi There,
I am wondering if Access can do a transaction query like SQL server. For example, I'd like to insert records to serveral tables, if one insertion is failed, I need to roll back the previous insertion, otherwise commit all the insertion. Any ideas? Thank you very much for your help. Christina |
#2
|
|||
|
|||
Can Access make a transaction query?
Yes, Access can do this. In VBA Help, search on any of the following
methods: begintrans, committrans or rollback -- Cheryl Fischer, MVP Microsoft Access "Christina" wrote in message ... Hi There, I am wondering if Access can do a transaction query like SQL server. For example, I'd like to insert records to serveral tables, if one insertion is failed, I need to roll back the previous insertion, otherwise commit all the insertion. Any ideas? Thank you very much for your help. Christina |
#3
|
|||
|
|||
Can Access make a transaction query?
"Christina" wrote
I am wondering if Access can do a transaction query like SQL server. For example, I'd like to insert records to serveral tables, if one insertion is failed, I need to roll back the previous insertion, otherwise commit all the insertion. Any ideas? Thank you very much for your help. Hi Christina, PMFBI In addition to what Cheryl has aptly stated, here is a code example once provided here by John Vinson that is as good an example as you will get in Help I believe: Example of TRANSACTION from John Vinson on MS newsgroup: ****************quote***************************** ** 'In this example, query appArchive will copy some records to another table, 'then query delArchived will delete those records that have been copied. Private Sub cmdArchive_Click() Dim ws As Workspace Dim db As DAO.Database Dim qdApp As DAO.Querydef Dim qdDel As DAO.Querydef Dim inTrans as Boolean On Error GoTo Proc_Error Set ws = dbEngine.Workspaces(0) ' the current workspace ' Start a Transaction: all queries run ' during a transaction are run together; you must Commit the ' transaction if they worked, and Rollback if they didn't, ' so either everything gets done or nothing is changed. ws.BeginTrans inTrans = True Set db = CurrentDb ' reference this database Set qdApp = db.Querydefs("appArchive") ' find the append query qdApp.Execute dbFailOnError ' and run it Set qdDel = db.Querydefs("delArchived") ' and the delete query qdDel.Execute dbFailOnError ' Unlikely, but check to be sure that the queries copied and deleted ' the same number of records If qdDel.RecordsAffected qdApp.RecordsAffected Then GoTo Proc_Error Set qdApp = Nothing Set qdDel = Nothing ' Commit the transaction to disk ws.CommitTrans Exit Sub Proc_Error: ' Roll back the two queries If InTrans Then ws.Rollback 'use msgbox to describe the problem End Sub *************unquote*********** In SQL Server you might do this within a query, in Access you will need to use VBA code. Apologies for butting in, Gary Walter |
Thread Tools | |
Display Modes | |
|
|