Join Curt Frye for an in-depth discussion in this video Preserving data integrity using transactions, part of Up and Running with VBA in Access.
It's easy to make mistakes when you edit table data, so most databases have a transaction system that lets you verify that you want to make your change. Transactions work by accepting your changes as temporary input and letting you ask the user if they're sure they want to make the change. If so, Access completes the transaction. If not, it returns the table to its previous state, and it discards the changes. The table we will work with is the Clients table, so I'll just double-click that over in the Navigation pane to open it.
The table is in the state that it was in after I completed the exercise in the previous movie. But what's important is that the ClientSalesPerson field contains a value of 2. So if there's at least one record there that contains a 2 in that field, then fine; if not, then go ahead and change any of the values in the ClientSalesPerson field to 2 so that the code will run properly. In this case, it's the ClientID number 2, Buena Suerta, that has a ClientSalesPerson value of 2, so that's the one that will be changed.
I'll press Ctrl+W to close the table, and we're going to work with the UseTransactions code module. And it's all the way at the bottom of the Modules section of the Navigation pane. I'll double-click it, and it appears inside the Visual Basic Editor. Now, there's a lot of code here, so instead of having you type anything in, I'm going to save some time and just go through what the code does. I'll start out with the declaration for the subroutine named AddingTransactions, and then I define two variables: the first is myR, which is a recordset, and then the second is myWS, which is a workspace.
Then I assign the Clients table to the myR recordset and the existing workspace-- in other words, the user environment inside of Access--to the variable myWS. We use workspaces so that we have access to transactions. And in fact, the very next statement is myWS BeginTrans. In other words, we're about to affect a table, but Access is going to make sure that it gets a commit statement before it actually implements, or applies, any of those changes to the table data.
Now the code is going to go through each row in the recordset until it reaches the end of file. That is what the Do Until myR.EOF and then loop code does. Within that loop, it's going to look at the ClientSalesPerson field for each row, and if that field contains the value of 2, then it's going to open that row for editing, and it's going to change the ClientSalesPerson value to 1, and then it will update. But again, that update isn't final because we're in Transaction mode and we haven't seen a commit transaction statement yet.
Then we close the If and move to the next row, as long as we haven't reached the end of file. When we do reach the end of file, we drop out of the loop, and Access displays a message box. That message box contains the text "Changed salesperson 2 to 1," and asks, "Save the changes?" Now, this message box has a question format and also Yes and No buttons. If the user clicks the Yes button, so that is why = vbYes is the condition for the If statement, then it commits the transaction.
So it goes within the workspace object and implements the CommiTrans method. If not, which is signaled by the Else statement, it does a rollback. In other words, it discards the changes and leaves the table in its previous state. And then the rest of the code is used basically to undo what we did before. We close the If, then we close the recordset, close the workspace, and set those two variables to Nothing. So now we can run the code. I'll press F5, and then we flip back to the Access database window, and we see the message box indicating that the change occurred and asking if we want to save the changes.
I'll click Yes, and then we go back to the Visual Basic Editor, because that's where we were when the code ran. Now, I'll press Alt+F11 to go back to the main database window and double-click the Clients table in the Navigation pane to open it, and we see that the second record has indeed changed; the ClientSalesPerson value has changed from 2 to 1. Transactions are one way to maintain data integrity, but you should always back up important business data every day to make sure that if a mistake does occur, you can rebuild your tables with recent data.
- Working with macros and managing macro security settings
- Creating, exporting, and deleting code modules
- Running a VBA procedure
- Debugging VBA code
- Defining variables, constants, and calculations
- Displaying a calculated result in a message box
- Repeating tasks with loops
- Creating conditional statements
- Manipulating database objects, such as forms, tables, and queries
- Adding, editing, and deleting records
- Looking up values in a table
- Requerying and repainting forms