A data access application can be written with only program commands, with only the data control and bound controls, or with a combination of these two methods. The data control and bound controls provide the user with easy access to a database. You connect them to the database by setting specific properties of the controls. This chapter discusses only the program commands. Chapter 5, "Using Visual Basic's Data and Bound Controls," discusses using the data control and bound controls.
When you use just the program commands, you work with the data access objects of Visual Basic. Using the data access objects and their associated program commands is a little 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 the actions of the data control and the bound controls. Therefore, they will help you understand the concepts behind the controls. And even if you use the data control, you also need some of the programming commands to augment the capabilities of the data control.
To demonstrate the similarities and differences between data access objects and the data control, this chapter and Chapter 5 instruct you to build a data entry screen that allows you to see how the programming commands work and how the data control implements the commands. Figure 4.1 shows the data entry screen that you build in this chapter.
This data entry screen is used as an example in this chapter.
The key reason you use program commands is the flexibility they give in addition to the data control. Because program commands do not directly access the database, you can perform more detailed input validation than is possible with just the data engine rules. You also can cancel changes to your edited data without using transactions. Program commands also provide a more 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 also allow you to do transaction processing.
In this chapter, you learn about the following:
The main new feature of version 4, with regard to data access objects, is the new recordset object. This object takes the place of the table, dynaset, and snapshot objects in earlier versions of Visual Basic. Instead of opening a table or creating a dynaset, you now open a recordset. Because you can still open a recordset with table, dynaset, or snapshot properties, it still makes sense to refer to these recordset types. Later in this chapter, the advantages and disadvantages of each type is discussed.
In addition, Visual Basic 4 provides three new methods of positioning the record pointer:
The first step in writing most data access programs is setting up a link to the database with which you want to work. When you create a new database (as described in Chapter 3), that database is available for you to work with in your program until you exit the program or explicitly close the database. If you have an existing database, you must open it before your program can work with it. Opening a database is done using the OpenDatabase method of the Workspace object. To use the OpenDatabase method, you create a database object and call the method as shown in this bit of code:
These commands open an Access database with the default options of read/write data access and shared access. The full syntax of the OpenDatabase method allows you to specify that the database should be opened exclusively (no other users or programs may 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 nonAccess database, you can specify the database type. The use of exclusive access and read-only access are usually required for multiuser applications (as discussed in Chapter 7, "Creating Multiuser Programs"). The use of nonAccess databases is covered in Chapter 8, "Accessing Other Databases with the Jet Engine."
However, you may 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 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 listing. 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.
After the database is opened, you must still define a recordset to access the data in the database. Think of opening the database as telling the program where to go to find the data and defining the recordset as telling the program what data to use.
There are three types of recordsets available in Visual Basic:
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 is the physical representation of the database design. Because all data in a database is stored in tables, accessing tables 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 is 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 but does not allow the convenience of changing records in multiple tables with a single command such as an action query.
There are several advantages in using tables in your programs:
Of course, there are also disadvantages in using tables in your programs:
These disadvantages can usually be overcome with programming, but the solutions are often less than elegant. Some of the workarounds are discussed later in this chapter when you look at the various methods for moving through a recordset and finding specific records.
To open a table for use by the program, you define a Recordset object and then use the OpenRecordset method to access the table. You also specify the dbOpenTable constant in the parameters of the method to identify the type of recordset to create, as shown in the following segment of code. This listing assumes that you have already opened the database using the OldDb object and that the database contains a table called Customers.
These commands open an Access table with the default parameters of shared use and read/write mode. Optional parameters may be included 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 4.1.
Table 4.1 Options Used to Modify the Access Mode of Tables
| Option | Action |
| dbDenyWrite | Prevents others in a multiuser environment from writing to the table while you have it open. |
| dbDenyRead | Prevents others in a multiuser environment from reading the table while you have it open. |
| dbReadOnly | Prevents you from making changes to the table. |
If you have existing code that uses table objects and the OpenTable function, Visual Basic 4 still supports this code. However, you should use the new methods for any future work you do.
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 reflect additions or deletions of records made by other users. This makes dynasets less useful for some types of multiuser applications.
A dynaset is actually a set of record pointers that point to the specified data that 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.
Some of the advantages provided by dynasets are as follows:
dynasets do have some limitations:
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 using the OpenRecordset method. 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 4.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.
In the creation of a dynaset, any valid SQL statement can be used. (The details of using SQL statements are covered in Chapter 6, "Understanding Structured Query Language (SQL).")In creating a dynaset, you may also specify options that affect its behavior. These options are listed in table 4.2.
Table 4.2 Options Used to Modify the Access Mode of a dynaset
| Option | Action |
| dbDenyWrite | Prevents others in a multiuser environment from writing to the dynaset while you have it open. |
| dbReadOnly | Prevents you from making changes to the dynaset. |
| dbAppendOnly | Allows 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. |
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. The advantage of using ODBC is that the Visual Basic programmer can connect to the information on the database servers without having to know the inner workings of the engine.
A dynaset can also be created from another dynaset or from a QueryDef (listing 4.2 shows the creation from another dynaset). 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 allows 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 4.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. The results of these two dynasets are shown in figures 4.2 and 4.3.
Listing 4.2 How to Set the Filter and Sort Properties of a dynaset and Create a Second dynaset from the First
The results of the creation of a dynaset from base tables.
The results of creating one dynaset from another dynaset after filter and sort conditions have been set.
You may well ask, "If I need the results in the second dynaset, can't I just create it from the base tables in the first place?" The answer is that you can do it if your application needs only the second table. However, consider an order entry system in which you want access to all your customers (the creation of the first dynaset), and one of the functions of the system is to generate a mailing list for a sales 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 created from a QueryDef, dynaset, or another snapshot. A snapshot differs from a dynaset in that it is not updatable. The most frequent use of snapshots in a program is to generate reports or informational screens in which the data is static.
snapshots provide the programmer with the following advantages:
The primary disadvantage of using a snapshot is that it is not an updatable recordset. In addition, you cannot create an index on a snapshot to help set the order of the data or locate specific records.
A snapshot is created by defining a recordset object with the Dim statement and then using the OpenRecordset method to assign the records to the object (as shown in listing 4.3. As with a dynaset, there are optional parameters that can be specified in the OpenRecordset method. These parameters are summarized in table 4.3.
Listing 4.3 Create a snapshot in Much the Same Way You Create a dynaset
Table 4.3 Options Used to Modify the Access Mode of a snapshot
| Option | Action |
| dbDenyWrite | Prevents others in a multiuser environment from writing to the snapshot while you have it open. |
| dbForwardOnly | Allows only forward scrolling through the snapshot. |
| dbSQLPassThrough | Passes the SQL statement used to create the snapshot to an ODBC database to be processed. |
Suppose that you have written a data entry screen using the data and bound controls. To get information on the screen, you simply draw the bound control and then set the appropriate data field for the control. 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 in the toolbox to display data, not just the controls specifically designated for use with the data control.
For the sample case in this chapter, you create a customer data entry screen based on the Customers table of the sample database. To begin building this screen, start a new project in Visual Basic. Then on the default form, you add the data labels and text boxes to hold the data from the table. The form with these controls added is shown in figure 4.4.
Use unbound controls to display data from the data access objects.
To set up the table for use, you must open the table using the OpenRecordset method. For this case, place the Dim statement that defines the data access objects in the 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 4.4). At this point, the table is open and you are positioned at the first record in the table.
Listing 4.4 Placing the OpenDatabase and OpenRecordset in the Form_Load Event
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. This process is shown in listing 4.5. Notice that the listing defines the text boxes as a control array so that a loop can be used to quickly modify certain properties of the controls such as foreground color or visibility. This method is used later in the chapter. Also notice that the assignments are placed in a subroutine so that the same routine can be called from a number of command button events instead of repeating the code in each event. This arrangement makes the code more efficient and easier to maintain.
Listing 4.5 Assigning Data Fields to the Display Properties of the Form's Controls
You can find the commands from these listings, and the other listings used to build the data entry screen, in the CHAPTER4.MAK file on the companion disc.
Because a database with only one record is fairly useless, a database engine must provide methods for moving from one record to another within recordsets. Visual Basic provides four classes of such methods:
Each of these methods has benefits and limitations, as described in the following sections.
You can use the move methods on any recordsets available in Visual Basic. The move methods are as follows:
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 the 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 fig. 4.5). To activate these buttons, add the code shown in listing 4.6. The code for each button is preceded by an identifying comment line.
Add command buttons to allow the user to navigate through the recordset.
Listing 4.6 Assigning Move Methods to Navigation Command Buttons to Make Them Work
The Move n method allows you to 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.
The find methods are available for use only on dynasets and snapshots. You cannot use find methods on table objects. (Because the data entry screen was created with a table, you cannot use the find methods in the sample.) The find methods are used to locate records that meet specified criteria. The criteria is expressed in the same way that the Where clause of a SQL command is specified—except without the Where keyword. The four find methods are as follows:
After the find method is run, you should check the status of the NoMatch property. 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 4.7 shows the use of the find methods to move through a dynaset.
Listing 4.7 How to Move through Selected Records in a dynaset Using Find Methods
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. Searches can by optimized by the Jet engine if an index is available for the search field. If you will be doing many searches, you should consider creating an index for the field in its base table.
In many cases, it is faster to re-create the dynaset using the search criteria than 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. Listing 4.8 shows the comparison of these two methods.
Listing 4.8 Creating a dynaset with a Filter Condition in the SQL Statement or Creating a Second dynaset after Setting the Filter Property of the First dynaset
Comparing Variables with the Find Method
When you use variables as the value to be compared to, you may 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. For 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, you would surround a date variable with the pound symbol to compare it to a date field. You do not need to include any additional symbols when comparing numbers.
When a find method is successful, the record pointer moves to the new record. If the 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:
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 limitations of the Seek method are as follows:
A Seek method, as shown in listing 4.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 4.9.
Listing 4.9 Using the Seek Method to Find a Specific Record in a Table
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 4.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 4.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. The results of each of these Seek operations are shown in table 4.4.
These tables show the difference between using ascending and descending order in an index.
Listing 4.10 Varying Results Are Obtained Using Different Seek Operators and Index Orders on a Table
Table 4.4 Different Seek Comparison Operators and Index Sort Orders Yield Different Results
| 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 > 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 < operator. As can be seen from the preceding example, 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 4.7 shows the Seek Name button and dialog box added to the sample case.
Using a dialog box to obtain the Seek condition desired by the user.
It is often desirable to find a specific record, even 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 4.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 using the Bookmark previously obtained.
Listing 4.11 Using a Bookmark to Return to a Specific Record in a Recordset
If you are working with a database other than an Access database, 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 4.12 shows code that, while processing a mailing list, uses a bookmark array to identify customers whose birthdays are coming up.
Listing 4.12 Storing Multiple Bookmarks in an Array
Filters, sorts, and indexes are properties of the recordset object. These properties are set using an assignment statement such as
Filters, indexes, and sorts allow the programmer 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, ascending or descending sort order can also be specified.
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.
The filter property of a dynaset is specified in the same manner as 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 customer information tables.
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:
Using functions does work, but it is an inefficient way to filter a dynaset. The better approach would be 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 will only process the desired records.
If you work with only the filtered dynaset, it is more efficient to create the required dynaset using the appropriate SQL clause in the OpenRecordset method. This method is shown here:
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. The sort property is specified 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 syntax for setting the sort property is shown in listing 4.13.
Listing 4.13 Two Methods for Creating a Filtered dynaset
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 also shown in listing 4.13.
An index is used 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. If you want to use an index that does not already exist, you must create it using the methods described in Chapter 3, then set the index property of the recordset to the new index name. Following is an example of how to use a program command to set the current index.
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 as described in Chapter 3, "Implementing the Database Design," and then set the index property of the table to the newly created index. The example shown in listing 4.14 creates a Zip Code index for the Customers table.
Listing 4.14 Creating a New Index and Setting the Index Property
If your program needs an index, why not just create it at design time and not worry about having to create it at run time? There are several reasons for not doing this:
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 create it only when you need it, set up the application both ways and test the performance both ways.
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.
There are some programs, or program functions, concerned with finding one specific piece of information in a database. However, the vast majority of programs and functions work with multiple records from the database. There are two basic methods of working with multiple records:
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 4.4 and 4.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.
There are four main recordset properties that can be used to control the processing of multiple records in a recordset. The definitions of these properties are given in table 4.5.
Table 4.5 Properties Used to Control Loop Processing
| Property | Indicates |
| BOF | Beginning of File flag, indicates whether the record pointer is at the first record (BOF = True) or not (BOF = False). |
| EOF | End of File flag, indicates whether the record pointer is at the last record (EOF = True) or not (EOF = False). |
| RecordCount | Indicates the number of records in the recordset which have been accessed. This will give a count of the total records in the recordset only after the last record has been accessed (for example, by using a MoveLast method.) |
| NoMatch | Indicates that the last find method or Seek method was unsuccessful in locating a record that matched the desired criteria. |
These properties can be used to terminate loops or prevent errors. Consider the data entry form in figure 4.5. To prevent an error from occurring when the user presses the Next button, you may want to 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). Alternatively, you may want to disable the Next button when you reach the end of file. The same principal can be applied to the Previous button and the BOF condition. You may also want to check the RecordCount property of a recordset and enable only the Add Record button if the count is zero.
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 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 let you 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 4.8 shows a table of purchasing data for the fish inventory of the sample case. The code in listing 4.15 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 4.9 shows the results table from the SQL query.
Purchasing data shown here can be processed with calculation queries or action queries.
Listing 4.15 Using Calculation Queries to Determine Information about Data in the Recordset
The 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. The query and the equivalent program code are contained in the project SQLCALC.MAK on the companion disc.
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 4.16 shows examples of several action queries.
Listing 4.16 Using Action Queries to Perform Operations on Multiple Records
When using action queries to modify groups of records, be very careful in 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, use the Update method. Listing 4.17 shows how to add a new record to the recordset.
Listing 4.17 Using AddNew and Update to Add a Record to the Recordset
Because AddNew places information only in the copy buffer, reusing the AddNew method or moving the record pointer with any move or find 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 4.18 shows the use of the Edit method.
Listing 4.18 Using Edit and Update to Change the Data in a Record
Because Edit only places information in the copy buffer, reusing the Edit method or moving the record pointer with any move or fine 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 multiuser environment, the Update method also clears the record locks associated with the pending add or edit method. (The use of the Update method is shown in listings 4.17 and 4.18.)
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 4.19. This method removes the record from the recordset and sets the record pointer to a null value.
Listing 4.19 Using Delete to Remove a Record from the Recordset
Once 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 4.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.
Add, edit, and delete functions are added to the data entry screen with new command buttons.
Transaction processing allows 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 statements for transaction processing. These statements perform the following functions:
Listing 4.20 shows the BeginTrans, RollBack, and CommitTrans statements in use in the sales example mentioned earlier in this section.
Listing 4.20 Using Transaction Processing to Handle Multiple Changes to a Database as One Group
Some of the topics mentioned in this chapter are covered in greater detail in other portions of the book. Please refer to these chapters:
© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.
