"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.

 

[Montez659]

I haven't been able to cancel anything by hitting Esc - I can see the new record in the table. And also, Me.Cancel keeps giving me an error of Method or data member not found.

What am I missing?

EDIT: Ok, i think I am getting it a bit. I need to play around with it some more.

 

[Norie]

Montez

I can't help feeling that there's something somewhere that just isn't set up right.

It might even be something you did a while ago.

 

[dk]

Just to clarify a few points. Any updates made through a transaction will not be visible outside of that transaction - that's the whole point of these things. A transaction just writes the data to memory and only writes it to the disk when the commit is called. If the updates were made for each individual record then you could feasibly end up with a user who selects data that is only half completed and the whole integrity of the database breaks. If you are seeing any "lag" it will be only due to the time taken to actually write the data to disk.

Anyway, it's clear that what you're doing does not require a transaction - a transaction is for multiple updates/insert/deletes that must all occur successfully whereas you're describing the update/insertion of a single record and this is bog standard functionality provided by Access. If you add a command button to your form (assuming that you have the Controls Wizard selected) it will give you some options for what you want the button to do and one of these options under Record Operations is Undo Record - the equivalent of using escape. If you want to have a little more control you could remove the navigation buttons (set the form's Navigation Buttons to No) and then add buttons for record navigation (first, last, next, previous) and record operations (add new, delete, undo, save). All of these can be added by the wizard and they're fairly robust.

HTH
DK

 

[xenou]

Another note: I've perhaps muddied the issue by introducing questions about forms. The main idea was that this can be useful for capturing an autonumber key without much code being needed. You can do this with or without code, with or without forms, or in combination with both forms and code. There's no hard separation between the two since you can add code to your forms, even though you can also have forms with no VBA customizations at all. I believe I probably write too much Access code, myself -- it seems that if you understand how Access works you can get a lot of real slick stuff done just by using ordinary, inbuilt functionality that comes with the program.

 

[Montez659]

I believe I probably write too much Access code, myself

As do I! It always seems that I am able to get exactly what I want from code as opposed to Access. Of course, I have used VBA longer than Access anyways.

I appreciated all the help and I think there are a few different routes that I could go, so I will definitely check those out.

As far as the lag is concerned though, the weird part was that I was still getting the wrong record id even after the transaction was committed. I even put a one second pause in there just to make sure, and I was still getting the wrong id. That leads me to believe that there was something else going on and not necessarily lag that was causing me to get the bad record id.

Anyways, that is all water under the bridge because there are other better ways to do it. Again, thanks for all your help!

 

[xenou]

It's probably a poor example (in truth, it seems to me that good Access developers don't script all their database transactions with VBA). Nevertheless, if you are interested in a sample of inserting a value and then using the ID for a second insert here is one way (among what must be a dozen other possibilities).

What I've done here is simply:

1. create a POHeader table (with a vendor and a date)
2. create a PODetail table (with items that belong to the PO)

We update both tables with some dummy data (hardcoded into the routine). As soon as the PO Header is created, we get the PONumber just assigned, and use it as a foreign key when we update the PODetail table. If any error occurs after the transaction is begun, we roll it back. In reality, you don't really even need to put it in a transaction - just make the PONumber required and if you have the PO Number all is well, and if you don't, it the PODetail record can't be inserted (if the POHeader exists we can still use it, and if not we can try again).

Sample Database with the code module and two tables:
<a href="http://northernocean.net/etc/mrexcel/20110713_db112.zip">Sample Database</a>
sha256sum (zip file): cb206eb80ebf72cebddcdb66c37008525d2fcb3222f47319096749c52c0f6234

As a matter of interest in transactions, you can step through the code and, as soon as you've updated the POHeader table, open it in datasheet view and take a peek - you won't see the transaction. Then continue stepping through the code and check it after the commit transaction statement - now it's visible. For a further exercise, it would be well to do the same thing with a form and no code (I'll see if I can come up with anything that works though I'm short on time this week).

Code (assuming the tables described):

