  
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:
- Database locking schemes: You'll examine the locking system used by the Microsoft
Jet database engine, and you'll look at the differences between optimistic and pessimistic
locking schemes. You'll learn a scheme for performing multitable locking of data
tables in highly relational databases.
- Cascading updates and deletes: You'll learn how to use these features of the
Microsoft Jet database engine to enforce database relations using the Cascading Updates
and Deletes options.
- Transaction management: You'll see the process of transaction management, as
well as how to add transaction management to your Visual Basic applications by using
the BeginTrans, CommitTrans, and Rollback methods. Transaction
management using the SQL pass-through method with back-end databases also is covered.
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: At this level, only one user at a time can access the database.
Use this locking level when you need to perform work on multiple, related database
objects (such as tables, queries, indexes, and relations) at the same time.
- Table locking: At this level, only one user at a time can access the locked table.
Use this locking level when you need to perform work on multiple records in the same
table.
- Page locking: At this level, only one user can access the page of records in
the database table. This is the lowest locking level provided by Microsoft Jet. Page
locking is handled automatically by Visual Basic whenever you attempt to edit or
update a record in a dataset.
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:
- Using database locking schemes
- Using cascading updates and deletes to maintain database integrity
- Using database transactions to provide commit/rollback options for major updates
to your database
You learned that three levels of locking are available to Visual Basic programs:
- Database level: You can use the Exclusive property of the data control or the
second parameter of the OpenDatabase method to lock the entire database.
Use this option when you need to perform work that affects multiple database objects
(such as tables, queries, indexes, relations, and so on).
- Table level: You can set the Options property of the data control to 3 or the
third parameter of the OpenRecordset method to dbDenyRead+dbDenyWrite
in order to lock the entire table for your use only. Use this option when you need
to perform work that affects multiple records in a single table (for example, increasing
the sales price on all items in the inventory table).
- Page level: Microsoft Jet automatically performs page-level locking whenever
you use the data control to edit and save a record, or whenever you use Visual Basic
code to perform the Edit/AddNew and Update/CancelUpdate methods.
You can use the LockEdits property of the Recordset to set the page locking to pessimistic
(to perform locking at edit time) or optimistic (to perform locking only at update
time).
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.
  
|