Chapter Three

• Visual Basic Database Objects
  • Dataset-Oriented Versus Data Record-Oriented
  • • Visual Basic and Data Objects
    • Dataset Size Affects Program Performance
  • The Dynaset-Type Recordset Data Object
    • Strengths of the Dynaset-Type Recordset Object
      • Figure 3.1.
      • Figure 3.2.
  • Listing 3.1. Creating a Database object and a Recordset object.
  • Listing 3.2. Declaring database and data table variables.
  • Listing 3.3. Opening the database and creating the Dynaset.
  • Listing 3.4. Counting the records in a Dynaset.
    • Figure 3.3.
  • Listing 3.5. Adding a new Recordset object and string variable.
  • Listing 3.6. Using the Filter property to create a Recordset.
  • Listing 3.7. Displaying the record count of the filtered Recordset.
    • Figure 3.4.
  • Listing 3.8. Cloning a new Recordset.
    • Figure 3.5.
  • Listing 3.9. Coding the cmdSaveguidemarks_Click event for guideMARKS.VBP.
    • Limitations of the Dynaset-Type Recordset Data Object
    • When to Use the Dynaset-Type Recordset Data Object
  • The Table-Type Recordset Data Object
    • Strengths of the Table-type Recordset Data Object
      • Figure 3.6.
  • Listing 3.10. Declaration code for the TBSEEK.VBP project.
  • Listing 3.11. Coding the Form_Load routine of TBSEEK.VBP.
  • Listing 3.12. Coding the LoadList routine of TBSEEK.VBP.
  • Listing 3.13. Coding the Seek routine for TBSEEK.VBP.
    • Figure 3.7.
  • Limitations of the Table-Type Recordset Data Object
    • When to Use the Table-Type Recordset Data Object
  • The Snapshot-Type Recordset Data Object
  • Listing 3.14. Comparing Snapshot-type and Dynaset-type Recordsets.
    • Figure 3.8.
  • When to Use the Snapshot Data Object
  • The Database Data Objects
  • Listing 3.15. Listing Database objects.
    • Figure 3.9.
    • Figure 3.10.
  • Summary
  • Quiz
  • Exercises

Chapter Three

Visual Basic Database Objects

In the previous day's lesson, you learned how to create simple data entry forms using some of the data-bound controls and the various data field types. Today you learn about the programmatic data objects of Visual Basic 5.0. Data objects are used within a Visual Basic program to manipulate databases, as well as the data tables and indexes within the database. The data objects are the representations (in program code) of the physical database, data tables, fields, indexes, and so on. Throughout today's lesson, you create small Visual Basic programs that illustrate the special features of each data object.

Every Visual Basic program that accesses data tables uses data objects. Even if you are only using the data-aware controls (for example, the data control and bound input controls) and are not writing programming code, you are still using Visual Basic data objects.

The primary data object used in Visual Basic programs is the Recordset object. This is the object that holds the collection of data records used in your Visual Basic programs. There are three different types of Recordset objects. They are

• Dynaset-type Recordset object
• Table-type Recordset object
• Snapshot-type Recordset object

Any one of these Recordset objects can be used to gain access to an existing data table in a database. However, they each have unique properties and behave differently at times. Today you learn how these three types of Recordset data objects differ and when it is best to use these objects in your programs.

---

NOTE: In previous versions of Visual Basic, the Recordset object types were available as unique data objects (Dynaset, Table, and Snapshot). These objects can still be used when working with the older (version 2.5) data access object model, but it is not recommended. All data access object models now support the Recordset object types and that is the object you should use in all new Visual Basic programs.

---

You also learn about another data object today: the Database object. You can use the Database object to get information about the connected database. In this lesson, you learn about the general properties and behaviors of the Database object of the data control and how you can use them in your programs.

---

NOTE: You learn more about the Database object in Day 9 "Visual Basic and the Microsoft Jet Engine."

---

Dataset-Oriented Versus Data Record-Oriented

Before you learn about Visual Basic data objects, you should first learn some basics of how Visual Basic operates on databases in general. When you understand how Visual Basic looks at databases, you can better create programs that meet your needs.

The database model behind the Microsoft Access database and other SQL-oriented databases is quite different from the database model behind traditional PC databases such as FoxPro, dBASE, and Paradox. Traditional PC databases are record-oriented database systems. Structured Query Language (SQL) databases are dataset-oriented systems. Understanding the difference between record-oriented processing and dataset-oriented processing is the key to understanding how to optimize database programs in Visual Basic.

In record-oriented systems, you perform database operations one record at a time. The most common programming construct in record-oriented systems is the loop. The following pseudocode example shows how to increase the price field of an inventory table in a record-oriented database:

Processing in record-oriented systems usually involves creating a routine that reads a single data record, processes it, and returns to read another record until the job is completed. PC databases use indexes to speed the process of locating records in data tables. Indexes also help speed processing by allowing PC databases to access the data in sorted order (by LastName, by AccountBalance, and so on).

