Day 17

Multiuser Considerations

Today you'll look at some issues related 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 understand using cascading updates and deletes to maintain the referential integrity of your database. You also will know how to perform database-level, table-level, and page-level locking schemes in your database applications.

Database Locking Schemes

Whenever more than one person is accessing a single database, some type of process must be used to prevent two users from attempting to update the same record at the same time. This process is 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 Locking

Database-level locking is the most restrictive locking scheme you can use 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. After you open the database by 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 cannot read or write any information until you close the database. You should use database-level locking only when you must perform work that affects multiple data objects (such as tables, indexes, relations, and queries). The Visual Basic CompactDatabase operation, for example, affects all the data objects, so the database must be opened exclusively.

If you need to perform an operation to update the customer ID values in several tables and you also need to update several queries to match new search criteria, you should use database-level locking.

Take a look at a Visual Basic project to see 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:\TYSDBVB5\SOURCE\DATA\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 choosing File | Make MULTIUS1.EXE from the Visual Basic main menu. Use MULTIUS1.EXE as the name of the executable file.

Now run the executable file. It loads and displays the data control. Run a second instance of the executable file. This is an attempt to run a copy of the same program. Because this second copy attempts to open the same database for exclusive use, you see an error message when the second program starts (see Figure 17.1).

Figure 17.1. Attempting to open a locked database.


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. Again, attempt to run two instances of this program. This time, when you attempt to start the second instance, you receive a similar message, after which the program exits safely. (See Figure 17.2.)

Figure 17.2. Trapping the locked database error.


Table Locking

You can use table-level locking to secure a single table while you perform sensitive operations on the table. If you want to increase the sale price of all items in your inventory by five percent, for example, you open the table for exclusive use and then perform the update. After you close the table, other users can 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.

Now 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 setting allows other users to open the database while your program is running. Now set the RecordSource property to MasterTable 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 MasterTable while your program is running.

Save and recompile the program. Start a copy of the executable version of the program. It runs without error. Now attempt to start a second copy of the same program. You see an error message telling you that the table could not be locked because it is in use elsewhere--that is, by the first instance of the program (see Figure 17.3).

Figure 17.3. Attempting to open a locked table.


You can perform the same table-locking operation by using this Visual Basic code:

