"Transactional" record updates

[Montez659]

Can anyone point me in the direction of a "transactional" record update?

What I mean is that I have 4 different SQL statements in vba executing on a command button, and if any one of those fail, I want the whole thing to fail. Otherwise, junk records will be created.

I would google it but I really don't even know the correct terminology to search for.

Thanks in advance.

 

[dk]

Hi

You've got the correct terminology - a database transaction is one where the whole lot either succeeds or fails. You didn't say how you're running the queries within your VBA code (e.g. RunSQL, DAO, ADO). I personally use ADO as that has served me well over many years. Here's an example that uses ADO to attempt to execute two SQL statements. To use this code you'll need to set up an example table called "SOME_TABLE" that has a single numeric field (doesn't matter what the field's called). The code will fail on the second SQL statement as it's trying to insert a text value into a numeric field - because of this the whole transaction gets "rolled back" to the beginning as though the code never ran. If you then change the second statement so that it inserts a numeric value then it should run successfully and the table will have two records inserted.

Not only are transactions useful for where you want something to either fail completely or succeed completely but transactions can also improve performance when performing a large number of inserts or updates.

Anyway, here's the ADO example. If you're using DAO then it supports transactions too but the syntax is slightly different.

HTH
DK

Sub TransactionExample()

    Dim oConn As Object
    Dim sSQL As String

    On Error GoTo ErrHandler    'General error handler

    Set oConn = CreateObject("ADODB.Connection")
    oConn.Provider = "Microsoft.Jet.OLEDB.4.0"
    oConn.ConnectionString = "C:\temp\somefile.mdb"
    oConn.Open


    oConn.BeginTrans
    On Error GoTo TransError    'set to transaction error handler


    'For the purpose of this example the table SOME_TABLE has one field defined as long integer

    sSQL = "INSERT INTO SOME_TABLE VALUES (123)"
    oConn.Execute sSQL

    'This will fail as it's trying to insert a text value into a numeric field
    sSQL = "INSERT INTO SOME_TABLE VALUES ('blah')"
    oConn.Execute sSQL

    oConn.CommitTrans

    On Error GoTo ErrHandler    'reset back to general error handler

    oConn.Close
    Set oConn = Nothing


    MsgBox "Transaction successfully completed.", vbInformation, "Success"



ExitSub:
    Exit Sub



ErrHandler:
    MsgBox "An error occurred: " & Err.Description, vbExclamation, "Error"
    Resume ExitSub


TransError:
    oConn.RollbackTrans
    oConn.Close
    Set oConn = Nothing
    MsgBox "The transaction failed on the following SQL statement: " & vbCr & sSQL & vbCr & "Error Description: " & Err.Description, vbExclamation, "Transaction Error"
    Resume ExitSub


End Sub

 

[xenou]

along the lines of terminology (besides transactional processing as such) you can throw in "atomicity" or "atomic" ...

 

[Montez659]

I have actually been using the CurrentDB.Execute method. And I have Access 2007. The code looks pretty straight forward.

I will take a gander on the internets for these terms you mention.

 

[Montez659]

Quick question on the commit trans and rollback. Do the records actually appear in the db or are they kind of like "shadow" records that don't really exist?

If I were to do a Dlookup to get the record id by the latest date/time stamp, would it pull the record that has not been comitted yet or the prior record?

 

[xenou]

I'm not sure but I think it depends on the record locking you use. Do you really need to worry about this? How long do you intend to leave uncommitted changes "out there" in user sessions?

 

[Montez659]

No, it's not that.

I have an unbound form that needs to update three tables and one of them is a Junction table, by which I need the id of the record that is being inserted into the main table before I can update the junction table. I have always heard bad things about identity so I decided to do a makeshift Dlookup to find the last inserted by a time stamp that is automatically added.

lastDate = DMax("transDTStamp", "tblTransactions")
transID = DLookup("[transID]", "tblTransactions", "[transDTStamp]= #" & lastDate & "#")

I wondered if this could be run before the transaction is committed and still return the id of the record that I am trying to add.

In all honesty, this db will be split eventually but only used by one person - me, so there might be a better way of doing this. It is just how I decided to go about it.

 

[Montez659]

Well, I guess by testing I answered my own question. I was not able to retrieve the record id using the code above when the record was not committed.

I suppose that only the first two SQL's are vital and I can commit the transaction before firing off the last SQL.

Unless...I suppose that I could create two connections and two transactions and if either fail, roll back both. That is just theory though, not sure it is even possible.

 

[xenou]

by which I need the id of the record that is being inserted into the main table before I can update the junction table.

Is this an autonumber key (one you don't know until its created)?

 

[Montez659]

Is this an autonumber key (one you don't know until its created)?

Yes, it is.