In data-oriented systems, such as Microsoft Access, you perform database operations one set at a time, not one record at a time. The most common programming construct in set-oriented systems is the SQL statement. Instead of using program code to loop through single records, SQL databases can perform operations on entire tables from just one SQL statement. The following pseudocode example shows how you would update the price field in the same inventory file in a dataset-oriented database:

The UPDATE SQL command behaves with SQL databases much like keywords behave with your Visual Basic programs. In this case, UPDATE tells the database that it wants to update an entire table (the Inventory table). The SET SQL command changes the value of a data field (in this case, the Price data field). The WHERE command is used to perform a logical comparison of the SalesRegion field to the value Northeast. As you can see, in dataset-oriented databases, you create a single statement that selects only the records you need to perform a database operation. After you identify the dataset, you apply the operation to all records in the set. In dataset systems, indexes are used to maintain database integrity more than to speed the location of specific records.

Visual Basic and Data Objects

Visual Basic database objects are dataset-oriented. Visual Basic programs generally perform better when data operations are done with a dataset than when data operations are done on single records. Some Visual Basic objects work well when performing record-oriented operations; most do not. The Visual Basic table-type Recordset object is very good at performing record-oriented processing. The Visual Basic Dynaset- and snapshot-type Recordset objects do not perform well on record-oriented processes.

A common mistake made by database programmers new to Visual Basic is to create programs that assume a record-oriented database model. These programmers are usually frustrated by Visual Basic's slow performance on large data tables and its slow response time when attempting to locate a specific record. Visual Basic's sluggishness is usually due to improper use of Visual Basic data objects--most often because programmers are opening entire data tables when they only need a small subset of the data in order to perform the required tasks.

Dataset Size Affects Program Performance

Unlike record-oriented systems, the size of the dataset you create affects the speed at which Visual Basic programs operate. As a data table grows, your program's processing speed can deteriorate. In heavily transaction-oriented applications, such as accounting systems, a dataset can grow quickly and cripple your application's ability to process information. If you are working in a network environment where the machine requesting data and the machine storing the data are separated, sending large datasets over the wire can affect not only your application, but all applications running on the network. For this reason, it is important to keep the size of the datasets as small as possible. This does not mean you have to limit the number of records in your data tables! You can use Visual Basic data objects to select the data you need from the table instead.

For example, you might have a data table that contains thousands of accounting transactions. If you want to modify the payment records in the data table, you can create a data object that contains all of the records (quite a big set), or you can tell Visual Basic to select only the payment records (a smaller set). Or, if you know that you only need to modify payment records that have been added to the system in the last three days, you can create an even smaller dataset: The smaller the dataset, the faster your program can process the data. Visual Basic data objects give you the power to create datasets that are the proper size for your needs.

The Dynaset-Type Recordset Data Object

The Visual Basic Dynaset-type Recordset data object is the most frequently used data object in Visual Basic programs. It is used to dynamically gain access to part or all of an existing data table in a database, hence the name Dynaset. When you set the DatabaseName and RecordSource properties of a Visual Basic data control, you are actually creating a Visual Basic Dynaset-type Recordset. You can also create a Dynaset-type Recordset by using the CreateDynaset method of the Database object.

When you create a Visual Basic Dynaset-type Recordset, you do not create a new physical table in the database. A Dynaset exists as a virtual data table. This virtual table usually contains a subset of the records in a real data table, but it can contain the complete set. Because creating a Dynaset does not create a new physical table, Dynasets do not add to the size of the database. However, creating Dynasets does take up space in RAM on the machine that creates the set (the one that is running the program). Depending on the number of records in the Dynaset, temporary disk space can also be used on the machine requesting the dataset.

Strengths of the Dynaset-Type Recordset Object

There are several reasons to use Dynasets when you access data. In general, Dynasets require less memory than other data objects and provide the most update options, including the capability to create additional data objects from existing Dynasets. Dynasets are the default data objects for the Visual Basic data control, and they are the only updatable data object you can use for databases connected through Microsoft's Open Database Connectivity (ODBC) model. The following sections provide more details of the strengths of the Dynaset data object. Dynasets Are Really Key Sets Visual Basic Dynasets use relatively little workstation memory, even for large datasets. When you create a Dynaset, Visual Basic performs several steps. First, Visual Basic selects the records you requested. Then, it creates temporary index keys to each of these records and sends the complete set of keys to your workstation along with enough records to fill out any bound controls (text boxes and/or grid controls) that appear on your on-screen form. This process is illustrated in Figure 3.1.

Figure 3.1. Dynasets contain key sets that point to the actual data.

---

NOTE: The actual data request engine used by Visual Basic is called the Microsoft Jet data engine. In pure SQL systems, all requests for data result in a set of data records. Data requests to the Microsoft Jet engine result in a set of keys that point to the data records. By returning keys instead of data records, Microsoft Jet engine is able to limit network traffic and speed database performance.

---