Sub OpenTable()
    On Error GoTo OpenTableErr
    `
    Dim db As Database
    Dim rs As Recordset
    `
    Set db = DBEngine.OpenDatabase("C:\TYSDBVB5\SOURCE\DATA\MULTIUSE.MDB")
    Set rs = db.OpenRecordset("MasterTable", 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.

Page Locking

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. What Is Page Locking? A data page can contain more than one data record. Currently, the Microsoft Jet data page is always 2KB. Locking a data page locks 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 are 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 by using the CompactDatabase method, you have "holes" in your data pages. These holes do 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 dataset. Even though records in a dataset are listed one after another, they might not be physically stored in the same manner. Therefore, editing one of the dataset records might not lock the next record in the dataset list. Pessimistic and Optimistic Locking 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. Two page-locking modes are 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 method has 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 method 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 attempts 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 also has edited the same record and already has saved it, the update is canceled and the user is informed with an error message saying that someone already has 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. An Example of Page-Level Locking In this section, you build a new Visual Basic project to demonstrate 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 its Caption property to &Edit. Add a frame control to the form and set its Caption property to Page Locking. Place two option button controls in the frame control. Set the Caption property of Option1 to Pessimistic and the Caption property of Option2 to Optimistic. Use Figure 17.4 as a layout guide.

Figure 17.4. 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("C:\TYSDBVB5\SOURCE\DATA\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 snippet turns 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 change 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 dataset.

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 starts up, you are 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 is 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 17.5.)

First, you'll test the behavior of pessimistic page locking. Make sure that 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 17.6.)

Figure 17.5. Running two instances of the page-locking project.

Figure 17.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 test the behavior of Microsoft Jet during optimistic locking. Select the Optimistic radio button on both forms. In the first form, click Edit and enter 1 when prompted. The first instance now is editing record 1. Move to the second instance and click Edit. This time, you do not see an error message. When prompted, enter 1 as the record to edit. Again, you see no error message as Microsoft Jet allows you to begin editing record 1 of the set. Now both programs are editing record 1 of the set.

Click the Update button of the second instance of the program to save the new data to the dataset. The second instance now has read, edited, and updated the same record opened earlier by the first instance. Now move to the first instance and click 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 that the update has been canceled. (See Figure 17.7.)

Figure 17.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. When to Use Pessimistic or Optimistic Page Locking The advantage of using pessimistic locking is that once you begin editing a record, you can save your work because all other users are prevented from accessing that record. The disadvantage of using pessimistic locking is that if you have many people in the database, it is possible that quite a bit of the file is 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 dataset record at one time, only one person can save that dataset record. This usually is 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! Except in rare cases where there is an extreme amount of network traffic, you probably will find that optimistic locking is enough.

NOTE: All ODBC data sources use optimistic locking only.

Using Cascading Updates and Deletes

In the lesson on Day 9, "Visual Basic and the Microsoft Jet Engine," you learned how to identify and define cascading updates and delete relationships by 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 using cascading updates and deletes in your database definition, you can ensure that changes made to columns in one data table are distributed properly to all related columns in all related tables in the database. This type of referential integrity is essential when designing and using database applications accessed by multiple users.

Microsoft Jet can enforce update and delete cascades only 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 by using the Visdata program (see Day 7, "Using the Visdata Program") or by using Visual Basic code (see Day 9).

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. If you define a relationship between the column Valid.ListID and the column Master.ListID, for example, any time a user updates the value of Valid.ListID, Microsoft Jet scans the MasterTable and updates 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.

Building the Cascading Demo Project

The MULTIUSE.MDB database used in the earlier exercise is also used for this exercise. This database has a one-to-many relationship with enforced referential integrity for both cascading updates and cascading deletes. ValidTypes is the base table, and CustType is the base field. MasterTable is the foreign table, and CustType is the foreign field. You might find it helpful to open this database in the Visual Data Manager (Visdata) and explore the structure of these two tables.

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 you build a project that illustrates the process of cascading updates and deletes. Use the information in Table 17.1 and Figure 17.8 to build the MULTIUS3.VBP project.

Table 17.1. The control table for the MULTIUS3.VBP project.
Control Property Setting
Form Name Ch1703
Caption Cascading Demo
Left 1020
Height 4275
Top 1170
Width 6480
DBGrid Name DBGrid1
AllowAddNew True
AllowDelete True
Height 2715
Left 120
Top 120
Width 3000
DBGrid Name DBGrid2
AllowAddNew True
AllowDelete True
Height 2715
Left 3240
Top 120
Width 3000
Data Control Name Data1
Caption Master Table
DatabaseName C:\TYSDBVB5\SOURCE\ DATA\MULTIUSE.MDB
Height 300
Left 120
RecordsetType 1-Dynaset
RecordSource MasterTable
Top 3000
Width 3000
Data Control Name Data2
Caption Valid Types
DatabaseName C:\TYSDBVB5\SOURCE\ DATA\MULTIUSE.MDB
Height 300
Left 3240
RecordsetType 1-Dynaset
RecordSource ValidTypes
Top 3000
Width 3000
Command Button Name Command1
Caption Refresh
Height 300
Left 2580
Top 3480
Width 1200

Figure 17.8. Laying out the MULTIUS3.FRM form.


Only two lines of Visual Basic code are needed to complete the form. Add the following 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 to test the cascading updates and deletes.

Running the Cascading Demo Project

When you run the project, you 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. After you finish the edit and move the record pointer to another record in the ValidTypes grid, click the Refresh button to update both datasets. You 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 Fig-ure 17.9.

Figure 17.9. Adding new records to the MULTIUSE.MDB database.

Delete the T99 record from the ValidTypes table by highlighting the entire row and pressing Delete. After you delete the record, click 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 also are deleted.

When to Use the Cascading Updates and Deletes

The capability to enforce cascading updates and deletes as part of the database definition is a powerful tool. With this power comes some responsibility, too, however. Because database cascades cannot easily be 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. At times, you might not want to cascade all update or delete operations.

Whenever you define a relation object in which the base table is a validation table and the foreign table is a master table, it is wise to define an update cascade. This ensures that any changes made to the validation table are cascaded to the related master table. It is not a good idea to define a delete cascade for this type of relation. Rarely do 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 from the validation table that is used by one or more records in the master table, Microsoft Jet issues an error message telling the user that it is unable to delete the record.

Whenever you define a relation object in which the base table is a master table and the foreign table is a child table (for example, 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 also might make sense to delete all CustomerComments records whenever the related CustomerMaster record is deleted. This is not always the case, though. If the child table is CustomerInvoice, for example, 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 using the database in a production setting.

Transaction Management

Another important tool for maintaining the integrity of your database is the use of transactions to manage database updates and deletes. Visual Basic enables 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 transaction with the CommitTrans or RollBack keyword. You can start a set of database operations (add, edit, and delete records) and then, if no error occurs, you can use the CommitTrans keyword to save the updated records to the database. If you encounter an error along the way, though, 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.

Suppose that 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 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 will be difficult to reconstruct the data as it existed before the process began. In other words, it will be difficult unless you used Visual Basic transactions as part of the update routine.

Microsoft Jet Transactions and the Workspace Object

All Microsoft Jet transactions are applied to the current workspace object. (See Day 10, "Creating Database Programs with Visual Basic Code," for a discussion of the Workspace object.) If you do not name a Workspace object, Visual Basic uses the default workspace for your program. Because transactions apply to an entire workspace, it is recommended that you explicitly declare workspaces when you use transactions. This gives you the capability to isolate datasets 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.

Building the Microsoft Jet Transaction Project

You now build a small project that illustrates one possible use for transactions in your Visual Basic applications. You create a database routine that performs the tasks listed in the previous example. You 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.

TIP: To avoid errors when running this project, make sure that you selected the appropriate DAO reference before executing the program. Do this by choosing Project | References from the Visual Basic 5 menu. Then enable the checkbox next to Microsoft DAO 3.5 object library.

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 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("C:\TYSDBVB5\SOURCE\DATA\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 performs the actual month-end update. Do this by using the SQL statements you learned in the lessons on Days 13, "Creating Databases with SQL," and 15, "Updating Databases with SQL." 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 helps 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 17.10.)

Figure 17.10. Waiting for approval to commit the transaction.


If you choose No in this message box, Microsoft Jet reverses all the previously completed database operations between the Rollback and the BeginTrans statements. You can confirm this by clicking No, using Visdata or Data Manager to load the MULTIUS4.MDB database, and then inspecting the contents of the tables.

NOTE: An SQL-Visual Basic script called MULTIUS4.SQV is included on the CD-ROM that accompanies this book. You can use this script with the SQL-VB program (see Days 13 and 15) to create a "clean" MULTIUS4.MDB file. After you run MULTIUS4.VBP once and answer Yes to commit the transaction, you might want to run the MULTIUS4.SQV script to refresh the database.

Advantages and Limitations of Transactions

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 they 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 ignores the transaction statements in your code; you do 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 do 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'll receive error 2004. You can trap for this error. The only solution is to make more disk space available or to reduce the number of database operations between the BeginTrans and the CommitTrans statements.

Microsoft Jet enables you to nest transactions up to five levels deep. If you are using external ODBC databases, however, you cannot nest transactions.

Summary

Today, you learned about the three important challenges that face every database programmer writing multiuser applications:

You learned that three levels of locking are available to Visual Basic programs:

You learned how to use Visual Basic to enforce referential integrity and automatically perform cascading updates or deletes to related records. You 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 learned how to use database transactions to protect your database during extended, multitable operations. You learned how to use the BeginTrans, CommitTrans, and Rollback methods of the workspace object. Finally, you learned some of the advantages and limitations of transaction processing.

Quiz

1. What are the three levels of locking provided by the Microsoft Jet database engine?

2. Which form of locking would you use when compacting a database?

3. Which form of locking would you use if you needed to update price codes in the price table of a database?

4. Which property of a Recordset do you set to control whether your application's data has optimistic or pessimistic page locking?

5. What is the difference between pessimistic and optimistic page locking?

6. Can you use pessimistic locking on an ODBC data source?

7. What happens to data when cascading deletes are used in a relationship?

8. Why would you use transaction management in your applications?

9. What are the limitations of transactions?

10. Do you need to declare a workspace when using transactions?

Exercises

1. Write Visual Basic code that exclusively opens a database (C:\DATA\ABC.MDB) during a Form Load event. Include error trapping.

2. Build on the code you wrote in the previous exercise to exclusively open the table Customers in ABC.MDB.

3. Suppose that you are building a new accounts receivable system for your company. You have saved all tables and data into a single database named C:\DATA\ABC.MDB. You have discovered that all invoices created must be posted to a history file on a daily basis. Because this history file is extremely valuable (it is used for collections, reporting, and so on), you don't want your posting process to destroy any of the data that it currently contains. Therefore, you decide to use transactions in your code.
Write the Visual Basic code that takes invoice transactions from the temporary holding table, Transactions, and inserts them into a table named History, which keeps the cumulative history information.
The History table contains four fields: HistoryItem (counter and 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 and 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. Clicking 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.