[COLOR="SeaGreen"]'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/COLOR]
[COLOR="SeaGreen"]'SAMPLE CODE - goes in a standard module[/COLOR]
[COLOR="SeaGreen"]'Assumes a database with two tables:[/COLOR]

[COLOR="SeaGreen"]'POHeader (PONumber {long}, SupplierID {long}, PODate {date})[/COLOR]
[COLOR="SeaGreen"]'PODetail (POLineNumber {long}, ItemNumber {long}, Qty {double}, PONumber {long|foreign key})[/COLOR]



[COLOR="Navy"]Sub[/COLOR] DoInsert()
[COLOR="Navy"]Dim[/COLOR] rs [COLOR="Navy"]As[/COLOR] DAO.Recordset
[COLOR="SeaGreen"]'--------------------------------------[/COLOR]
[COLOR="SeaGreen"]'Dummy data variables[/COLOR]
[COLOR="Navy"]Dim[/COLOR] mySupplierID [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] myPODate [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Date[/COLOR]
[COLOR="Navy"]Dim[/COLOR] myPONumber [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] myItemNumber [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] myQuantity [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Double[/COLOR]
[COLOR="SeaGreen"]'Flag variable for commit[/COLOR]
[COLOR="Navy"]Dim[/COLOR] intTransactionCommitted [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
 
    
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] ErrHandler:
    
    [COLOR="SeaGreen"]'//Sample data[/COLOR]
    mySupplierID = 1
    myPODate = [COLOR="Navy"]Date[/COLOR]
    myItemNumber = 1
    myQuantity = 10
    
    [COLOR="SeaGreen"]'-------------------------------------------------------[/COLOR]
    [COLOR="SeaGreen"]'BEGIN TRANSACTION[/COLOR]
    intTransactionCommitted = -1
    DBEngine.Workspaces(0).BeginTrans
    
    [COLOR="SeaGreen"]'//Create a PO[/COLOR]
    [COLOR="Navy"]Set[/COLOR] rs = CurrentDb.OpenRecordset("POHeader", dbOpenTable)
    [COLOR="Navy"]With[/COLOR] rs
        .AddNew
        ![SupplierID] = mySupplierID
        ![PODate] = myPODate
        
        [COLOR="SeaGreen"]'//Save PO[/COLOR]
        .Update
                
        [COLOR="SeaGreen"]'//Get PO Number just created[/COLOR]
        .Bookmark = .LastModified
        myPONumber = ![PONumber]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    rs.Close
    
    [COLOR="SeaGreen"]'//Create a PO Line[/COLOR]
    [COLOR="Navy"]Set[/COLOR] rs = CurrentDb.OpenRecordset("PODetail", dbOpenTable)
    [COLOR="Navy"]With[/COLOR] rs
        .AddNew
        ![ItemNumber] = myItemNumber
        ![Qty] = myQuantity
        ![PONumber] = myPONumber
        
        [COLOR="SeaGreen"]'//Save PO Line[/COLOR]
        .Update
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    rs.Close
    
    [COLOR="SeaGreen"]'//No errors - so commit transactions[/COLOR]
    DBEngine.Workspaces(0).CommitTrans
    intTransactionCommitted = 0
    
    [COLOR="Navy"]Set[/COLOR] rs = [COLOR="Navy"]Nothing[/COLOR]
    
    [COLOR="SeaGreen"]'END TRANSACTION[/COLOR]
    [COLOR="SeaGreen"]'-------------------------------------------------------[/COLOR]

My_Exit:
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] rs [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] rs.EditMode <> dbEditNone [COLOR="Navy"]Then[/COLOR]
       rs.CancelUpdate
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    rs.Close
    [COLOR="Navy"]Set[/COLOR] rs = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]

ErrHandler:
[COLOR="Navy"]If[/COLOR] intTransactionCommitted <> 0 [COLOR="Navy"]Then[/COLOR]
    DBEngine.Workspaces(0).Rollback
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
MsgBox "An error occurred: " & Err.Number & " " & Err.Description _
    & vbNewLine & "Transaction was cancelled."
[COLOR="Navy"]Resume[/COLOR] My_Exit

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]