The set of keys is stored in RAM and--if the set is too large to store in RAM alone--in a temporary file on a local disk drive. As you scroll through the dataset, Visual Basic retrieves actual records as needed from the physical table used to create the Dynaset. If you have a single text box on the form, Visual Basic retrieves the data from the table one record at a time. If you have a grid of data or a loop that collects several records from the table in succession, a small set of the records in the dataset is retrieved by Visual Basic. Visual Basic also caches records at the workstation to reduce requests to the physical data table, which speeds performance.

If the Dynaset is very large, you might end up with a key set so large that it requires more RAM and temporary disk space than the local machine can handle. In that case, you receive an error message from Visual Basic. For this reason, it is important that you use care in creating your criteria for populating the dataset. The smaller the dataset, the smaller the key set. Dynasets Are Dynamic Even though Dynasets are virtual tables in memory created from physical tables, they are not static copies of the data table. After you create a Dynaset, if anyone else alters the underlying data table by modifying, adding, or deleting records, you see the changes in your Dynaset as soon as you refresh the Dynaset. Refreshing the Dynaset can be done using the Refresh method. You can also refresh the Dynasets by moving the record pointer using the arrow keys of the data control or using the MoveFirst, MoveNext, MovePrevious, and MoveLast methods. Moving the pointer refreshes only the records you read, not the entire Dynaset.

Although the dynamic aspect of Dynasets is very effective in maintaining up-to-date views of the underlying data table, Dynasets also have some limitations and drawbacks. For example, if another user deletes a record that you currently have in your Dynaset and you attempt to move to that record, Visual Basic reports an error. Dynasets Can Be Created from More than One Table A Dynaset can be created using more than one table in the database. You can create a single view that contains selected records from several tables, update the view, and therefore update all the underlying tables of the data at one time. This is a very powerful aspect of a Visual Basic Dynaset data object. Using Visual Basic Dynasets, you can create virtual tables that make it easy to create simple data entry screens and display graphs and reports that show specialized selections of data. Use Dynasets to Create Other Dynasets or Snapshots Often in Visual Basic programs, you need to create a secondary dataset based on user input. The Dynaset data object is the only data object from which you can create another Dynaset.

You can create additional Dynasets by using the Clone method or the CreateDynaset method. When you clone a Dynaset, you create an exact duplicate of the Dynaset. You can use this duplicate to perform look-ups or to reorder the records for a display. Cloned Dynasets take up slightly less room than the original Dynaset.

Let's put together a short code sample that explores Dynasets. You do this all in Visual Basic code, too, instead of using the Visual Basic data control.

First start a new Visual Basic 5.0 Standard EXE project. Be sure to add a reference to the Microsoft DAO 3.5 Object Library before you begin coding. To do this, Select Project | References from the Main menu (see Figure 3.2).

Figure 3.2. Adding the Microsoft DAO 3.5 Reference to a Visual Basic Project.

Now double-click the form to open the code window to the Form_Load event. You write the entire example in this procedure.

When you open a Dynaset using Visual Basic code instead of using the data control, you must create two Visual Basic objects: a Database object and a Recordset object. Listing 3.1 shows how you create the objects in Visual Basic code.

Listing 3.1. Creating a Database object and a Recordset object.

You must initialize these objects with values before they can access data. This process is similar to setting the properties of the data control. To initialize the values, you first create two variables that correspond to the DatabaseName and RecordSource properties of the Visual Basic data control. The code sample in Listing 3.2 shows how it is done.

---

TIP: The code sample in Listing 3.2 uses the App.Path Visual Basic keywords. You can use the Path method of the App object to determine the drive letter and directory from which the program was launched. In most projects throughout this guide, you find the databases are stored in the same directory as the sample projects. By using the App.Path method as part of the database name, you always point to the correct drive and directory for the required file.

---

Listing 3.2. Declaring database and data table variables.

---

TIP: Notice that you created two string variables, and both variable names start with the letters "str", which stand for string type. This is the prefix of the variable name. The prefix of the name tells you what type of data is stored in the variable. This is common programming practice. Adhering to a strict naming convention makes it easier to read and maintain your programs.

---

Before you continue with the chapter, save this form as DYNASETS.FRM and save the project as DYNASETS.VBP.

Now that you have created the data objects, created variables to hold database properties, and initialized those variables with the proper values, you are ready to actually open the database and create the Dynaset-type Recordset. The code in Listing 3.3 shows how to do this using Visual Basic code.

Listing 3.3. Opening the database and creating the Dynaset.

There are two added lines in Listing 3.3. The first added line opens the guideS5.MDB database and sets the Visual Basic database object db to point to the database. This gives your Visual Basic program a direct link to the selected database.

---

TIP: Note that this database object was created using the OpenDatabase method of the DBEngine object. The DBEngine is covered in greater detail on Day 9.

---

Now you can use the db data object to represent the open database in all other Visual Basic code in this program. The second line creates a Dynaset-type Recordset object that contains all the records in the Titles table. The Visual Basic rs object is set to point to this set of records. Notice that the OpenRecordset method is applied to the db Database object.

---

TIP: Notice that these last two lines of code use the Set keyword. This Visual Basic keyword is used to initialize all programming objects. You might think that you could perform the same task using the following code line:

