In Chapters 29, "Using the Visual Basic Data Control," and 30, "Doing More with Bound Controls," you saw how you could write a database application very quickly by using the Data control and bound controls that come with Visual Basic. These chapters showed you that, by setting a few properties, you can create a nearly complete data-entry screen. I say nearly complete because, in Chapter 30, you also saw that you needed to write some program code to handle some additional functions of a database application--for example, adding or deleting records or finding a specific record.
These additional functions introduced you to some of the programming that you can do in a database application. However, you can write an entire database application with just program commands and not use the Data control at all. When you use just the program commands, you work with Visual Basic's data access objects (DAO). In this chapter, we'll learn how Visual Basic's data access objects can be used to create complete, robust data-management applications. Data access objects act as a Visual Basic program's internal representation of physical data--data stored in some type of database or data-management engine. Think of the data access objects as special types of variables. These "variables," however, represent data stored outside the program rather than information stored in the computer's memory while the program is running.
Using the data access objects and their associated program commands is more complex than using the Data control and bound controls, but does offer greater programming flexibility for some applications. The data access objects and programming commands also provide the basis for many of the actions of the Data control and the bound controls. Therefore, they help you understand the concepts behind the controls. As you saw in Chapter 26, even if you use the Data control, you may also need to write some programming code to augment its capabilities.
See "Essential Functions the Data Control Forgot," Chapter 29
To demonstrate the similarities and differences between data access objects and the Data control, this chapter instructs you on how to build a data entry screen similar to the ones you created in the previous chapters. This way, you can compare how the programming commands work to how the Data control implements the commands. Figure 31.1 shows the data entry screen that you will build in this chapter.
FIG. 31.1
You can create this data entry screen by following this chapter's instructions.
A key reason for using program commands is the flexibility they give you beyond what is available with the Data control. You can perform more detailed input validation than is possible with just data engine rules because program commands do not directly access the database. You also can cancel changes to your edited data without using transactions. The use of program commands also provides an efficient way to handle data input and searches that do not require user interaction. Examples of this are receiving data from lab equipment or across a modem, or looking up the price of an item in a table. Program commands enable you to do transaction processing as well.
The first step in writing many data access programs is to set up a link to the database with which you want to work. If your application will be working with a database that already exists, you'll need to create a Database object within the program, then use that object to create a link to the existing database. In effect, you are "opening the database" for use in your program. Most of the other data access objects will flow from that Database object.
See "Implementing Your Design," Chapter 28
As we discussed in Chapter 24, a database is opened as part of a session with the database engine. The database engine is represented in a program by the DBEngine object; we define sessions by creating Workspace objects within the DBEngine object. You can then open a database with the Workspace object's OpenDatabase method. To use the OpenDatabase method, create a database object and call the method, as shown in this bit of code:
Dim OldDb As Database, OldWs As Workspace Set OldWs = DBEngine.Workspaces(0) Set OldDb = OldWs.OpenDatabase("C:\YOUTH\YOUTHTRK.MDB")
For a detailed discussion of the DBEngine and Workspace objects, see "Creating the Database," Chapter 28
TROUBLESHOOTING: When I try to run the preceding commands, I get the error User-defined type not defined. What's happening? In order to use any database capabilities (beyond the Data control and bound controls) in your program, you must have one of the Data Access Object libraries specified in your program references. To set the program references, select the References command from the Project menu and select one of the DAO libraries from the "References - (projectname)" dialog box.
NOTE: There are two basic Jet DAO libraries that Visual Basic can include in an application. These external libraries are the "Microsoft DAO 3.5 Object Library" and the "Microsoft DAO 2.5/3.5 Compatibility Library." If you will be programming for 32-bit clients only, and using 32-bit Jet (Access 95/97) databases, you should select the 3.5 library. If you have a need to exchange data with 16-bit systems or Access 2.0 applications, you will have to use the 2.5/3.5 compatibility library.
These commands open a Jet database with the default options of read/write data access and shared access. The full syntax of the OpenDatabase method lets you specify that the database should be opened exclusively (no other users or programs can access it at the same time), that it be opened in read-only mode (no updates are allowed), or, if you are connecting to a non-Access database, you can specify the database type. The use of exclusive access and read-only access are usually only required for multiuser applications (as discussed in Chapter 31, "Multi-User Databases").
See "Denying Table Access to Others," Chapter 34
However, you might want to use the read-only mode even in a single-user application for a lookup database (for example, a ZIP Code database or a state abbreviations database that you include with your application but do not want the user to be able to modify). To open the database as read-only, change the Set statement to the form shown in the following code. The first parameter after the database name indicates whether the database is opened for exclusive access; the second parameter indicates whether read-only mode is to be used:
Set OldDb = OldWs.OpenDatabase("C:\ZIPCODE.MDB",False,True)
After you open the database, you have only created a link from your program to the database file itself. You still do not have access to the information in the database. To gain access to the information, you must create and open a Recordset object that links to data stored in one or more of the tables in the database.
When you create a Recordset object to open a recordset in your program, you can access any entire table, specific fields and records from the table, or a combination of records and fields from multiple tables. There are three types of recordsets available in Visual Basic:
Recordset Type | Definition |
Table | Directly represents all records in an entire physical table in a database. |
Dynaset | Sets of pointers that provide access to fields and records in one or more tables of a database. |
Snapshot | Read-only copies of data from one or more tables. They are stored in memory. |
NOTE: This chapter refers to tables, dynasets, and snapshots, but it is important to remember that they are all recordsets and can only be accessed using the Recordset object. Specifically, all mentions of tables, dynasets, and snapshots actually refer to table-type recordsets, dynaset-type recordsets, and snapshot-type recordsets, respectively. Previous versions of Visual Basic supported objects that are now outdated: Table objects, Dynaset objects, and Snapshot objects.
The following sections describe each type of recordset, point out some of the advantages and disadvantages of each, and demonstrate the commands used to access the recordset.
A table (table-type recordset) is a direct link to one of the physical tables stored in the database. Because all data in a database is stored in tables, using this type of recordset provides the most direct link to the data. Tables are also the only form of recordset that supports indexes; therefore, searching a table for a specific record can be quicker than searching a dynaset or snapshot.
When using tables, data is addressed or modified one table at a time, one record at a time. This arrangement provides very fine control over the manipulation of data. However, it does not give you the convenience of changing records in multiple tables with a single command, such as an action query.
Advantages of Using Tables Using tables in your programs gives you several advantages:
Disadvantages of Using Tables Of course, using tables in your programs also has disadvantages:
You can usually overcome these disadvantages with programming, but the solutions are often less than elegant. This chapter discusses some of the workarounds in its coverage of the various methods for moving through a recordset and for finding specific records. These topics are covered later in this chapter.
See "Positioning the Record Pointer," Chapter 31
Opening a Table for Use To open a table for the program to use, define a Recordset object and then use the OpenRecordset method to access the table. To identify the type of recordset to create, specify the dbOpenTable constant in the parameters of the method, as shown in the following segment of code. This code assumes that you have already opened the database by using the OldDb object and that the database contains a table called "Youth":
Dim OldTbl As Recordset Set OldTbl = OldDb.OpenRecordset("Youth",dbOpenTable)
These commands open a table in a Jet database, with the default parameters of shared use and read/write mode. You can include optional parameters in the OpenRecordset method to open the table for exclusive use or to open the table in read-only mode. These options are summarized in Table 31.1.
Option | Action |
dbDenyWrite | Prevents others in a multi-user environment from writing to the table while you have it open. |
dbDenyRead | Prevents others in a multi-user environment from reading the table while you have it open. |
dbReadOnly | Prevents you from making changes to the table. |
A dynaset is a grouping of information from one or more tables in a database. This information is comprised of selected fields from the tables, often presented in a specific order and filtered by a specific condition. Dynasets address the records present in the base tables at the time the dynaset was created. Dynasets are an updatable recordset, so any changes made by the user are stored in the database. However, dynasets do not automatically reflect additions or deletions of records made by other users or programs after the dynaset was created. This makes dynasets less useful for some types of multi-user applications.
A dynaset is actually a set of record pointers that point to the specified data as it existed when the dynaset was created. Changes made to information in the dynaset are reflected in the base tables from which the information was derived as well as in the dynaset itself. These changes include additions, edits, and deletions of records.
Advantages of Using Dynasets Some of the advantages provided by dynasets are as follows:
Disadvantages of Using Dynasets Dynasets do have some limitations:
Setting Up a Dynaset To set up a dynaset for use within a program, you must define the Recordset object with the Dim statement and then generate the dynaset by using the OpenRecordset method with the dbOpenDynaset parameter. For creating a dynaset, the key part of the OpenRecordset method is the SQL statement that defines the records to be included, the filter condition, the sort condition, and any join conditions for linking data from multiple tables. The code shown in Listing 31.1 shows the simplest form of creating a dynaset, in which all records and fields are selected from a single table with no sort or filter conditions specified. This is the type of dynaset created by default when using a Data control (though you can use a table or snapshot with the Data control). The statements in Listing 31.1 provide you access to the same information as you had by accessing the table directly with the previous code. The only difference is the type of recordset that was created.
Dim OldDb As Database, NewDyn As Recordset,OldWs As Workspace Set OldWs = DBEngine.Workspaces(0) Set OldDb = OldWs.OpenDatabase("C:\YOUTH\YOUTHTRK.MDB") Set NewDyn = OldDb.OpenRecordset("SELECT * FROM Youth", _ dbOpenDynaset)
NOTE: If you want to include all records from one table in a dynaset in no particular order, you can omit the SQL statement and simply use the table name (Set OldDb = OldWs.OpenDatabase("Youth"). However, it's a good idea to go ahead and use a SQL statement in case you want to modify it later.
When you create a dynaset, you can use any valid SQL statement that selects records. You can also specify options that affect the dynaset's behavior. Table 31.2 lists these options.
Option | Action |
dbDenyWrite | Prevents others in a multi-user environment from writing to the dynaset while you have it open. |
dbReadOnly | Prevents you from making changes to the dynaset. |
dbAppendOnly | Enables you to add new records, but prevents you from reading or modifying existing records. |
dbSQLPassThrough | Passes the SQL statement used to create the dynaset to an ODBC database server to be processed. |
The following code shows how to create a dynaset-type recordset that only allows the user to read the information in the database:
Set NewDyn = OldDb.OpenRecordset("Youth", dbOpenDynaset, dbReadOnly)
NOTE: An ODBC server is a database engine, such as Microsoft SQL Server or Oracle, that conforms to the Open Database Connectivity (ODBC) standards. The purpose of a server is to handle query processing at the server level and return to the client machine only the results of the query. ODBC drivers, which are usually written by the vendor of the database engine, handle the connection between Visual Basic and the database server. An advantage of using ODBC is that you can connect to the information on the database servers without having to know the inner workings of the engine.
You can also create a dynaset from another dynaset28., as illustrated in Listing 31.2. The reason for creating a second dynaset from an initial dynaset is that you can use the filter and sort properties of the first dynaset to specify the scope of records and the presentation order of the second dynaset. Creating a second dynaset enables you to create a subset of your initial data. The second dynaset is usually much smaller than the first, which allows faster processing of the desired records. In Listing 31.2, a dynaset was created from the Customer table to result in a national mailing list. A second dynaset was then created, which includes only the customers living in Alabama and sorts them by city name for further processing. Figures 31.2 and 31.3 show the records returned by these two dynasets.
Dim OldDb As Database, NewDyn As Recordset, ScnDyn As Dynaset Dim OldWs As Workspace Set OldWs = DBEngine.Workspaces(0) Set OldDb = OldWs.OpenDatabase("C:\YOUTH\YOUTHTRK.MDB") Set NewDyn = OldDb.OpenRecordset("SELECT * FROM Youth", _ dbOpenDynaset) NewDyn.Filter = "State = `AL'" NewDyn.Sort = "City" Set ScnDyn = NewDyn.OpenRecordset(dbOpenDynaset)
FIG. 31.2
The results of the creation of a dynaset from base tables.
FIG. 31.3
The results of creating one dynaset from another dynaset after filter
and sort conditions have been set.
You might wonder why, if you need the results in the second dynaset, you can't just create it from the base tables in the first place. The answer is that you can do so if your application needs only the second table. However, consider a member tracking system in which you want access to all your members (the creation of the first dynaset), and one of the functions of the system is to generate a mailing list for a particular region (the creation of the second dynaset). Because the pointers to all the required information are already present in the first dynaset, the creation of the second dynaset is faster than if it were created from scratch.
A snapshot, as the name implies, is a "picture," or copy, of the data in a recordset at a particular point in time. A snapshot is very similar to a dynaset in that it is created from base tables, using a SQL statement, or from a QueryDef, dynaset, or another snapshot. A snapshot differs from a dynaset in that it is not updatable. As a general rule, use a snapshot whenever you want a set of data that isn't time-sensitive; that is, it doesn't matter if records in the underlying database are modified after the snapshot is created. The most frequent use of snapshots in a program is to generate reports or informational screens in which the data is static.
Advantages of Using Snapshots Snapshots provide you with the following advantages:
Disadvantages of Using Snapshots The primary disadvantage of using a snapshot is that it is not an updatable recordset. In addition, you can't use an index with a snapshot to help set the order of the data or locate specific records.
CAUTION: To avoid memory constraints, make sure that a snapshot returns only a small set of records.
Setting Up a Snapshot You can create a snapshot by defining a Recordset object with the Dim statement and then using the OpenRecordset method with the dbOpenSnapshot parameter to assign the records to the object (as shown in Listing 31.3). As with a dynaset, you can specify optional parameters in the OpenRecordset method. Table 31.3 summarizes these parameters.
Dim OldDb As Database, NewSnap As Recordset, OldWs As Workspace Set OldWs = DBEngine.Workspaces(0) Set OldDb = OldWs.OpenDatabase("C:\YOUTH\YOUTHTRK.MDB") Set NewSnap = OldDb.OpenRecordset("Youth",dbOpenSnapshot)
Option | Action |
dbDenyWrite | Prevents others in a multi-user environment from writing to the snapshot while you have it open. |
dbForwardOnly | Enables only forward scrolling through the snapshot. |
dbSQLPassThrough | Passes the SQL statement used to create the snapshot to an ODBC database to be processed. |
A forward-only recordset is a special type of snapshot that allows only forward scrolling through its records. This means that the MoveFirst, MovePrevious, and Find methods will not work on the recordset. The advantage of using this type of recordset is that it is faster than a snapshot. However, the forward-only recordset should be used only in situations where a single pass through the recordset is needed, such as in report generation routines.
To set up a forward-only recordset, you use the OpenRecordset method and specify the dbOpenForwardOnly constant as shown in the following line of code:
Set NewRSet= OldDb.OpenRecordset("Youth",dbOpenForwardOnly)
Suppose that you have written a data entry screen using the Data control and bound controls. To display information on-screen, you simply draw bound controls and then set the appropriate data fields for the controls. The display of the information is automatic. Using the data access objects, the process is only slightly more involved. You still use control objects (text boxes, labels, check boxes, and so on) to display the information, but you have to assign the data fields to the correct control properties with each record displayed. When used in this manner, the control objects are typically referred to as unbound controls. One advantage of using unbound controls is that you can use any control to display data, not just the bound controls specifically designated for use with the Data control.
Information in fields can be accessed through a recordset's Fields collection in one of several ways. For example, any of these techniques would suffice for retrieving the contents of a field named ThisField in a recordset named MyRS and placing it into a text box named Text1:
NOTE: If a field's name contains spaces, you can enclose the entire name in square braces, as in Text1.Text = MyRS![longer field name].
For an example, we'll detail how to build a member data entry screen based on the Youth table of the sample database we've discussed. You can apply these concepts to any existing database. To begin building this screen, start a new project in Visual Basic. Then, on the default form, add the data labels and text boxes to hold the data from the table. Figure 31.4 shows the form with these controls added.
FIG. 31.4
Use unbound controls to display data from the data access objects.
To set up the table for use, you must open the table by using the OpenRecordset method. For this case, place the Dim statements that define the data access objects (like the ones in Listing 31.3) in the General Declarations section of the form so that the objects are available throughout all the code in the form. You then open the database and table in the Form_Load event (see Listing 31.4). At this point, the table is open and you are positioned at the first record in the table.
Set OldWs = DBEngine.Workspaces(0) `******************************** `Open database and Customer table `******************************** Set OldDb = OldWs.OpenDatabase("C:\YOUTH\YOUTHTRK.MDB") Set RcSet = OldDb.OpenRecordset("Youth",dbOpenTable) `******************************************** `Move to first record and display information `******************************************** RcSet.MoveFirst Call ShowFields
To display the data, assign the value of the desired data fields to the display properties of the controls (captions for labels, text for text boxes, and so on) that contain the data. Listing 31.5 shows this process. Notice that the listing defines the text boxes as a control array; you can use a loop to quickly modify certain properties of the controls such as foreground color or visibility. Also notice that the assignments are placed in a subroutine; you can call the same routine from a number of command button events rather than repeat the code in each event. This arrangement makes the code more efficient and easier to maintain.
Private Sub ShowFields() Text1(0).Text = RcSet("Lastname") Text1(1).Text = RcSet("Firstname") Text1(2).Text = RcSet("Address") Text1(3).Text = RcSet("City") Text1(4).Text = RcSet("State") Text1(5).Text = RcSet("Zip") Text1(6).Text = RcSet("Phone") End Sub
NOTE: Because the Text property is the default property of a text box, you do not have to include the property name in the assignment statement. My personal preference is to include the name for readability.
Because a database with only one record is fairly useless, a database engine must provide ways to move from one record to another within recordsets. Visual Basic provides six such techniques:
Technique | Description |
Move methods | Changes the position of the record pointer from the current record to another record. |
Find methods | Locates the next record that meets the find condition. Find methods work on dynasets and snapshots. |
Seek method | Finds the first record in a table that meets the requested condition. |
Bookmark property | Identifies the location of a specific record. |
AbsolutePosition | Moves the record pointer to a specific record position in the recordset. |
PercentPosition | Moves the record pointer to the record property nearest the indicated percentage position in the recordset. |
Each of these has benefits and limitations, as described in the following sections.
You can use the Move methods on any recordsets available in Visual Basic. There are five different Move methods:
Move Method | Action |
MoveFirst | Moves the record pointer from the current record to the first record in the opened recordset. |
MoveNext | Moves the record pointer from the current record to the next record (the record following the current record) in the opened recordset. If there is no next record (that is, if you are already at the last record), the end-of-file (EOF) flag is set. |
MovePrevious | Moves the record pointer from the current record to the preceding record in the opened recordset. If there is no previous record (that is, if you are at the first record), the beginning-of-file (BOF) flag is set. |
MoveLast | Moves the record pointer from the current record to the last record in the opened recordset. |
Move n | Moves the record pointer from the current record n records down (if n is positive) or up (if n is negative) in the opened recordset. If the move would place the record pointer beyond the end of the recordset (either BOF or EOF), an error occurs. |
These commands move the record pointer to the record indicated based on the current order of the recordset. The current order of the recordset is the physical order, unless an index was set for a table, or a dynaset or snapshot was created with a specific order specified. To show the use of the MoveFirst, MovePrevious, MoveNext, and MoveLast methods, add command buttons to the data entry screen so that the user can move through the recordset (see Figure 31.5). To activate these buttons, add the code shown in Listing 31.6. The code for each button is preceded by an identifying comment line.
FIG. 31.5
Add command buttons to enable the user to navigate through the recordset.
`************************************************ `The MoveFirst method activates the "Top" button. `************************************************ RcSet.MoveFirst Call ShowFields `******************************************************** `The MovePrevious method activates the "Previous" button. `******************************************************** RcSet.MovePrevious Call ShowFields `************************************************ `The MoveNext method activates the "Next" button. `************************************************ RcSet.MoveNext Call ShowFields `************************************************** `The MoveLast method activates the "Bottom" button. `************************************************** RcSet.MoveLast Call ShowFields
The Move n method lets you move more than one record from the current position. The value of n is the number of records to move in the recordset. This value can be either positive or negative to indicate movement either forward or backward in the recordset. The following piece of code shows the use of this method to move two records forward from the current record:
RcSet.Move 2
The Move n method also has an optional parameter that enables you to move a specified number of records from a bookmark. You must set the bookmark prior to using this form of the Move method. The following line of code shows how this method is used:
RcSet.Move 2, bkmrk
You can use the Find methods on dynasets and snapshots only. You can't use Find methods on table objects. (Because the data entry screen was created with a table, you can't use the Find methods in the example.) The Find methods are used to locate records that meet specified criteria. You express the criteria in the same way that you specify the Where clause of a SQL command--except without the Where keyword. There are four Find methods:
Find Method | Action |
FindFirst | Starting at the top of the database, finds the first record in the recordset with the specified criteria. |
FindNext | Starting at the current location in the recordset, finds the next record down with the specified criteria. |
FindPrevious | Starting at the current location in the recordset, finds the next record up with the specified criteria. |
FindLast | Starting at the bottom of the recordset, finds the last record in the database with the specified criteria. |
After the Find method is run, check the status of the NoMatch property of the recordset. If NoMatch is True, the method failed to find a record that matched the requested criteria. If NoMatch is False, the record pointer is positioned at the found record.
Listing 31.7 shows the use of the Find methods to move through a dynaset.
`*************************************** `Set up the database and Dynaset objects `*************************************** Dim OldDb As Database, NewDyn As Recordset, FindCrit As String Dim OldWs As Workspace Set OldWs = DBEngine.Workspaces(0) Set OldDb = OldWs.OpenDatabase("C:\YOUTH\YOUTHTRK.MDB") Set NewDyn = OldDb.OpenRecordset("SELECT * FROM Youth", _ dbOpenDynaset) `******************************************** `Set the search criteria for the find methods `******************************************** FindCrit = "State = `AL'" `******************************************* `Find the first record matching the criteria `******************************************* NewDyn.FindFirst FindCrit Do While Not NewDyn.NoMatch `****************************************************** `Loop forward through all records matching the criteria `****************************************************** ewDyn.FindNext FindCrit Loop `****************************************** `Find the last record matching the criteria `****************************************** NewDyn.FindLast FindCrit Do While Not NewDyn.NoMatch `******************************************************* `Loop backward through all records matching the criteria `******************************************************* ewDyn.FindPrevious FindCrit Loop
TIP: You might want to set a bookmark prior to invoking one of the Find methods. Then, if a matching record is not found, you can return to the record that was current before the Find was attempted.
The Find methods work by scanning each record, starting with the current record, to locate the appropriate record that matches the specified criteria. Depending on the size of the recordset and the criteria specified, this search operation can be somewhat lengthy. The Jet engine can optimize searches if an index is available for the search expression. If you are going to do many searches, consider creating an index for the field or fields in the base table.
TIP: In many cases, it is faster to re-create a dynaset by using the search criteria than it is to use the Find methods to process all matching records. You can also create a second filtered dynaset from the first dynaset by using the search criteria as the Filter condition. The best technique depends upon the amount of data, size of each record, as well as other factors. Try different approaches with your data to see what's best for a given situation. Listing 31.8 shows the comparison of these two approaches.
`********************** `Create Initial Dynaset `********************** Dim OldDb As Database, NewDyn As Recordset, ScnDyn As Recordset Dim OldWs As WorkSpace Set OldWs = DBEngine.Workspaces(0) Set OldDb = OpenDatabase("C:\YOUTH\YOUTHTRK.MDB") Set NewDyn = OldDb.OpenRecordset("SELECT * FROM Youth", _ dbOpenDynaset) `********************************* `Use Find method to search records `********************************* NewDyn.FindFirst "State = `FL'" Do Until NewDyn.NoMatch ewDyn.FindNext "State = `FL'" Loop `************************************************************* `Create second dynaset and use Move methods to process records `************************************************************* NewDyn.Filter = "State = `FL'" Set ScnDyn = NewDyn.OpenRecordset() ScnDyn.MoveFirst Do Until ScnDyn.EOF ScnDyn.MoveNext Loop `******************************************************* `Create initial dynaset with "Where" clause and use Move `******************************************************* Set NewDyn = OldDb.OpenRecordset _ ("SELECT * FROM Youth WHERE State = `FL'", dbOpenDynaset) NewDyn.MoveFirst Do Until NewDyn.EOF ewDyn.MoveNext Loop
TROUBLESHOOTING: When you use variables as the value to be compared to, you might encounter the error Cannot bind name item when you run the program. When the field and the variable you are comparing are string (or text) variables, surround the variable name by single quotes (`), as shown in the following sample code:Dim FindCrit As String, FindStr As String FindStr = "Smith" FindCrit = "Lastname = `" & FindStr & "`" NewDyn.FindFirst FindCritFor the sake of readability, you can also assign the single quote to a constant and use that constant in your code.
In the same manner, surround a date variable with the pound symbol (#) to compare it to a date field. You don't need to include any additional symbols when comparing numbers.
When a Find method is successful, the record pointer moves to the new record. If a Find method is not successful, the recordset's NoMatch property is set to True and the record pointer does not move. One way to use the NoMatch property is to write an If condition that checks the value, as shown in the following code:
If NewDyn.NoMatch Then `Notify user of event MsgBox "Record not found" Else `Process found record. command End If
The Seek method is the fastest way to locate an individual record in a table; however, it is also the most limiting of the record-positioning methods. The following list outlines the limitations of the Seek method:
The Seek method, as shown in Listing 31.9, consists of the method call, the comparison operator, and the values of the key fields. The comparison operator can be <, <=, =, >=, >, or <>. The key values being compared must be of the same data type as the fields in the controlling index. Although you are not required to include the same number of key values as there are fields in the index, you do have to include a key value for each field you want to search. These values must appear in the same order as the fields in the index and be separated by commas, as shown in the second part of Listing 31.9.
Dim OldDb As Database, OldTbl As Recordset Dim OldWs As WorkSpace Set OldWs = DBEngine.Workspaces(0) Set OldDb = OldWs.OpenDatabase("C:\YOUTH\YOUTHTRK.MDB") Set OldTbl = OldDb.OpenRecordset("Youth",dbOpenTable) `************************************ `Set the index property for the table `************************************ OldTbl.Index = "Name" `****************************************** `Execute the seek for the desired condition `****************************************** OldTbl.Seek ">", "Smith" `********************************************************* `Display information or "Not Found" message as appropriate `********************************************************* If OldTbl.NoMatch Then MsgBox "Not Found" Else MsgBox OldTbl("Lastname") & ", " & OldTbl("Firstname") End If `********************************************************* `Seek method with first and last name information supplied `********************************************************* OldTbl.Seek ">=", "Smith", "M"
You must carefully plan for one behavior of the Seek method. When the Seek method uses the comparison operators =, >=, >, or <>, Seek starts with the first record for the current index and scans forward through the index to find the first matching occurrence. If the comparison operator is < or <=, Seek starts with the last record in the table and scans backward through the table. If the index has unique values for each record, this presents no problem. However, if there are duplicate index values for the key fields being specified, the record found depends on the comparison operator and the sort order of the index. Figure 31.6 shows a table of first and last names indexed on last name and then first name. The table on the top is indexed in ascending order; the table on the bottom is indexed in descending order. Listing 31.10 shows four possible combinations of controlling index and comparison operator for finding a record for the last name of Smith. Each of these combinations is labeled in the comments of the code. Note that the comparison operator is a string value that is to be enclosed in quotes, as is the data that is to be compared (if it's in a Text field). Table 31.4 shows the results of each of these Seek operations.
FIG. 31.6
These tables show the difference between using ascending and descending order
in an index.
Dim OldDb As Database, OldTbl As Recordset Dim OldWs As WorkSpace Set OldWs = DBEngine.Workspaces(0) Set OldDb = OldWs.OpenDatabase("C:\YOUTH\YOUTHTRK.MDB") Set OldTbl = OldDb.OpenTable("Youth", dbOpenTable) `************************* `Set ascending order index `************************* OldTbl.Index = "Name" OldTbl.Seek ">=", "Smith", "A" printer.Print OldTbl("Lastname") & ", " & OldTbl("Firstname") OldTbl.Seek "<=", "Smith", "Z" printer.Print OldTbl("Lastname") & ", " & OldTbl("Firstname") `************************* `Set descending order index `************************* OldTbl.Index = "Name2" OldTbl.Seek ">=", "Smith", "A" printer.Print OldTbl("Lastname") & ", " & OldTbl("Firstname") OldTbl.Seek "<=", "Smith", "Z" printer.Print OldTbl("Lastname") & ", " & OldTbl("Firstname")
Seek Comparison Operator | Index Order | Resulting Record |
">=", "Smith, A" | Ascending | Smith, Adam |
"<=", "Smith, Z" | Ascending | Smith, Maureen |
">=", "Smith, A" | Descending | Roberts, Judy |
"<=", "Smith, Z" | Descending | Smith, Zachary |
Notice that you must also be careful when using the > ,< , >=, or <= operator on a descending index. The > (and >=)operator is interpreted as finding the record that occurs later in the index than the specified key value. That is why the ">=", "Smith" search on a descending index returns the record Roberts, Judy. Similar behavior is exhibited by the < and <= operators. As you can see from the preceding example, you must use care when choosing both the index sort order and the comparison operator with the Seek method to ensure that the desired results are achieved.
As with the Find methods, if a Seek is successful, the record pointer moves. Otherwise, the recordset's NoMatch property is set to True and the record pointer does not change. Figure 31.7 shows the Seek Name button and dialog box added to the sample case.
It is often desirable to be able to return to a specific record after the record pointer moves or new records are added. You can do so by using the Bookmark property of the recordset. The bookmark is a system-assigned variable that is correlated to the record and is unique for each record in a recordset. Listing 31.11 shows how to obtain the value of the bookmark for the current record, move to another record, and then return to the original record by using the bookmark previously obtained.
FIG. 31.7
The Seek button presents the user with an opportunity to enter search conditions.
Dim OldDb As Database, NewDyn As Recordset Dim OldWs As WorkSpace Set OldWs = DBEngine.Workspaces(0) Set OldDb = OldWs.OpenDatabase("C:\YOUTH\YOUTHTRK.MDB") Set NewDyn = OldDb.OpenRecordset _ ("SELECT * FROM Youth", dbOpenDynaset) `**************************************************** `Set a variable to the bookmark of the current record `**************************************************** CrntRec = NewDyn.Bookmark `********************** `Move to another record `********************** NewDyn.MoveNext `************************************************************* `Return to the desired record by setting the bookmark property ` to the previously defined value. `************************************************************* NewDyn.Bookmark = CrntRec
CAUTION: If you're working with a database type other than Jet, check the Bookmarkable property of the recordset you are using to see whether bookmarks are supported before you execute any methods that depend on the bookmarks.
If you must store multiple bookmark values, consider storing them in an array for faster processing. Listing 31.12 shows code that, while processing a mailing list, uses a bookmark array to identify customers whose birthdays are coming up.
ReDim BkMrk(1) nmbkmk = 0 NewDyn.MoveFirst Do Until NewDyn.EOF `*************************** `Check for birthday in month `*************************** If birthday Then `********************* `Add bookmark to array `********************* mbkmk = nmbkmk + 1 If nmbkmk > 1 Then ReDim Preserve BkMrk(1 To nmbkmk) End If BkMrk(nmbkmk) = NewDyn.Bookmark End If ewDyn.MoveNext Loop `***************** `Process bookmarks `***************** For I = 1 To nmbkmk ewDyn.Bookmark = BkMrk(I) Debug.Print Lastname, Birthday Next I
In addition to the Bookmark property, the Recordset object has two other properties that you can set to establish the position of the record pointer. These properties are AbsolutePosition and PercentPosition.
The PercentPosition property specifies the approximate position in a recordset where a record is located. By setting this property to a value between 0 and 100, you cause the pointer to move to the record closest to that location. Setting the property to a value outside the range causes an error to occur. You can use the PercentPosition property with all three types of recordsets.
The AbsolutePosition property enables you to tell the recordset to move to a specific record. The value of the property can range from 0 for the first record in the recordset to 1 less than the number of records. Setting a value outside of that range causes an error. Therefore, it is a good idea to include error checking in the code used to set the AbsolutePosition property. The AbsolutePosition property can be used only with dynasets and snapshots. Listing 31.13 shows how you can use the AbsolutePosition and PercentPosition properties. Note the validation of the requested position; this is used to prevent errors.
`Move to the percent position specified If rcpct > 100 Then rcpct = 100 If rcpct < 0 Then rcpct = 0 NewDyn.PercentPosition = rcpct `Move to the absolute position specified If rcabs > NewDyn.RecordCount Then rcabs = NewDyn.RecordCount If rcabs < 0 Then rcabs = 0 NewDyn.AbsolutePosition = rcabs
Filters, sorts, and indexes are properties of the Recordset object. You can set these properties by using an assignment statement such as:
NewDyn.Filter = "Lastname = `Smith'"
Filters, indexes, and sorts enable you to control the scope of records being processed and the order in which records are processed. Filters (which are available only for dynasets and snapshots) limit the scope of records by specifying that they meet certain criteria, such as "last name starts with M." Indexes (available only for tables) and sorts (available only for dynasets and snapshots) specify the order of a recordset based on the value of one or more fields in the recordset. For sorts and indexes, you can also specify ascending or descending sort order.
The Filter property is available only for dynasets and snapshots. Although the following discussion refers only to dynasets, the same statements hold true for snapshots. When set, the Filter property does not affect the current dynaset, but filters records that are copied to a second dynaset or snapshot created from the first.
You can specify the Filter property of a dynaset the same way you specify the Where clause of a SQL statement, but without the Where keyword. The filter can be a simple statement, such as State = `AL', or one that uses multiple conditions, such as State = `FL' AND Lastname = `Smith'. You can also use an expression, such as Lastname LIKE `M*', to find people whose last names begin with M. The following sample code shows how these Filter properties are set for a dynaset created from the Youth information table:
Dim NewDyn As Recordset, ScnDyn As Recordset Set NewDyn = OldDb.OpenRecordset("Youth",dbOpenDynaset) NewDyn.Filter = "State = `FL' AND Lastname = `Smith'" `Second recordset contains only "filtered" records. Set ScnDyn = OldDb.OpenRecordset(dbOpenDynaset)
You can include added flexibility in your Filter conditions by using functions in the condition. For example, if you want to filter a dynaset of all states with the second letter of the state code equal to L, use the Mid function, as shown here:
NewDyn.Filter = "Mid(State,2,1) = `L'"
Using functions does work, but it is an inefficient way to filter a dynaset. A better approach is to include the condition in the query used to create the dynaset.
More About Filters
The Filter condition of the dynaset has no effect on the current dynaset--only on secondary dynasets created from the current one. The only way to "filter" the existing recordset is to move through the recordset with the Find methods. By setting the Find condition to your Filter condition, you only process the desired records.If you work with only the filtered dynaset, it is more efficient to create the required dynaset by using the appropriate SQL clause in the OpenRecordset method. This method is shown here:
Fltr = "State = `FL' AND Lastname = `Smith'"
Set NewDyn = OldDb.OpenRecordset("SELECT * FROM Youth WHERE" & Fltr)
As with the Filter property, the Sort property is available only for dynasets and snapshots. Although the following discussion refers only to dynasets, the same statements apply to snapshots. You can specify the Sort property by providing the field names and order (ascending or descending) for the fields on which the dynaset is to be sorted. You can specify any field or combination of fields in the current dynaset. The Sort condition is similar to the Order By clause of a SQL statement. Listing 31.14 shows the syntax for setting the Sort property.
Dim OldDb As Database, NewDyn As Recordset, ScnDyn As Recordset Dim OldWs As WorkSpace Set OldWs = DBEngine.Workspaces(0) Set OldDb = OldWs.OpenDatabase("C:\YOUTH\YOUTHTRK.MDB") `***************************************************************** `The first method sets the sort property of one dynaset then ` creates a second dynaset from the first. `***************************************************************** Set NewDyn = OldDb.OpenRecordset("SELECT * FROM Youth") NewDyn.Sort = "Lastname,Firstname" Set ScnDyn = NewDyn.OpenRecordset() `***************************************************** `The second method creates the sorted Dynaset directly `***************************************************** Set ScnDyn = OldDb.OpenRecordset _ ("SELECT * FROM Youth ORDER BY Lastname,Firstname")
CAUTION: When specifying a multiple field sort, the order of the fields is important. A sort on first name and then last name yields different results than a sort on last name and then first name.
As was the case for the Filter property, the Sort property has no effect on the current dynaset; it specifies the order of any dynaset created from the current one. You can also achieve the same results of a sorted dynaset by specifying the Order By clause of the SQL statement used to create the dynaset. This alternate technique is shown in Listing 31.14.
You can use an index with a table to establish a specific order for the records or to work with the Seek method to find specific records quickly. For an index to be in effect, the Index property of the table must be set to the name of an existing index for the table. An example of how to use a program command to set the current index follows:
OldTbl.Index = "NameIndex"
The index specified for the table must be one that has already been created and is part of the indexes collection for the given table. If the index does not exist, an error occurs. The index is not created for you!
If the index you want does not exist, create it and then set the Index property of the table to the newly created index. The example shown in Listing 31.15 creates a ZIP Code index for the Youth table by creating a new Index object, appending a Field object to the Index object's Fields collection, then appending the Index object to the Recordset object's Indexes collection.
Dim Idx1 As Index, Fld1 As Field Set Idx1 = NewTbl.CreateIndex("Zip_Code") Set Fld1 = Idx1.CreateField("Zip") Idx1.Fields.Append Fld1 NewTbl.Indexes.Append Idx1 NewTbl.Index = "Zip_Code"
If your program needs an index, why not just create it at design time so you don't have to worry about creating it at runtime? There are several reasons why it may be more beneficial to create an index at runtime:
Of these reasons, the performance issue of updating multiple indexes is the one most often considered. To determine whether it is better to add the index at design time or to create it only when you need it, set up the application both ways and test the performance of each.
NOTE: Although it is desirable to limit the number of indexes your table has to keep current, it is advisable to have an index for each field that is commonly used in SQL queries. This is because the Jet engine (starting with version 2.0) employs query optimization that uses any available indexes to speed up queries.
Some programs, or program functions, are meant to find one specific piece of information in a database. However, the vast majority of programs and functions work with multiple records as a group. There are two basic methods of working with multiple records:
Method | Definition |
Program loops | Groups of commands contained inside a DO...WHILE, DO...UNTIL, or FOR...NEXT programming structure. The commands are repeated until the exit condition of the loop is met. |
SQL statements | Commands written in Structured Query Language that tell the database engine to process records. SQL is covered in detail in Chapter 29, "Understanding SQL." |
Most programmers are familiar with the use of Do...While and For...Next loops. In working with recordsets, all the programming principles for loops still apply. That is, you can perform a loop while a specific condition exists or for a specific number of records. Loops of this type were shown earlier in this chapter (refer to Listings 31.4 and 31.5).
Another way of working with multiple records forms an implied loop. Most data entry or data viewing programs include command buttons on the form to move to the next record or previous record. When a user repeatedly presses these buttons, he or she executes a type of program loop by repeating the move events. A special consideration for this type of loop is what to do when you are at the first record, the last record, or if you have an empty recordset. The problem is that if you move backward from the first record, forward from the last record, or try to move anywhere in an empty recordset, an error occurs. Fortunately, the Jet database engine provides some help in this area. There are properties of the recordset that can tell you when these conditions exist, as described in the following section.
You can use four main recordset properties to control the processing of multiple records in a recordset. Table 31.5 gives the definitions of these properties.
Property | Indicates |
BOF | Beginning of File flag, indicates whether the record pointer is positioned before the first record (BOF = True) or not (BOF = False). |
EOF | End of File flag, indicates whether the record pointer is positioned past the last record (EOF = True) or not (EOF = False). |
RecordCount | Indicates the number of records in the recordset that have been accessed. This gives a count of the total records in the recordset only after the last record has been accessed (for example, by using MoveLast), unless the recordset in question is a table-type recordset. |
NoMatch | Indicates that the last Find method or Seek method was unsuccessful in locating a record that matched the desired criteria. |
You can use these properties to terminate loops or prevent errors. Consider the data entry form in Figure 31.5. To prevent an error from occurring when the user presses the Next button, use code that allows the move only if the recordset is not at the end of the file. The following code takes this possibility into account:
If NOT OldDyn.EOF Then OldDyn.MoveNext If OldDyn.EOF Then DolDyn.MoveLast End If
Alternatively, you can disable the Next button when you reach the end of file. You can apply the same principle to the Previous button and the BOF condition. You might also want to check the RecordCount property of a recordset and enable only the Add Record button if the count is zero.
NOTE: After the MoveNext method has been executed, it is possible that the pointer is now at the end of the file (EOF). This would mean that there is no current record. Therefore, if the end of the file is encountered, a MoveLast method is used to make sure the record pointer is positioned at the last record in the recordset.
In addition to processing records with a program loop, you can use SQL statements to handle a number of functions that apply to multiple records. The following sections discuss two main types of functions:
Calculation Queries Calculation queries allow you to determine cumulative information about a group of records such as the total; average, minimum, and maximum values; and the number of records. Calculation queries also enable you to specify the filter criteria for the records. For example, you can extract total sales for all salesmen in the Southeast region or the maximum price of a stock on a given day (assuming, of course, that the base data is in your tables). Figure 31.8 shows a table of purchasing data for the fish inventory in an example database. The code in Listing 31.16 shows how to determine the total purchase costs for one type of fish and the minimum, maximum, and average unit cost of all the fish. Figure 31.9 shows the table that results from the SQL query.
FIG. 31.8
You can process purchasing data shown here with calculation queries or action
queries.
Dim OldDb As Database, NewDyn As Recordset, _ ewDyn2 As Recordset, SQL As String Dim OldWs As WorkSpace Set OldWs = dbEngine.Workspaces(0) Set OldDb = OldWs.OpenDatabase("C:\FISH\TRITON.MDB") `******************************************* `Use the SUM function to get the total cost. `******************************************* SQL = "SELECT SUM([Total Cost]) As Grand FROM Fishbuys _ WHERE Fishcode = 1001" Set NewDyn = OldDb.OpenRecordset(SQL) Print NewDyn("Grand") NewDyn.Close `***************************************************************** `Use the MIN, AVG, and MAX functions to get unit price statistics. `***************************************************************** SQL = "SELECT MIN([Unit Price]) As Mincst, _ AVG([Unit Price]) As Avgcst, " SQL = SQL + _ " MAX([Unit Price]) As Maxcst FROM Fishbuys WHERE Fishcode > 0" Set NewDyn2 = OldDb.OpenRecordset(SQL) Print NewDyn2("Mincst"), NewDyn2("Avgcst"), NewDyn2("Maxcst") NewDyn2.Close OldDb.Close
FIG. 31.9
A calculation query produces a dynaset with a single record containing the
results.
Using a calculation query can replace many lines of program code that would be required to produce the same results. In addition, a query is usually faster than the equivalent program code.
Action Queries Action queries operate directly on a recordset to insert, delete, or modify groups of records based on specific criteria. As with calculation queries, action queries perform the same work that would require many lines of program code. Listing 31.17 shows examples of several action queries.
Dim OldDb As Database, NewDyn As Recordset, NewQry As QueryDef Dim OldWs As WorkSpace Set OldWs = DBEngine.Workspaces(0) Set OldDb = OldWs.OpenDatabase("C:\FISH\TRITON.MDB") `****************************************** `Calculate the total cost of each purchase. `****************************************** SQL = _ "Update Fishbuys Set [Total Cost] = [Quantity] * [Unit Price]" Set NewQry = OldDb.CreateQueryDef("Calc Total", SQL) NewQry.Execute NewQry.Close `************************************** `Delete all records for Fishcode = 1003 `************************************** SQL = "Delete From Fishbuys WHERE Fishcode = 1003" Set NewQry = OldDb.CreateQueryDef("Del Fish", SQL) NewQry.Execute NewQry.Close OldDb.DeleteQueryDef ("Calc Total") OldDb.DeleteQueryDef ("Del Fish") OldDb.Close
CAUTION: When using action queries to modify groups of records, be very careful when specifying the WHERE clause of the query that defines the records to be modified. Improperly setting this clause can produce disastrous results, such as the deletion of all records in a recordset.
In this chapter, you have learned how to find specific records and how to move through a group of records. However, in most programs, you also must add, modify, and delete records. The commands covered in the following sections apply only to tables and dynasets (remember that snapshots are not updatable).
To add a new record to a recordset, use the AddNew method. AddNew does not actually add the record to the recordset; it clears the copy buffer to allow information for the new record to be input. To physically add the record after you've put data into the record's fields, use the Update method. Listing 31.18 shows how to add a new record to the recordset.
`********************************* `Use AddNew to set up a new record `********************************* NewDyn.AddNew `******************************************************* `Place the necessary information in the recordset fields `******************************************************* NewDyn("Lastname") = "McKelvy" NewDyn("Firstname") = "Mike" NewDyn("Address") = "6995 Bay Road" NewDyn("City") = "Pensacola" NewDyn("State") = "FL" NewDyn("Zip") = "32561" `************************************************************ `Use the update method to add the new record to the recordset `************************************************************ NewDyn.Update
CAUTION: Because AddNew places information only in the copy buffer, reusing the AddNew method or moving the record pointer with any Move or Find method (before using the Update method) clears the copy buffer. Any information entered in the record is therefore lost.
In a manner similar to adding a record, you use the Edit method to make changes to a record. The Edit method places a copy of the current record's contents into the copy buffer so that information can be changed. As with AddNew, the changes take effect only when the Update method is executed. Listing 31.19 shows the use of the Edit method.
`******************************************************* `Use the find method to locate the record to be changed. `******************************************************* NewDyn.FindFirst "Lastname = `McKelvy'" `******************************************** `Check the NoMatch Property to avoid an error `******************************************** If NewDyn.NoMatch Then MsgBox "Not Found" Else `**************************************************** `Use the edit method to set up the record for changes `**************************************************** ewDyn.Edit `*************************************************** `Change the necessary information in the copy buffer `*************************************************** ewDyn("Address") = "P. O. Box 380125" ewDyn("City") = "Birmingham" ewDyn("State") = "AL" ewDyn("Zip") = "35238" `*********************************************************** `Use the update method to write the changes to the recordset `*********************************************************** ewDyn.Update End If
CAUTION: Because Edit only places information in the copy buffer, reusing the Edit method or moving the record pointer with any Move or Find method (before using the Update method) clears the copy buffer. Any information entered in the record is therefore lost.
The Update method is used in conjunction with the AddNew and Edit methods to make changes to the recordsets. The Update method writes the information from the copy buffer to the recordset. In the case of AddNew, Update also creates a blank record in the recordset to which the information is written. In a multi-user environment, the Update method also clears the record locks associated with the pending Add or Edit method. (Listings 31.17 and 31.18 show the use of the Update method.)
NOTE: If you use Data controls to work with recordsets, the use of the Update method is not required. An update is automatically performed when a move is executed by the Data control.
Deleting a record requires the use of the Delete method, as shown in Listing 31.20. This method removes the record from the recordset and sets the record pointer to a null value.
`******** * * ******************************************** `Use the find method to locate the record to be deleted `****************************************************** NewDyn.FindFirst "Lastname = `McKelvy'" `******************************************** `Check the NoMatch property to avoid an error `******************************************** If NewDyn.NoMatch Then MsgBox "Not Found" Else `****************************************** `Use the delete method to remove the record `****************************************** ewDyn.Delete End If
CAUTION: After you delete a record, it is gone. You can recover the record only if you issued a BeginTrans command before you deleted the record, in which case you can RollBack the transaction. Otherwise, the only way to get the information back into the database is to re-create the record with the AddNew method.
Figure 31.10 shows some command buttons added to the data entry screen for the sample case. These buttons make use of the add, edit, and delete capabilities described in the preceding sections. The Delete Record button deletes the current record. The Add New Record button blanks out the text boxes to prepare them for new input. The Edit Record button prepares the recordset for editing. As a visual indication of editing, the foreground color of the text boxes also changes. Both the Edit Record and Add New Record buttons cause the normal command buttons (the Top, Previous, Next, Bottom, and Seek Name buttons) to be hidden and two new buttons to be displayed. The new buttons are Save and Cancel. The Save button stores the values displayed in the text boxes to the appropriate fields in the recordset and issues the Update method. The Cancel button terminates the Edit or Add process and restores the original information for the current record. After either Save or Cancel is selected, both buttons disappear and the eight main buttons are again shown.
NOTE: I stated previously that deletions and changes to the database are made without confirmation by the user. If you want your program to have confirmation built in, you have to provide it in your code. The easiest way to do this is through the MsgBox function. With this function, you can provide a warning to the user and ask for confirmation.
FIG. 31.10
Add, Edit, and Delete functions are added to the
data entry screen with new command buttons.
Transaction processing enables you to treat a group of changes, additions, or deletions to a database as a single entity. This is useful when one change to a database depends on another change, and you want to make sure that all changes are made before any of the changes become permanent. For example, you have a point-of-sale application that updates inventory levels as sales are made. As each item is entered for the sales transaction, a change is made to the inventory database. However, you only want to keep the inventory changes if the sale is completed. If the sale is aborted, you want to return the inventory database to its initial state before the sale was started. Transaction processing is a function of the Workspace object and, therefore, affects all databases open in a particular workspace.
Visual Basic provides three methods for transaction processing. These methods perform the following functions:
Transaction Method | Function |
BeginTrans | Starts a transaction and sets the initial state of the database. |
RollBack | Returns the database to its initial state before the BeginTrans statement was issued. When RollBack is executed, all changes made after the last BeginTrans statement are discarded. |
CommitTrans | Permanently saves all changes to the database made since the last BeginTrans statement. After the CommitTrans statement has been issued, the transactions cannot be undone. |
Listing 31.21 shows the BeginTrans, RollBack, and CommitTrans methods as they are used in an order entry application. The transactions are used in case the customer cancels the order prior to the completion of the order processing.
OldWs.BeginTrans `********************************************** `Perform loop until user ends sales transaction `********************************************** Do While Sales `********************************************** `Get item number and sales quantity from form ` Input Itemno,SalesQty ` Find item number in inventory `********************************************** Inv.FindFirst "ItemNum = " & Itemno `************************* `Update inventory quantity `************************* Inv.Edit Inv("Quantity") = Inv("Quantity") - SalesQty Inv.Update Loop `***************************************** `User either completes or cancels the sale `***************************************** If SaleComp Then OldWs.CommitTrans Else OldWs.Rollback End If
Some of the topics mentioned in this chapter are covered in greater detail in other portions of the book:
© Copyright, Macmillan Computer Publishing. All rights reserved.