Today you'll learn about issues that relate to designing and coding applications that serve multiple users. Multiuser applications pose some unique challenges when it comes to database operations. These challenges are the main topics of this chapter:
By the time you complete this chapter, you'll be able to add transaction management to your Visual Basic applications and you'll know how to use cascading updates and deletes to maintain the referential integrity of your database. You will also know how to perform database-level, table-level, and page-level locking schemes in your database applications.
Whenever there is more than one person accessing a single database, some type of process must be used in order to prevent two users from attempting to update the same record at the same time. This process is known as a locking scheme. In its simplest form, a locking scheme allows only one user at a time to update information in the database.
The Microsoft JET database engine provides three levels of locking:
Database-level locking is the most restrictive locking scheme you can employ in your Visual Basic application. When you open the database using the Visual Basic data control, you can lock the database by setting the Exclusive property of the data control to True. When you open the database using Visual Basic code, you can lock the database by setting the second parameter of the OpenDatabase method to True. Here's an example:
Set db = DbEngine.OpenDatabase("c:mydb",True)
When the database is locked, no other users can open it. Other programs will not be able to read or write any information until you close the database. You should only use database-level locking when you must perform work that will affect multiple data objects (such as tables, indexes, relations, and queries). For example, the Visual Basic CompactDatabase operation affects all the data objects. It requires that the database be opened exclusively.
If you need to perform an operation in order to update the customer ID values in several tables and you also need to update several queries to match new search criteria, you would want to use database-level locking.
Let's start a Visual Basic project to illustrate how database-level locking works. Load Visual Basic and open a new project. Add a data control to the form. Set its DataBaseName property to C:\TYSDBVB\CHAP18\MULTIUSE.MDB and its Exclusive property to True. Save the form as MULTIUS1.FRM and the project as MULTIUS1.VBP. Now create an executable version of the project by selecting File | Make EXE from the Visual Basic main menu. Use MULTIUS1.EXE as the name of the executable file.
Now run the executable file. It will load and display the data control. Run a second instance of the executable. This is an attempt to run a copy of the same program. Because this second copy will attempt to open the same database for exclusive use, you'll see an error message when the second program starts. (See Figure 18.1.)
Attempting to open a locked database.
You'll notice that the second program continues after the error occurs, even though the database is not opened. You can check for the error when you first load the project by adding the following code to the Error event of the data control:
Private Sub Data1_Error(DataErr As Integer, Response As Integer)
If Err <> 0 Then
MsgBox Error$(Err)+Chr(13)+"Exiting Program", vbCritical, "Data1_Error"
Unload Me
End If
End Sub
Add this code to the Data1_Error event and then recompile the program. Just as you did in the previous example, attempt to run two instances of this program. This time, when you attempt to start the second instance, you will receive a similar message, after which the program will exit safely. (See Figure 18.2.)
Trapping the locked database error.
You can use table-level locking to secure a single table while you perform sensitive operations on the table. For example, if you want to increase the sale price of all items in your inventory by 5 percent, you open the table for exclusive use and then perform the update. After you close the table, other users will be able to open it and see the new price list. Using table-level locking for an operation like this can help prevent users from writing sales orders that contain some records with the old price and some records with the new price.
Let's modify the MULTIUS1.VBP project to illustrate table-level locking. Reopen the project and set the Exclusive property of the data control to False. This allows other users to open the database while your program is running. Now set the RecordSource property to Master Table and set the Options property to 3. Setting the Options property to 3 opens the recordset with the DenyWrite (1) and DenyRead (2) options turned on. This prevents other programs from opening Master Table while your program is running.
Save and recompile the program. Start a copy of the executable version of the program. It will run without error. Now attempt to start a second copy of the same program. You will see an error message telling you that the table is locked. (See Figure 18.3.)
Attempting to open a locked table.
You can perform the same table locking by using Visual Basic code with the following code piece:
Sub OpenTable()
On Error GoTo OpenTableErr
'
Dim db As Database
Dim rs As Recordset
'
Set db = DBEngine.OpenDatabase(App.Path + "\multiuse.mdb")
Set rs = db.OpenRecordset("Master Table", dbOpenTable,
dbDenyRead + dbDenyWrite)
'
GoTo OpenTableExit
'
OpenTableErr:
MsgBox Error$(Err) + Chr(13) + "Exiting Program", vbCritical, "OpenTable"
GoTo OpenTableExit
'
OpenTableExit:
'
End Sub
Notice the use of the dbDenyRead and dbDenyWrite constants in the OpenRecordset method. This is the same as setting the Option property of the data control to 3. Also notice that an error trap is added to the module to replace the code in the Error event of the data control.
The lowest level of locking available in Visual Basic is page-level locking. Page-level locking is handled automatically by the Microsoft JET engine and cannot be controlled through Visual Basic code or with data-bound control properties. Each time a user attempts to edit or update a record, the Microsoft JET performs the necessary page locking to ensure data integrity.
A data page can contain more than one data record. Currently, the Microsoft JET data page is always 2K in size. Locking a data page will lock all records that are stored on the same data page. If you have records that are 512 bytes in size, each time Microsoft JET performs a page lock, four data records will be locked. If you have records that are 50 bytes in size, each Microsoft JET page lock can affect 40 data records.
The exact number of records that are locked on a page cannot be controlled or accurately predicted. If your data table contains several deleted records that have not been compacted out using the CompactDatabase method, you will have "holes" in your data pages. These holes will not contain valid records. Also, data pages contain records that are physically adjacent to each other—regardless of any index, filter, or sort order that has been applied to create the data set. Even though records in a data set are listed one after another, they might not be physically stored in the same manner. Therefore, editing one of the data set records might not lock the next record in the data set list.
Even though page-level locking is performed automatically by Microsoft JET, you can use the LockEdits property of a record set to control how page-locking is handled by your application. There are two page-locking modes available: pessimistic locking (LockEdits=True) and optimistic locking (LockEdits=False). The default locking mode is pessimistic.
In pessimistic locking mode, Microsoft JET locks the data page whenever the Edit or AddNew method is invoked. The page stays locked until an Update or Cancel method is executed. When a page is locked, no other program or user can read or write any data records on the locked data page until the Update or Cancel methods have been invoked. The advantage of using the pessimistic locking mode is that it provides the highest level of data integrity possible at the page level. The disadvantage of using the pessimistic locking mode is that it can lock data pages for a long period of time. This can cause other users of the same database to encounter error messages as they attempt to read or write data in the same table.
In optimistic locking mode, Microsoft JET only locks the data page whenever the Update method is invoked. Users can invoke the Edit or AddNew methods and begin editing data without causing Microsoft JET to execute a page lock. When the user is done making changes and saves the record using the Update method, Microsoft JET will attempt to place a lock on the page. If it is successful, the record is written to the table. If Microsoft JET discovers that someone else has also edited the same record and has already saved it, the update is canceled and the user is informed with an error message saying that someone has already changed the data.
The advantage of using optimistic locking is that page locks are in place for the shortest time possible. This reduces the number of lock messages users receive as they access data in your database. The disadvantage of using optimistic locking is that it is possible for two users to edit the same record at the same time. This can lead to lock errors at update time rather than at read time.
Let's build a new Visual Basic project to illustrate page-level locking as well as the differences between pessimistic and optimistic locking. Load Visual Basic and start a new project.
Place a command button on the form. Set its Name property to cmdEdit and it's Caption property to &Edit. Add a frame control to the form and set its Caption property to Page Locking. Place two option button controls within the frame control. Set the Caption property of Option1 to Pessimistic and the Caption property of Option2 to Optimistic. Refer to Figure 18.4 as a layout guide.
Laying out the page locking project.
Now you need to add code to this demo. First, place the following variable declarations in the general declarations section of the form:
Option Explicit Dim db As Database Dim rs As Recordset Dim cName As String Dim nMax As Integer
Now add the following code to the Form_Load event. This code prompts you for a name for the form header. It then opens the database and data table, and it counts all the records in the table.
Private Sub Form_Load()
' get instance ID
cName = InputBox("Enter Job Name:")
Me.Caption = cName
'
' load db and open set
Set db = OpenDatabase(App.Path + "\multiuse.mdb")
Set rs = db.OpenRecordset("mastertable", dbOpenTable, dbSeeChanges)
'
' count total recs in set
rs.MoveLast
nMax = rs.RecordCount
'
End Sub
Now add the following two code pieces to the Click events of the option buttons. These routines toggle the LockEdits property of the recordset between pessimistic locking (LockEdits=True) and optimistic locking (LockEdits=False).
This code snippet turns on pessimistic locking:
Private Sub Option1_Click()
If Option1 = True Then
rs.LockEdits = True
Else
rs.LockEdits = False
End If
End Sub
This code piece turn on optimistic locking:
Private Sub Option2_Click()
If Option2 = True Then
rs.LockEdits = False
Else
rs.LockEdits = True
End If
End Sub
Finally, add the following code to the cmdEdit_Click event of the form. While in edit mode, this code prompts you for a record number. It then moves to that record, invokes the Edit method, makes a forced changed in a recordset field, and updates some titles and messages. When the form is in update mode, this routine attempts to update the recordset with the changed data and then resets some titles. Here's the code:
Private Sub cmdEdit_Click()
On Error GoTo cmdEditClickErr ' set trap
'
Dim nRec As Integer ' for rec select
Dim X As Integer ' for locator
'
' are we trying to edit?
If cmdEdit.Caption = "&Edit" Then
' get rec to edit
nRec = InputBox("Enter Record # to Edit [1 - " +
Trim(Str(nMax)) + "]:", cName)
' locate rec
If nRec > 0 Then
rs.MoveFirst
For X = 1 To nRec
rs.MoveNext
Next
rs.Edit ' start edit mode
' change rec
If Left(rs.Fields(0), 1) = "X" Then
rs.Fields(0) = Mid(rs.Fields(0), 2, 255)
Else
rs.Fields(0) = "X" + rs.Fields(0)
End If
' tell 'em you changed it
MsgBox "Modified field to: [" + rs.Fields(0) + "]"
' prepare for update mode
cmdEdit.Caption = "&Update"
Me.Caption = cName + " [Rec: " + Trim(Str(X - 1)) + "]"
End If
Else
rs.Update ' attempt update
cmdEdit.Caption = "&Edit" ' fix caption
Me.Caption = cName ' fix header
dbengine.idle dbfreelocks ' pause VB
End If
'
GoTo cmdEditClickExit
'
cmdEditClickErr:
' show error message
MsgBox Trim(Str(Err)) + ": " + Error$, vbCritical, cName + "[cmdEdit]"
'
cmdEditClickExit:
'
End Sub
Notice that there is a new line in this routine: the DBEngine.Idle method. This method forces Visual Basic to pause for a moment to update any dynaset or snapshot objects that are opened by the program. It is a good idea to place this line in your code so that it is executed during some part of the update process. This ensures that your program has the most recent updates to the data set.
Save the form as MULTIUS2.FRM and the project as MULTIUS2.VBP. Compile the project and save it as MULTIUS2.EXE. Now you're ready to test it. Load two instances of the compiled program. When it first starts up, you will be prompted for a job name. It does not matter what you enter for the job name, but make sure that you enter different names for each instance. The name you enter will be displayed on messages and form headers so that you can tell the two programs apart. Position the two instances apart from each other on the screen. (See Figure 18.5.)
Running two instances of the page locking project.
First you'll test the behavior of pessimistic page locking. Make sure the pessimistic radio button in the Page Locking frame is selected in both instances of the program. Now click the Edit button of the first instance of the program; when prompted, enter 1 as the record to edit. This program now has locked a page of data. Switch to the second instance of the program and click the Edit button. You'll see error 3260, which tells you that the data is unavailable. (See Figure 18.6.)
A failed attempt at editing during pessimistic locking.
Remember that pessimistic locking locks the data page as soon as a user begins an edit operation on a record. This lock prevents anyone else from accessing any records on the data page until the first instance releases the record by using Update or UpdateCancel. Now click the error message box and then click the Update button to release the record and unlock the data page.
Now you'll test the behavior of Microsoft JET during optimistic locking. Select the Optimistic radio button on both forms. In the first form, press Edit and enter 1 when prompted. The first instance is now editing record one. Move to the second instance and press Edit. This time you will not see an error message. When prompted, enter 1 as the record to edit. Again, you'll see no error message as Microsoft JET allows you to begin editing record one of the set. Now both programs are editing record one of the set.
Press the Update button of the second instance of the program to save the new data to the data set. The second instance has now read, edited, and updated the same record opened earlier by the first instance. Now move to the first instance and press the Update button to save the changes made by this instance. You'll see error 3197, which tells you that data has been changed and the update has been canceled. (See Figure 18.7.)
A failed attempt to update during optimistic locking.
Optimistic locking occurs at the moment the Update method is invoked. Under the optimistic scheme, a user can read and edit any record he or she chooses. When the user attempts to write the record back out to disk, the program checks to see whether the original record was updated by any other program since the user's version last read the record. If changes were saved by another program, error 3197 is reported.
The advantage of using pessimistic locking is that once you begin editing a record, you will be able to save your work because all other users are prevented from accessing that record. The disadvantage of using pessimistic locking is that if you have a lot of people in the database, it is possible that quite a bit of the file will be unavailable at any one time.
The advantage of using optimistic locking is that it occurs only during an update and then only when required. Optimistic locks are the shortest in duration. The disadvantage of using optimistic locking is that even though more than one user can edit a data set record at one time, only one person may save that data set record. This is usually the first person to complete the edit (not the person who opened the record first or the person who saves it last). This can be very frustrating for users who have filled out a lengthy data entry screen only to discover that they cannot update the data table! However, except in rare cases where there is an extreme amount of network traffic, you will probably find that optimistic locking is enough.
TIP: All ODBC data sources use optimistic locking only.
In the lesson on Day 10 ("Visual Basic and the Microsoft JET Engine"), you learned how to identify and define cascading updates and delete relations using the relation data access object. At the time, a particular aspect of relation objects was not fully covered: the capability to define cascading updates and deletes in order to enforce referential integrity. By employing cascading updates and deletes in your database definition, you can ensure that changes made to columns in one data table will be properly distributed to all related columns in all related tables within the database. This type of referential integrity is essential when designing and deploying database applications that will be accessed by multiple users.
Microsoft JET can only enforce update and delete cascades for native Microsoft JET format databases. Microsoft JET cannot enforce cascades that involve an attached table.
TIP: Cascading options should be added at database design time and can be accomplished using the VisData program (see Day 8, "Using VisData") or through Visual Basic code (see Day 10, "Visual Basic and the Microsoft JET").
Cascading occurs when users update or delete columns in one table that are referred to (via the relation object) by other columns in other tables. When this update or delete occurs, Microsoft JET automatically updates or deletes all the records that are part of the defined relation. For example, if you define a relationship between the column Valid.ListID and the column Master.ListID, any time a user updates the value of Valid.ListID, Microsoft JET will scan the Master table and update the values of all Master.ListID columns that match the updated values in the Valid.ListID column. In this way, as users change data in one table, all related tables are kept in sync through the use of cascading updates and deletes.
Let's use VisData to define a relation object that includes cascading updates and deletes. Start VisData and load the C:\TYSDBVB\CHAP18\MULTIUSE.MDB database. Then select Jet | Relations from the main menu to bring up the Relations dialog box. Press the Add Relation button and enter RelCustType as the relation object name. Select ValidTypes as the base table and CustType as the base field. Select MasterTable as the foreign table and CustType as the foreign field.
TIP: It might seem to you that the terms base table and foreign table are used incorrectly in the relation definition. It might help you to remember that all relation definitions are "based" on the values in the ValidTypes table. Also, it might help to remember that any data table related to the ValidTypes table is a foreign table.
Now check the Enforce Referential Integrity checkbox, select the One-To-Many radio button, and then check both the UpdateCascade and DeleteCascade checkboxes. Finally, in the Join Type section, select the radio button Only rows where joined fields from both tables are equal; then press the Add Relation button to save the object to the database. Your screen should look like the one shown in Figure 18.8.
Adding a cascading relation to the database.
Now you'll build a project that illustrates the process of cascading updates and deletes. Use the information in Table 18.1 and Figure 18.9 to build the MULTIUS3.VBP project.
Table 18.1. The control table for the MULTIUS3.VBP project.
| Controls | Properties | Settings |
|---|---|---|
| Form | Name Caption Left Height Top Width | Ch1803 Cascading Demo 1020 4275 1170 6480 |
| DBGrid | Name AllowAddNew AllowDelete Height Left Top Width | DBGrid1 True True 2715 120 120 3000 |
| DBGrid | Name AllowAddNew AllowDelete Height Left Top Width | DBGrid2 True True 2715 3240 120 3000 |
| Data Control | Name Caption DatabaseName Height Left RecordsetType RecordSource Top Width | Data1 Master Table C:\TYSDBVB\CHAP18\MULTIUSE.MDB 300 120 1 - Dynaset MasterTable 3000 3000 |
| Data Control | Name Caption DatabaseName Height Left RecordsetType RecordSource Top Width | Data2 Valid Types C:\TYSDBVB\CHAP18\MULTIUSE.MDB 300 3240 1 - Dynaset ValidTypes 3000 3000 |
| Command Button | Name Caption Height Left Top Width | Command1 Refresh 300 2580 3480 1200 |
Laying out the MULTIUS3.FRM form.
Only two lines of Visual Basic code are needed to complete the form. Add the following two lines to the Command1_Click event. These two lines update both data controls and their associated grids.
Private Sub Command1_Click()
Data1.Refresh
Data2.Refresh
End Sub
Save the form as MULTIUS3.FRM and the project as MULTIUS3.VBP and then run the project. Now you're ready test the cascading updates and deletes.
When you run the project, you'll see the two tables displayed in each grid, side-by-side. First, test the update cascade by editing one of the records in the Valid Types table. Select the first record and change the CustType column value from T01 to T09. When you have finished the edit and have moved the record pointer to another record in the ValidTypes grid, press the Refresh button to update both data sets. You'll see that all records in the MasterTable that had a value of T01 in their CustType field now have a value of T09. The update of ValidTypes was "cascaded" into the MasterTable by Microsoft JET.
Now add a new record with the CustType value of T99 to the ValidTypes table (set the Description field to any text you want). Add a record to the MasterTable that uses the T99 value in its CustType field. Your screen should look something like the one shown in Figure 18.10.
Adding new records to the MULTIUSE.MDB database.
Delete the T99 record from the ValidTypes table by highlighting the entire row and pressing the Delete key. After you have deleted the record, press the Refresh button again to update both data controls. What happens to the record in the MasterTable that contains the T99 value in the CustType field? It is deleted from the MasterTable! This shows the power of the cascading delete. When cascading deletes are enforced, any time a user deletes a record from the base table, all related records in the foreign table are also deleted.
The capability to enforce cascading updates and deletes as part of the database definition is a powerful tool. However, with this power comes some responsibility, too. Because database cascades cannot be easily undone, you should think through your database design carefully before you add cascading features to your database. It is not always wise to add both update and delete cascades to all your relationships. There are times when you might not want to cascade all update or delete operations.
Whenever you have defined a relation object, where the base table is a "validation" table and the foreign table is a "master" table, it is wise to define an update cascade. This will ensure that any changes made to the validation table will be cascaded to the related master table. It is not a good idea to define a delete cascade for this type of relation. Rarely will you want to delete all "master" records whenever you delete a related record from the validation table. If the user attempts to delete a record in the validation table that is used by one or more records in the master table, Microsoft JET will issue an error message telling the user that it is unable to delete the record.
Whenever you have defined a relation object where the base table is a master table and the foreign table is a child table (that is, CustomerMaster.CustID is the base table and CustomerComments.CustID is the foreign table), you might want to define both an update and a delete cascade. It is logical to make sure that any changes to the CustomerMaster.CustID field would be updated in the CustomerComments.CustID field. It might also make sense to delete all CustomerComments records whenever the related CustomerMaster record is deleted. However, this is not always the case. For example, if the child table is CustomerInvoice, you might not want to automatically delete all invoices on file. Instead, you might want Microsoft JET to prevent the deletion of the CustomerMaster record if a related CustomerInvoice record exists.
The key point to remember is that cascades are performed automatically by Microsoft JET, without any warning message. You cannot create an "optional" cascade or receive an automatic warning before a cascade begins. If you choose to use cascades in your database, be sure to think through the logic and the relations thoroughly, and be sure to test your relations and cascades before deploying the database in a production setting.
Another important tool for maintaining the integrity of your database is the use of transactions to manage database updates and deletes. Visual Basic allows you to enclose all database update operations as a single transaction. Transactions involve two steps: first, mark the start of a database transaction with the BeginTrans keyword; second, mark the end of the database transcation with the CommitTrans or RollBack keyword. You can start a set of database operations (add, edit, delete records) and then, if no error occurs, you can use the CommitTrans keyword to save the updated records to the database. If, however, you encounter an error along the way, you can use the RollBack keyword to tell Microsoft JET to reverse all database operations completed up to the point where the transaction first began.
For example, suppose you need to perform a series of database updates to several tables as part of a month-end update routine for an accounting system. This month-end processing includes totaling up transactions by customer from the TransTable, writing those totals to existing columns in a CustTotals table, appending the transactions to the HistoryTable, and deleting the transactions from the TransTable. The process requires access to three different tables and involves updating existing records (appending new records to a table and deleting existing records from a table). If your program encounters an error part of the way through this process, it would be difficult to reconstruct the data as it existed before the process began. In other words, it would be difficult unless you used Visual Basic transactions as part of the update routine.
All Microsoft JET transactions are applied to the current workspace object. (See Day 10 for a discussion of the workspace object.) If you have not named a workspace object, Visual Basic will use the default workspace for your program. However, because transactions apply to an entire workspace, it is recommended that you explicitly declare workspaces when you use transactions. This will give you the ability to isolate data sets into different workspaces and better control the creation of transactions.
Here's the exact syntax for starting a transaction:
Workspace(0).BeginTrans ' starts a transaction
...
If Err=0 Then
Workspaces(0).CommitTrans ' completes a transaction
Else
Workspaces(0).Rollback ' cancels a transaction
End If
In this code, the default workspace for the transaction area is used. In an actual program, you should name a workspace explicitly.
You'll now build a small project that illustrates one possible use for transactions in your Visual Basic applications. You'll create a database routine that performs the tasks listed in the previous example. You'll open a transaction table, total the records to a subsidiary table, copy the records to a history file, and then delete the records from the original table.
You'll write two main routines: one to declare the workspace and open the database, and one to perform the database transaction. First, add the following code to the general declarations section of a new form in a new project:
Option Explicit Dim db As Database ' database object Dim wsUpdate As workspace ' workspace object Dim nErrFlag As Integer ' error flag
These are the form-level variables you will need to perform the update.
Add the following code, which creates the workspace and opens the database. Create a new Sub called OpenDB and place the following code in the routine:
Sub OpenDB()
On Error GoTo OpenDBErr
'
nErrFlag = 0 ' assume all is OK
'
Set wsUpdate = DBEngine.CreateWorkspace("wsUpdate", "admin", "")
Set db = wsUpdate.OpenDatabase(App.Path + "\MULTIUS4.mdb", True)
'
GoTo OpenDBExit
'
OpenDBErr:
MsgBox Trim(Str(Err)) + " " + Error$(Err), vbCritical, "OpenDB"
nErrFlag = Err
'
OpenDBExit:
'
End Sub
This routine creates a new workspace object to encompass the transaction and then opens the database for exclusive use. You don't want anyone else in the system while you perform this major update. An error trap routine has been added here in case you can't open the database exclusively.
Now you can add the code that will perform the actual month-end update. Do this by using the SQL statements you learned in the lessons on Day 15 and Day 16. Create a new Sub called ProcMonthEnd and then add the following code:
Sub ProcMonthEnd()
On Error goto ProcMonthEndErr
'
Dim cSQL As String
Dim nResult As Integer
'
wsUpdate.BeginTrans ' mark start of transaction
'
' append totals to transtotals table
cSQL = "INSERT INTO TransTotals SELECT TransTable.CustID,
SUM(TransTable.Amount) as Amount FROM TransTable
GROUP BY TransTable.CustID"
db.Execute cSQL
'
' append history records
cSQL = "INSERT INTO TransHistory SELECT * FROM TransTable"
db.Execute cSQL
'
' delete the transaction records
cSQL = "DELETE FROM TransTable"
db.Execute cSQL
'
' ask user to commit transaction
'
nResult = MsgBox("Transaction Completed. Ready to Commit?",
vbInformation + vbYesNo, "ProcMonthEnd")
If nResult = vbYes Then
wsUpdate.CommitTrans
MsgBox "Transaction Committed"
Else
wsUpdate.Rollback
MsgBox "Transaction Canceled"
End If
'
nErrFlag = 0
GoTo ProcMonthEndExit
'
ProcMonthEndErr:
MsgBox Trim(Str(Err)) + " " + Error$(Err), vbCritical, "ProcMonthEnd"
nErrFlag = Err
'
ProcMonthEndExit:
'
End Sub
This code executes the three SQL statements that perform the updates and deletes needed for the month-end processing. The routine is started with a BeginTrans. When the updates are complete, the user is asked to confirm the transaction. In a production program, you probably wouldn't ask for transaction confirmation; however, this will help you see how the process is working.
Finally, you need to add the code that puts everything together. Add the following code to the Form_Load event:
Private Sub Form_Load()
OpenDB
If nErrFlag = 0 Then
ProcMonthEnd
End If
'
If nErrFlag <> 0 Then
MsgBox "Error Reported", vbCritical, "FormLoad"
End If
Unload Me
End Sub
This routine calls the OpenDB procedure. Then, if no error is reported, it calls the ProcMonthEnd procedure. If an error has occurred during the process, a message is displayed.
Save the form as MULTIUS4.FRM and the project as MULTIUS4.VBP, and then run the project. All you'll see is a message that tells you the transaction is complete and asks for your approval. (See Figure 18.11.)
Waiting for approval to commit the transaction.
If you choose No in this dialog box, Microsoft JET will reverse all the previously completed database operations between the Rollback and the BeginTrans statements. You can confirm this by clicking the No button, using VisData or Data Manager to load the MULTIUS4.MDB database, and then inspecting the contents of the tables.
NOTE: There is an SQL-Visual Basic script on the diskette that ships with this book called MULTIUS4.SQV. This script can be used with the SQL-VB program (see Day 15, "Advanced SQL I," and Day 16, "Advanced SQL II") to recreate a "clean" MULTIUS4.MDB file. After you have ran MULTIUS4.VBP once and answered Yes to commit the transaction, you might want to run the CH1804.SQV script to refresh the database.
The primary advantage of using transactions in your Visual Basic programs is that they can greatly increase the integrity of your data. You should use transactions whenever you are performing database operations that span more than one table or even operations that affect many records in a single table. A secondary advantage of using transactions is that often they will often increase the processing speed of Microsoft JET.
As useful as transactions are, there are still a few limitations. First, some database formats might not support transactions (for example, Paradox files do not support transactions). You can check for transaction support by checking the Transactions property of the database. If transactions are not supported, Microsoft JET will ignore the transaction statements in your code; you will not receive an error message. Some dynasets might not support transactions, depending on how they are constructed. Usually, sets that are the result of SQL JOIN and WHERE clauses or result sets that contain data from attached tables will not support transactions.
Transaction operations are kept on the local workstation in a temporary directory (the one pointed to by the TEMP environment variable). If you run out of available space on the TEMP drive, you will receive error 2004. You can trap for this error. The only solution is to make more disk space available or reduce the number of database operations between the BeginTrans and the CommitTrans statements.
Microsoft JET allows you to nest transactions up to five levels deep. However, if you are using external ODBC databases, you cannot nest transactions.
Today you have learned about the three important challenges that face every database programmer writing multiuser applications. They are
You have learned that there are three levels of locking available to Visual Basic programs. These levels are as follows:
You have learned how to use the VisData application to create relation objects that enforce referential integrity and automatically perform cascading updates or deletes to related records. You have learned that there are times when it is not advisable to establish cascading deletes (for example, do not use cascading deletes when the base table is a validation list and the foreign table is a master).
Finally, you have learned how to use database transactions to protect your database during extended, multitable operations. You have learned how to use the BeginTrans, CommitTrans, and Rollback methods of the workspace object. And you have learned some of the advantages and limitations of transaction processing.
Write the Visual Basic code that will take invoice transactions from the temporary holding table, named Transactions, and insert them into a table named History, which keeps the cumulative history information.
The History table contains four fields: HistoyItem (counter, primary key), CustID (a unique identifier for the customer), InvoiceNo (the number of the invoice issued to the customer), and Amount.
The Transactions table also has four fields: TransNo (counter, primary key), CustID (a unique identifier for the customer), InvoiceNo (the number of the invoice issued to the customer), and Amount.
Complete this project by starting a new project and dropping a single command button (named Post) onto a form. Pressing this button should trigger the posting process.
Include error trapping in your routines. Also, include messages to notify the user that the transaction posting is complete or that problems have been encountered.