Rs = db.OpenRecordSet(strRSName,dbOpenRecordset)

However, this does not work. In Visual Basic, all objects must be created using the Set keyword.

---

The code in Listing 3.3 is all that you need to open an existing Microsoft Access database and create a Dynaset-type Recordset ready for update. However, for this project, you want to see a bit more. Let's add some code that tells you how many records are in the Titles data table.

You need one more variable to hold the record count. You also use the MoveLast method to move the record pointer to the last record in the Recordset. This forces Visual Basic to touch every record in the collection, and therefore gives you an accurate count of the total number of records in the table. You get the count by reading the RecordCount property of the Recordset. When you have all that, you display a Visual Basic message box that tells you how many records are in the Recordset. Listing 3.4 contains the code to add.

Listing 3.4. Counting the records in a Dynaset.

Save the form (DYNASETS.FRM) and project (DYNASETS.VBP) again and run the program. You see a message box telling you how many records are in the Recordset. Figure 3.3 shows the results of a typical run.

Figure 3.3. Displaying the RecordCount of a Recordset.

You can use the OpenRecordset command on an existing Recordset to create a smaller subset of the data. This is often done when the user is allowed to create a record selection criterion. If the dataset returned is too large, the user is allowed to further qualify the search by creating additional criteria to apply to the dataset.

Let's modify DYNASETS.VBP to create a smaller Dynaset-type Recordset from the existing Recordset. You need to create a new Recordset object and a new variable called strFilter to hold the criteria for selecting records. The code in Listing 3.5 shows how to add the object and variable to the existing DYNASETS.VBP project.

Listing 3.5. Adding a new Recordset object and string variable.

Now that you have the object and the variable (marked with <<< in Listing 3.5), you can add code that creates a new Recordset. First you set the Filter property of the existing Recordset using the variable you just created. Then you create the new Recordset from the old one. See the last two lines of the code in Listing 3.6.

Listing 3.6. Using the Filter property to create a Recordset.

Now that you've created the new Recordset from the old one, you can get a count of the selected records. You can add the same code you used earlier: Move to the end of the Recordset, get the RecordCount, and show it in a message box. Listing 3.7 shows the completed program.

Listing 3.7. Displaying the record count of the filtered Recordset.

Save and run the code to check the results (see Figure 3.4). Notice that the first record count (the full dataset) is larger than the second record count (the filtered dataset).

Figure 3.4. Display RecordCount of the Filtered Recordset.

It is also important to notice that the second Recordset object was created from the first Recordset object. This a very powerful feature of Visual Basic. When you want to get a smaller dataset, you don't have to reload the data from the database; you can use an existing Recordset as the source for a new dataset.

---

TIP: Creating subsets of a Recordset in this manner can sometimes be slower than simply creating a new Recordset from the database itself. The exception to this rule is when your database is stored at a distant server. In cases where your source data is far away and possibly available only over a slow network connection, using the Filter property to create subsets of data can be faster.

---

Now let's make one more series of changes to DYNASETS.VBP that illustrate the Clone method for Recordsets. Cloning a Recordset makes a duplicate of the set. Add another data object (rs3), and add the Clone Recordset program code in Listing 3.8.

Listing 3.8. Cloning a new Recordset.

Notice that all you have to do to clone a Recordset is to use the Clone method to load a new Recordset object variable. When you run the program this time, you see that the Recordset created using the Clone method contains the same number of records as its parent. Dynasets Can Use guidemarks, Filters, and Sorts Dynaset-type Recordsets can use the guidemark, Filter, and Sort properties to reorder data for display (Sort) or create a subset of the Recordset (Filter). Using the Visual Basic Find method on a Recordset forces Visual Basic to start at the first record in the collection and read each one until a match is found. Once the selected record is found, your user may want to return to the record that was displayed before the search began. That's what Visual Basic guidemarks do. They remember where you were.

When you search for a record in the dataset using one of the Find methods, you should set guidemarks before your search to remember where you started. This is especially handy if your Find criteria results in a null record. When a FindFirst method fails to locate the desired record, the record pointer is set to the first record in the collection. If you have saved the guidemark before starting the search, you can reset the Visual Basic guidemark and return the user to the place from which the search started.

Let's build a quick project to demonstrate the use of guidemarks. Use the information in Table 3.1 to create a small form with a data control, two bound input controls, two label controls, and a single command button.

Table 3.1. Controls for guideMARKS.FRM.

Refer to Figure 3.5 as a guide for sizing and locating the controls on the form.

Figure 3.5. Laying out the guidemark Demonstration form.

When you have completed the form layout, add the following code behind the command button. The code in Listing 3.9 is a toggle routine that saves the current place in the table by reading (and storing) the guidemark, or restores the previous place in the table by reading (and updating) the guidemark.

Listing 3.9. Coding the cmdSaveguidemarks_Click event for guideMARKS.VBP.

---

