Rollback Multiple SQL Update Queries in Ms Access

Rollback Multiple SQL update queries in MS Access

Transactions may suit, they allow rollback: http://msdn.microsoft.com/en-us/library/bb243155.aspx

EDIT

Here is a rough example in DAO:

Dim strSQL As String
Dim db As DAO.Database
Dim wrk As Workspace

On Error GoTo TrapError

    Set db = CurrentDb
    Set wrk = DBEngine.Workspaces(0)

    wrk.BeginTrans
        strSQL = "Update sysInfo Set InvoiceOR=False"
        db.Execute strSQL, dbFailOnError
    wrk.CommitTrans

Exit_Sub:
    Set db = Nothing
    Set wrk = Nothing
    Exit Sub

TrapError:

    MsgBox "Failed: " & Err.Description
    wrk.Rollback
    Err.Clear
    Resume Exit_Sub

Here are some rough notes for ADO:

Dim cmd As ADODB.Command
Dim cn As ADODB.Connection

Set cmd = CreateObject("ADODB.Command")
Set cn = CurrentProject.Connection

cmd.CommandText = "Update sysInfo Set InvoiceOR=False"
cmd.ActiveConnection = cn
cmd.ActiveConnection.BeginTrans
cmd.Execute , , adExecuteNoRecords

If Err <> 0 Then
    cmd.ActiveConnection.RollbackTrans
Else
    cmd.ActiveConnection.CommitTrans
End If

Rollback Multiple SQL update queries in MS Access

Transactions may suit, they allow rollback: http://msdn.microsoft.com/en-us/library/bb243155.aspx

EDIT

Here is a rough example in DAO:

Dim strSQL As String
Dim db As DAO.Database
Dim wrk As Workspace

On Error GoTo TrapError

    Set db = CurrentDb
    Set wrk = DBEngine.Workspaces(0)

    wrk.BeginTrans
        strSQL = "Update sysInfo Set InvoiceOR=False"
        db.Execute strSQL, dbFailOnError
    wrk.CommitTrans

Exit_Sub:
    Set db = Nothing
    Set wrk = Nothing
    Exit Sub

TrapError:

    MsgBox "Failed: " & Err.Description
    wrk.Rollback
    Err.Clear
    Resume Exit_Sub

Here are some rough notes for ADO:

Dim cmd As ADODB.Command
Dim cn As ADODB.Connection

Set cmd = CreateObject("ADODB.Command")
Set cn = CurrentProject.Connection

cmd.CommandText = "Update sysInfo Set InvoiceOR=False"
cmd.ActiveConnection = cn
cmd.ActiveConnection.BeginTrans
cmd.Execute , , adExecuteNoRecords

If Err <> 0 Then
    cmd.ActiveConnection.RollbackTrans
Else
    cmd.ActiveConnection.CommitTrans
End If

SQL - Update multiple records in one query

Try either multi-table update syntax

UPDATE config t1 JOIN config t2
    ON t1.config_name = 'name1' AND t2.config_name = 'name2'
   SET t1.config_value = 'value',
       t2.config_value = 'value2';

Here is a SQLFiddle demo

or conditional update

UPDATE config
   SET config_value = CASE config_name 
                      WHEN 'name1' THEN 'value' 
                      WHEN 'name2' THEN 'value2' 
                      ELSE config_value
                      END
 WHERE config_name IN('name1', 'name2');

Here is a SQLFiddle demo

How to rollback multiple Queries on different database servers in case of any error

You can use the TransactionScope class. It works generally well but in case of distributed SQL servers like in your case requires the MS DTC enabled in both servers and configured properly (security has to be granted for execution of network transactions, distributed ones and so on...)

here a copy paste from an example on MSDN, you could "almost" use it like this... :)

// Create the TransactionScope to execute the commands, guaranteeing
// that both commands can commit or roll back as a single unit of work.
using (TransactionScope scope = new TransactionScope())
{
    using (SqlConnection connection1 = new SqlConnection(connectString1))
    {
        // Opening the connection automatically enlists it in the 
        // TransactionScope as a lightweight transaction.
        connection1.Open();

        // Create the SqlCommand object and execute the first command.
        SqlCommand command1 = new SqlCommand(commandText1, connection1);
        returnValue = command1.ExecuteNonQuery();
        writer.WriteLine("Rows to be affected by command1: {0}", returnValue);

        // If you get here, this means that command1 succeeded. By nesting
        // the using block for connection2 inside that of connection1, you
        // conserve server and network resources as connection2 is opened
        // only when there is a chance that the transaction can commit.   
        using (SqlConnection connection2 = new SqlConnection(connectString2))
        {
            // The transaction is escalated to a full distributed
            // transaction when connection2 is opened.
            connection2.Open();

            // Execute the second command in the second database.
            returnValue = 0;
            SqlCommand command2 = new SqlCommand(commandText2, connection2);
            returnValue = command2.ExecuteNonQuery();
            writer.WriteLine("Rows to be affected by command2: {0}", returnValue);
        }
    }

    // The Complete method commits the transaction. If an exception has been thrown,
    // Complete is not  called and the transaction is rolled back.
    scope.Complete();

}

source: TransactionScope Class

to minimize locks you could specify the IsolationLevel with the overload of the constructor which takes a TransactionScopeOptions, default is Serializable if you are fine with that you could set it to ReadCommitted.

Note: Personally I would not use this one unless absolutely needed, because it's a bit of a pain to have the DTC always configured and Distributed Transactions are in general slower than local ones but really depends on your BL / DAL logic.

How to revert update query in sql server?

To avoid this in the future, you may want to use a transaction to give you the option to roll back.

For example

BEGIN TRAN T1;
UPDATE ImportantStuff SET ImportantValue = 1 WHERE SomeValue = 5

You can then either commit if it looks good:

COMMIT TRAN T1;

Or rollback if it doesn't

ROLLBACK TRAN T1

Of course, this is not a substitute for testing before you run a script against real data - but I have often used this during testing, rather than hoping for the best.

Without a transaction, you will need to reset the data from a back up.

---

Related Topics