TIP: Listing 3.9 uses two Static variables. Static variables keep their value even after the procedure ends. Using Static variables in your program is an excellent way to keep track of flag values even after procedures or functions exit. The only other way to make sure that variables maintain their value after exit from a routine is to place them in the declaration area of the form. The problem with placing them at the form-level declaration is that they now can be altered by routines in other procedures or functions on the same form. Declaring Static variables within the procedures in which they are used follows good programming practice by limiting the scope of the variable.

---

Save the form as guideMARKS.FRM and the project as guideMARKS.VBP, and then run the program. The program opens the guideS5.MDB file, creates a Dynaset-type Recordset of all the records in the Authors data table, and presents the first record on the form. Note that the command button caption says Save guidemark. Click the command button to create a guidemark that points to this record of the collection. The caption changes to Restore guidemark. Now use the arrow buttons on the data control to move to another record on the form. Click the command button. You see that the record pointer has been returned to the first record in the collection. This is because the Recordset guidemark property was reset to the value you stored earlier. Dynasets and ODBC If you are accessing data from an ODBC (Open Database Connectivity) data source, the only Visual Basic data object you can use to update the underlying data table is a Dynaset-type Recordset. You learn more about ODBC connected databases on Day 19, "ODBC Data Access Via the ODBC API."

Limitations of the Dynaset-Type Recordset Data Object

Although the Dynaset is an excellent data object, it has a few drawbacks that must be considered. Chief among these is that Dynasets do not allow you to specify an existing index, and you cannot use the Visual Basic Seek method to quickly locate a single record in the Dynaset. Also, errors can occur when displaying records in a Dynaset if the records in the underlying data table have been altered or deleted by another user. Dynaset Access and Seek Limitations Dynasets cannot make use of Index objects that exist in a database because the Index is built to control the entire data table and not just a subset of the data. Because Dynasets could be subsets of the data table, the Index is useless. Also, because you cannot specify an Index object for a Dynaset, you cannot use the Visual Basic Seek method on a Dynaset.

These are only minor limitations. If you have defined an Index in the underlying table with the Primary flag turned on, the Visual Basic data engine uses the primary key index when creating the Dynaset. This usually puts the Dynaset in optimal order. Even though you cannot use the Seek method on a Dynaset, you can use the FindFirst, FindNext, FindPrevious, and FindLast methods. Even though they are not true index searches, they are fast enough for operations on small- to medium-sized Dynasets. You learn more about Seek, Find, and Move in Day 10, "Creating Database Programs with Visual Basic Code." Dynamic Membership-Related Errors If your program opens a database and creates a Dynaset from an underlying table while another user has also opened the same database and created a Dynaset based on the same underlying data table, it is possible that both users will attempt to edit the same data record. If both users edit the same record and both attempt to save the record back to the underlying table, the second person who attempts to save the record receives a Visual Basic error.

When the second person tries to save the record, Visual Basic discovers that the original record in the underlying data table has been altered. In order to maintain database integrity, Visual Basic does not allow the second person to update the table.

When to Use the Dynaset-Type Recordset Data Object

The Dynaset object should be used in most database programs you write. In most cases, the Visual Basic Dynaset data object is the most effective data access object to use. It offers you a way to create a dynamic, updatable subset of data records in one or more data tables. The Dynaset object is the default object created by the bound data control and is the only updatable data object you can use to access ODBC data sources.

The Dynaset is not a good data object to use when you need to do a great deal of record-oriented processing on large datasets, such as index look-ups on large transaction files. If you have a Visual Basic program that uses Dynasets and is showing slow database performance, look for places where you can limit the size of Dynasets by narrowing the selection criteria.

The Table-Type Recordset Data Object

The Visual Basic Table-type Recordset data object is the data object that gives you access to the physical data table, sometimes referred to as the base table. You can use the Table object to directly open the table defined by Data Manager (or some other database definition tool). The chief advantage of using the Table object is that you can specify search indexes and use the Visual Basic Seek method. Like Dynasets, Tables take a limited amount of local workstation memory.

Table-type Recordset data objects also give you instant information on the state of the data table. This is important in a multiuser environment. As soon as a user adds or deletes a record from the table, all other users who have the data table open as a Visual Basic Table object also see the changes.

Visual Basic Table objects have their drawbacks, too. You cannot use a Select statement to initialize a Table object, and you cannot combine data tables to create unique views of the database when you create Table objects.

You cannot use guidemarks, create Filters, or sort the table. Furthermore, you cannot use the Table data object to access ODBC data sources. Only Dynasets and Snapshots can be used with ODBC data sources.

Strengths of the Table-type Recordset Data Object

The real strength of Table objects is that you can specify Index objects to use when searching for specific records in the table. Table objects also use limited workstation memory and offer instant updates whenever that data in the table changes. Data Pointers and Instant Membership Notification Like Dynasets, Table objects use limited workstation memory because Visual Basic caches pointers to the actual records at the workstation instead of loading all the records into workstation memory. This gives your programs the fastest access speed of all the data objects when you are searching for a single record.

Unlike Dynasets and Snapshots, Table objects are not subsets of the data table. They contain all the records in the table at all times. As soon as a new record is added to the data table, the record is available to the Table object. Also, as soon as a user deletes a record from the table, the Table object is updated to reflect the deletion. Table-Type Recordset Objects, Indexes, and the Seek Method The Visual Basic Table-type Recordset data object enables you to specify an index to apply to the data table. You can use indexes to order the data table for displays and reports and to speed searches using the Seek method.

The following project (TBSEEK.VBP) demonstrates the use of Visual Basic Table-type Recordset objects, indexes, and the Seek method. It opens the Titles table of the guideS5.MDB database and gives you the ability to select one of three indexes. When the index is selected, the program loads the records from the table into a list box. When you click the Search button, you are prompted to enter a search value to use in the Seek method on the table.

Use the information in Table 4.2 to build a new Standard EXE project that demonstrates the use of Visual Basic Table objects, indexes, and the Seek method.

Table 3.2. Controls for the TBSEEK.VBP project.

Refer to Figure 3.6 as a guide for placement and positioning of the controls listed in Table 3.2.

Figure 3.6. Laying out the TbSeek form.

---

NOTE: Because you again create data objects in Visual Basic code in this exercise, you need to load the Microsoft DAO 3.5 Object Library for this project.

---

After you have placed the controls on the form and sized them, you need to place the code from Listing 3.10 in the declaration section of the form. This code declares several variables that you use throughout the form.

Listing 3.10. Declaration code for the TBSEEK.VBP project.

Option Explicit
`
` form-level variables
`
Dim db As Database
Dim rs As Recordset
`
Dim strDBName As String
Dim strRSName As String
Dim strIndex As String
Dim strField As String 

Place the code from Listing 3.11 in the Form_Load event of the form. This code opens the guideS.MDB database and opens the Titles table.

Listing 3.11. Coding the Form_Load routine of TBSEEK.VBP.

Private Sub Form_Load()
    `
    ` set vars
    strDBName = App.Path & "\..\..\Data\guides5.mdb"
    strRSName = "Titles"
    `
    ` open database and table
    Set db = DBEngine.OpenDatabase(strDBName)
    Set rs = db.OpenRecordset(strRSName, dbOpenTable)
    `
End Sub 

Place the procedure shown in Listing 3.12 in the declaration section. This is the procedure that sets the table index and loads the list box in the proper order.

Listing 3.12. Coding the LoadList routine of TBSEEK.VBP.

The LoadList procedure is an example of a way to load a Visual Basic list box with data from a table. The routine first clears out the list box. Then the Index property of the table object is set (based on the user's input) and moves to the first record in the table.

Now the fun starts. The Do While..Loop construct reads each record in the table and creates a single line of text (strLine) that contains each of the fields separated by a single space. Notice that you need to use the CStr() function to convert the numeric fields in the data table (YearPub, ISBN, and Pub_ID) into string values before you can add them to strLine. After the line is built, the strLine is added to the list box using the lstRecordset.AddNew method. After the line is added to the list box, the record pointer is advanced using the rs.MoveNext method. This goes on until there are no more records in the table.

The following three code segments go behind the appropriate command button to set the indexes. They set values for selecting the index, setting the display, and calling the routine to load the list box.

Place this code in the cmdTitle_Click event:

Private Sub cmdTitle_Click()
    `
    ` set for Title index
    `
    strIndex = "Title"
    strField = "Title"
    LoadList
    `
End Sub

Place this code in the cmdISBN_Click event:

Private Sub cmdISBN_Click()
    `
    ` set for ISBN index
    `
    strIndex = "PrimaryKey"
    strField = "ISBN"
    LoadList
    `
End Sub

Place this code in the cmdPublisher_Click event:

Private Sub cmdPublisher_Click()
    `
    ` set for PubID index
    `
    strIndex = "PubID"
    strField = "PubID"
    LoadList
    `
End Sub

The Seek routine shown in Listing 3.13 calls an input box to prompt the user for a search value, performs the seek, and reports the results of the search. The routine first checks to see whether the user has filled the list box by selecting an index. If the list box contains data, the routine calls the Visual Basic InputBox function to get user input, and then invokes the Seek method of the table object. If the record is not found, you see a Seek Failed message. If you entered a record that is on file, you see a Record Found message.

Listing 3.13. Coding the Seek routine for TBSEEK.VBP.

Of course, every project should have an Exit button. Enter the following line for the Exit button:

Private Sub cmdExit_Click()
    `
    ` end program
    `
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    Unload Me
    `
End Sub

When you have completed the coding, save the form as TBSEEK.FRM and the project as TBSEEK.VBP, and then run the program. Click the Title, ISBN, or Publisher buttons to set the index and load the list box. Note that each time you select a different button, the list is loaded in a different order. After the list is loaded, click the Seek button to perform an indexed search on the data table. If you enter a value that is in the index, the program reports the title of the guide in a message box; otherwise, you see an error message. See Figure 3.7 for an example.

Figure 3.7. Testing the TbSeek Demonstration Project.

Limitations of the Table-Type Recordset Data Object

Even though the Visual Basic Table-type Recordset object provides the fastest search speed of any of the data objects, it also has certain drawbacks. You cannot sort a table; you can't use the Table object when accessing ODBC data sources; and you can't use the Visual Basic data control to access a Table object. Tables Cannot Use guidemarks, Sorts, or Filters Unlike Dynasets and Snapshots, Visual Basic Table objects cannot be sorted, filtered, or have guidemarks set. Instead of sorting the data, you can use Index objects to establish the order of the data in the table. If you need to filter the table (usually because it is a large table), you need to create a Dynaset or Snapshot that contains a subset of the data in the table.

Table objects can't use guidemarks, so you can't mark your place in a table, move around, and then return to the location using Visual Basic guidemarks. You can, however, save the table index value instead. The table must have an index declared, and you must know the fields used in the declared index. You can get this information from the Design form of Data Manager, or you can get it at runtime by reading the Index.Name and Index.Fields properties of the Table object. Refer to the section on the Database data object for an example of how to read the Index.Name and Index.Fields properties of a data table. ODBC Data Source Limitations If you plan to do any work with ODBC data sources, you have to forget using the Visual Basic Table object. It does not matter whether the ODBC source is a SQL Server data source or a spreadsheet on your local workstation. You cannot define a Table object to access the data. You must use a Dynaset or Snapshot object for ODBC data requests.

The reason for this limitation is that the ODBC driver gives Visual Basic access to virtually any type of data. There is no requirement that the data source comply with the Visual Basic data engine data table format. Because the Table object is designed specifically to provide direct access to Visual Basic data tables, it can only be used to access a data table that exists as data table in a Microsoft Access database.

When to Use the Table-Type Recordset Data Object

The Visual Basic Table-type Recordset object is the best choice when you need to provide speedy searches of large data tables. As long as you do not need to access ODBC data sources, and you do not need to get a set of data for processing, the Table object is an excellent choice.

If, however, you need to process sets of data instead of single records, the Table object does not work as easily or as quickly as a Dynaset or Snapshot object.

The Snapshot-Type Recordset Data Object

Visual Basic Snapshot-type Recordset objects are almost identical to Dynaset-type Recordsets in behavior and properties. However, there are two major differences between Snapshot objects and Dynaset objects. These two differences are the most important aspects of Snapshots.

• Snapshots are stored entirely in workstation memory.
• Snapshots are read-only and nonupdatable objects.

Instead of reviewing strengths and limitations of the Snapshot data object, let's look at these two properties of Snapshots in depth. Snapshot-Type Recordset Storage You need to consider several things when using Snapshot data objects. For example, unlike Visual Basic Dynasets, Snapshot objects are stored entirely at the workstation. If you create a Snapshot that contains 500 data records, all 500 records are sent from the data table directly to your workstation and loaded into RAM memory. If the workstation does not have enough RAM available, the records are stored in a temporary file on a local disk drive.

Because all the requested records are loaded on the local machine, initial requests for data can take longer with Snapshots than with Dynasets. However, when the data records are retrieved and stored locally, subsequent access to records within the Snapshot object is faster than with the Dynaset object. Also, because all records must be stored locally, you must be careful not to request too large a dataset; you might quickly run out of local RAM or disk space.

Snapshots are static views of the underlying data tables. If you request a set of data records in a Snapshot object, and then someone deletes several records from the underlying data table, the Snapshot dataset does not reflect the changes in the underlying table. The only way you can learn about the changes in the underlying data tables is to create a new Snapshot by making a new request. Snapshot-Type Recordsets Are Read-Only Data Objects Visual Basic Snapshots are read-only data objects. You cannot use Snapshots to update data tables. You can only use them to view data. This is because Snapshots are actually a copy of the data records created at your local workstation.

The project in Listing 3.14 illustrates the static aspect of Snapshot data objects compared to the dynamic aspect of Dynaset and Table data objects. Start a new Standard EXE project. There are no controls in this project, so be sure to add the Microsoft DAO 3.5 Object Library to access the data objects.

The entire source code is listed. Enter it into a single form and save it as SNAPSHOTS.FRM and SNAPSHOTS.VBP.

Listing 3.14. Comparing Snapshot-type and Dynaset-type Recordsets.

Although there is not a lot of code in this example, there are a few things worth pointing out. First, you see extensive use of the With..End With construct in Listing 3.14. This construct was introduced in Visual Basic 4.0 and is very useful when working with Visual Basic objects. Using the With..End With construct is faster than naming the same objects several times in code.

Also, notice the use of the GetRows method of the Recordset. This method fills a variant data variable with the contents of one or more records from the Recordset. This is a very efficient way to read several records into memory without using the slower For..Next loops.

When you run the SNAPSHOTS.VBP program, you see three record count reports. The first report occurs right after the data objects are created. The second count report occurs after a record has been removed from the Dynaset object. The last count report occurs after the record has been restored to the Dynaset object. Note that both the Table and the Dynaset objects reflect the changes in the data table, but the Snapshot does not (see Figure 3.8).

Figure 3.8. Comparing Dynasets, Snapshots, and Tables.

When to Use the Snapshot Data Object

Visual Basic Snapshot-type Recordset objects work best if you have a small set of data that you need to access frequently. For example, if you have a list of valid input values for a particular field stored in a control table, you can load these valid values into a Snapshot and refer to that dataset each time you need to verify user input.

If the dataset is not too large, Snapshots are very good for use in creating calculated reports or graphic displays. It is usually a good idea to create a static dataset for use in calculating reports. This way, any changes in the dataset that might occur in a multiuser environment from the time you start the report to the time you end it will not confuse any calculations done by the report.

---

TIP: It's a good idea to keep your Snapshots to less then 64KB in size. You can estimate the eventual size of your Snapshots by calculating the number of bytes in an average data record and estimating the average number of records you can expect in your Snapshot. You can refer to Day 2, "Creating Databases," for information on the size of Visual Basic data types.

---

The Database Data Objects

The Database object of a Visual Basic data control allows you access to all the properties and methods associated with the database underlying the data control. By using the related data objects, TableDefs, Fields, and Indexes, you can get information about all the tables in the database, all the indexes in the database, and all the fields in each table. Also, you can get additional information about the field types and index parameters.

The Database data object is most useful when you are developing generic database routines. Because the Database object gives you access to all the field names and properties, you can use this information to write generic data table display and update routines instead of having to write routines that have hard-coded field names and data types. TableDefs objects are covered in more detail on Day 10, "Creating Database Programs with Visual Basic Code". For now, though, let's write a short routine that lists all the tables, fields, and indexes in the guideS5.MDB database.

First, start a new Standard EXE project in Visual Basic 5 and load the Microsoft Jet DAO 3.5 Object Library. Use the information in Table 3.3 to set the form property settings and place the data control on the form.

Table 3.3. The controls for the 04ABC1.MAK project.

Be sure to place the data control at the very bottom of the form. It is only there to give you access to the various database properties that you print on the form itself. Enter the program code in Listing 3.15 in the Form_Activate event.

Listing 3.15. Listing Database objects.

After you enter the code, save the form as DATABASE.FRM and the project as DATABASE.VBP, and then run the program. You see a list on the screen showing the table name, a list of all the fields in the table, and a dialog box. Click the dialog box to continue to the next table. After clicking OK through the table listing, you see a list of each index defined for each table, which you can also click through one at a time. Your two screens should look something like the one in Figure 3.9 for tables and the one in Figure 3.10 for indexes.

Figure 3.9. List of fields in the Publishers table in guideS5.MDB.

---

NOTE: As you click through the database tables, you see several tables that start with "MSYS." These are system tables used by the Microsoft Jet database engine and are not used for data storage or retrieval. You should also notice that each Index object consists of a unique name and one or more fields (displayed in brackets). You do not see a data table associated with the index because the Microsoft Jet engine does not store that information in a manner you can easily see (it's actually in one of those "MSYS" tables!).

---

Figure 3.10. List of indexes for the Titles table in guideS5.MDB.

Summary

In today's lesson, you learned that there are three main types of Visual Basic Recordset data objects:

• Table-type objects: These are used when you have a large dataset and need to do frequent searches to locate a single record. You can use the Visual Basic Seek method and use Visual Basic Indexes with the Table object.
• Dynaset-type objects: These are used in most cases when you need read and write access to datasets. The Dynaset uses little workstation memory and allows you to create virtual tables by combining fields from different tables in the same database. The Dynaset is the only data object that allows you to read and write to ODBC data sources.
• Snapshot-type objects: These are used when you need fast read-only access to datasets. Snapshot objects are stored in workstation memory, so they should be kept small. Snapshots are good for storing validation lists at the workstation or for small reports.

You also learned about another data object--the Database object. You can use the Database object to get a list of tables in the database, a list of indexes associated with the tables, and a list of fields in each of the tables.

Quiz

1. Are Visual Basic Database objects dataset-oriented or record-oriented?

2. What is the most common Visual Basic data object?

3. Do Dynasets use a relatively large amount or small amount of workstation RAM? Why?

4. What are the weaknesses of using a Dynaset object?

5. What are the main advantages of using the Table data object?

6. Do you use the Refresh method with the Table data object?

7. Can you open a Table data object by setting the properties of a data control?

8. What is the difference between a Snapshot and a Dynaset data object?

9. Which data object do you use to extract table and field names from a database definition?

Exercises

1. What type of Recordset data object would you use--Dynaset, Table, or Snapshot--to create an attachment to an ODBC data source that you would like to update periodically? Why?
Write the code to open this type of data object. Assume that the database name is C:\DATA\ACCTPAY.MDB, with your desired table named Vendors.

2. Given the same data source as in Exercise 1, write the code to open a data object to be used in the generation of a report. (Assume the RAM memory is adequate on the machine running the program.)

3. Given the same data source as in Exercise 1, write the code that opens the data object so that you can access the data often in a multiuser environment to search for single records.