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
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."
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:
ReadLoop:
If EndOf File
Goto EndLoop
Else
Read Record
If Record.SalesRegion = `Northeast' Then
Price=Price*1.10
Write Record
End If
EndIf
Goto ReadLoop
EndLoop:
End Program
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:
UPDATE Inventory SET Price=Price*1.10 WHERE Inventory.SalesRegion = `Northeast'
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 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.
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 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.
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.
Private Sub Form_Load()
`
` creating Dynaset-type recordsets
`
Dim db As Database ` the database object
Dim rs As Recordset ` the recordset object
`
End Sub
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 book, 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.
Private Sub Form_Load()
`
` creating Dynaset-type recordsets
`
Dim db As Database ` the database object
Dim rs As Recordset ` the recordset object
`
` create local variables
Dim strDBName As String
Dim strRSName As String
`
` initialize the variables
strDBName = App.Path & "\..\data\books5.mdb"
strRSName = "Titles"
`
End Sub
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.
Private Sub Form_Load()
`
` creating dynaset-type recordsets
`
Dim db As Database ` the database object
Dim rs As Recordset ` the recordset object
`
` create local variables
Dim strDBName As String
Dim strRSName As String
`
` initialize the variables
strDBName = App.Path & "\..\data\books5.mdb"
strRSName = "Titles"
`
` create the objects
Set db = DBEngine.OpenDatabase(strDBName)
Set rs = db.OpenRecordset(strRSName, dbOpenDynaset)
`
End Sub
There are two added lines in Listing 3.3. The first added line opens the BOOKS5.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.
Private Sub Form_Load()
`
` creating dynaset-type recordsets
`
Dim db As Database ` the database object
Dim rs As Recordset ` the recordset object
`
` create local variables
Dim strDBName As String
Dim strRSName As String
Dim intRecs As Integer
`
` initialize the variables
strDBName = App.Path & "\..\data\books5.mdb"
strRSName = "Titles"
`
` create the objects
Set db = DBEngine.OpenDatabase(strDBName)
Set rs = db.OpenRecordset(strRSName, dbOpenDynaset)
`
` count the records in the collection
rs.MoveLast ` move to end of list to force a count
intRecs = rs.RecordCount ` get count
MsgBox strRSName & " :" & CStr(intRecs), vbInformation, "Total Records in Set"
`
End Sub
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.
Private Sub Form_Load()
`
` creating dynaset-type recordsets
`
Dim db As Database ` the database object
Dim rs As Recordset ` the recordset object
Dim rs2 As Recordset ` <<< add another recordset object
`
` create local variables
Dim strDBName As String
Dim strRSName As String
Dim intRecs As Integer
Dim strFilter As String ` <<< add filter
`
` initialize the variables
strDBName = App.Path & "\..\data\books5.mdb"
strRSName = "Titles"
strFilter = "YearPub>1990" ` <<< set filter
`
` create the objects
Set db = DBEngine.OpenDatabase(strDBName)
Set rs = db.OpenRecordset(strRSName, dbOpenDynaset)
`
` count the records in the collection
rs.MoveLast ` move to end of list to force a count
intRecs = rs.RecordCount ` get count
MsgBox strRSName & " :" & CStr(intRecs), vbInformation, "Total Records in Set"
`
End Sub
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.
Private Sub Form_Load()
`
` creating dynaset-type recordsets
`
Dim db As Database ` the database object
Dim rs As Recordset ` the recordset object
Dim rs2 As Recordset ` another recordset
`
` create local variables
Dim strDBName As String
Dim strRSName As String
Dim intRecs As Integer
Dim strFilter As String
`
` initialize the variables
strDBName = App.Path & "\..\data\books5.mdb"
strRSName = "Titles"
strFilter = "YearPub>1990"
`
` create the objects
Set db = DBEngine.OpenDatabase(strDBName)
Set rs = db.OpenRecordset(strRSName, dbOpenDynaset)
`
` count the records in the collection
rs.MoveLast ` move to end of list to force a count
intRecs = rs.RecordCount ` get count
MsgBox strRSName & " :" & CStr(intRecs), vbInformation, "Total Records in Set"
`
` create filtered collection
rs.Filter = strFilter
Set rs2 = rs.OpenRecordset
`
End Sub
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.
Private Sub Form_Load()
`
` creating dynaset-type recordsets
`
Dim db As Database ` the database object
Dim rs As Recordset ` the recordset object
Dim rs2 As Recordset ` another recordset
Dim rs3 As Recordset ` for cloning
`
` create local variables
Dim strDBName As String
Dim strRSName As String
Dim intRecs As Integer
Dim strFilter As String
`
` initialize the variables
strDBName = App.Path & "\..\..\data\books5.mdb"
strRSName = "Titles"
strFilter = "YearPub>1990"
`
` create the objects
Set db = DBEngine.OpenDatabase(strDBName)
Set rs = db.OpenRecordset(strRSName, dbOpenDynaset)
`
` count the records in the collection
rs.MoveLast ` move to end of list to force a count
intRecs = rs.RecordCount ` get count
MsgBox strRSName & " :" & CStr(intRecs), vbInformation, "Total Records in Set"
`
` create filtered collection
rs.Filter = strFilter
Set rs2 = rs.OpenRecordset
`
` count the records in the collection
rs2.MoveLast ` move to end of list to force a count
intRecs = rs2.RecordCount ` get count
MsgBox strFilter & " :" & CStr(intRecs), vbInformation, "Total Records in Set"
` exit program
End
`
End Sub
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.
Private Sub Form_Load()
`
` creating dynaset-type recordsets
`
Dim db As Database ` the database object
Dim rs As Recordset ` the recordset object
Dim rs2 As Recordset ` another recordset
Dim rs3 As Recordset ` for cloning
`
` create local variables
Dim strDBName As String
Dim strRSName As String
Dim intRecs As Integer
Dim strFilter As String
`
` initialize the variables
strDBName = App.Path & "\..\data\books5.mdb"
strRSName = "Titles"
strFilter = "YearPub>1990"
`
` create the objects
Set db = DBEngine.OpenDatabase(strDBName)
Set rs = db.OpenRecordset(strRSName, dbOpenDynaset)
`
` count the records in the collection
rs.MoveLast ` move to end of list to force a count
intRecs = rs.RecordCount ` get count
MsgBox strRSName & " :" & CStr(intRecs), vbInformation, "Total Records in Set"
`
` create filtered collection
rs.Filter = strFilter
Set rs2 = rs.OpenRecordset
`
` count the records in the collection
rs2.MoveLast ` move to end of list to force a count
intRecs = rs2.RecordCount ` get count
MsgBox strFilter & " :" & CStr(intRecs), vbInformation, "Total Records in Set"
`
` clone the recordset
Set rs3 = rs.Clone ` clone it
rs3.MoveLast ` move to end
intRecs = rs3.RecordCount ` get count
MsgBox "Cloned Recordset: " & CStr(intRecs), vbInformation, "Total Records in Set"
`
End Sub
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 Bookmarks, Filters, and Sorts Dynaset-type
Recordsets can use the Bookmark, 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 Bookmarks do. They remember where you were.
When you search for a record in the dataset using one of the Find methods, you should set Bookmarks 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 bookmark before starting the search, you can reset the Visual Basic Bookmark and return the user to the place from which the search started.
Let's build a quick project to demonstrate the use of Bookmarks. 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 BOOKMARKS.FRM.
| Control | Property | Setting |
| VB.Form | Name | FrmBookMarks |
| Caption | "Bookmark Demonstration" | |
| ClientHeight | 1320 | |
| ClientLeft | 60 | |
| ClientTop | 345 | |
| ClientWidth | 4605 | |
| StartUpPosition | 2 `CenterScreen | |
| VB.CommandButton | Name | CmdSaveBookmark |
| Caption | "&Save Bookmark" | |
| Height | 300 | |
| Left | 2760 | |
| Top | 180 | |
| Width | 1695 | |
| VB.Data | Name | DtaBookMarks |
| Align | 2 `Align Bottom | |
| Caption | "Data1" | |
| Connect | "Access" | |
| DatabaseName | C:\TYSDBVB5\SOURCE\DATA\BOOKS5.MDB | |
| RecordsetType | 1 `Dynaset | |
| RecordSource | "Authors" | |
| VB.TextBox | Name | TxtName |
| DataField | "Name" | |
| DataSource | "dtaBookMarks" | |
| Height | 300 | |
| Left | 1440 | |
| Top | 600 | |
| Width | 3015 | |
| VB.TextBox | Name | TxtAUID |
| DataField | "AUID" | |
| DataSource | "dtaBookMarks" | |
| Height | 300 | |
| Left | 1440 | |
| Top | 180 | |
| Width | 1215 | |
| VB.Label | Name | LblName |
| BorderStyle | 1 `Fixed Single | |
| Caption | "Author Name" | |
| Height | 300 | |
| Left | 120 | |
| Top | 600 | |
| Width | 1215 | |
| VB.Label | Name | LblAUID |
| BorderStyle | 1 `Fixed Single | |
| Caption | "Author ID" | |
| Height | 300 | |
| Left | 120 | |
| Top | 180 | |
| Width | 1215 |
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 Bookmark, or restores the previous place
in the table by reading (and updating) the Bookmark.
Private Sub cmdSaveBookmark_Click()
`
` show how bookmarks work
`
Static blnFlag As Boolean
Static strBookmark As String
`
If blnFlag = False Then
`
` flip flag and set caption
blnFlag = True
cmdSaveBookmark.Caption = "&Restore Bookmark"
`
` save bookmark for later
strBookmark = dtaBookMarks.Recordset.Bookmark
MsgBox "Bookmark Saved", vbInformation
Else
`
` flip flag and set caption
blnFlag = False
cmdSaveBookmark.Caption = "&Save Bookmark"
`
` restore saved bookmark
dtaBookMarks.Recordset.Bookmark = strBookmark
End If
`
End Sub
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 BOOKMARKS.FRM and the project as BOOKMARKS.VBP, and then run the program. The program opens the BOOKS5.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 Bookmark. Click the command button to create a Bookmark that points to this record of the collection. The caption changes to Restore Bookmark. 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 Bookmark 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."
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.
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 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 Bookmarks, 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.
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 BOOKS5.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.
| Control | Property | Setting |
| VB.Form | Name | frmTbSeek |
| Caption | "Table Index and Seek Demonstration" | |
| ClientHeight | 2895 | |
| ClientLeft | 60 | |
| ClientTop | 345 | |
| ClientWidth | 6540 | |
| StartUpPosition | 3 `Windows Default | |
| VB.CommandButton | Name | cmdExit |
| Caption | "E&xit" | |
| Height | 300 | |
| Left | 5220 | |
| Top | 2520 | |
| Width | 1200 | |
| VB.CommandButton | Name | cmdSeek |
| Caption | "&Seek" | |
| Height | 300 | |
| Left | 3900 | |
| Top | 2520 | |
| Width | 1200 | |
| VB.CommandButton | Name | cmdPublisher |
| Caption | "&Publisher" | |
| Height | 300 | |
| Left | 2640 | |
| Top | 2520 | |
| Width | 1200 | |
| VB.CommandButton | Name | CmdISBN |
| Caption | "&ISBN" | |
| Height | 300 | |
| Left | 1380 | |
| Top | 2520 | |
| Width | 1200 | |
| VB.CommandButton | Name | CmdTitle |
| Caption | "&Title" | |
| Height | 300 | |
| Left | 120 | |
| Top | 2520 | |
| Width | 1200 | |
| VB.ListBox | Name | LstRecordset |
| Height | 2040 | |
| Left | 120 | |
| Top | 360 | |
| Width | 6255 | |
| VB.Label | Name | LblIndex |
| BorderStyle | 1 `Fixed Single | |
| Height | 255 | |
| Left | 120 | |
| Top | 60 | |
| Width | 6255 |
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.
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 BOOKS.MDB database and opens the Titles table.
Private Sub Form_Load()
`
` set vars
strDBName = App.Path & "\..\..\Data\Books5.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.
Public Sub LoadList()
`
` load data collection into list box
`
Dim strLine As String
lstRecordset.Clear
`
rs.Index = strIndex
rs.MoveFirst
`
On Error Resume Next ` in case we get null fields
`
Do While Not rs.EOF
strLine = rs.Fields("Title")
strLine = strLine & " | " & CStr(rs.Fields("YearPub"))
strLine = strLine & " | " & CStr(rs.Fields("ISBN"))
strLine = strLine & " | " & CStr(rs.Fields("PubID"))
lstRecordset.AddItem strLine
rs.MoveNext
Loop
`
lblIndex.Caption = "Titles Table - Indexed by [" & strField & "]"
`
End Sub
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.
Private Sub cmdSeek_Click()
`
` perform table seek
`
Dim strSeek As String
`
If lstRecordset.ListCount = 0 Then
MsgBox "Select an Index First!", vbExclamation, "Missing Index"
Else
strSeek = InputBox("Enter a Seek value for " & strField)
rs.Seek "=", strSeek
If rs.NoMatch = True Then
MsgBox strSeek & " not in table", vbCritical, "Seek Failed"
Else
MsgBox rs.Fields("Title"), vbInformation, "Record Found"
End If
End If
`
End Sub
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 book
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.
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 Bookmarks, Sorts, or Filters Unlike Dynasets and Snapshots, Visual Basic Table objects cannot be sorted, filtered, or have Bookmarks 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 Bookmarks, so you can't mark your place in a table, move around, and then return to the location using Visual Basic Bookmarks. 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.
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.
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.
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.
Option Explicit
`
` form level variables
`
Dim db As Database
Dim rsDynaset As Recordset
Dim rsSnapshot As Recordset
Dim rsTable As Recordset
`
Dim strDBName As String
Dim strRSName As String
Dim varRecords As Variant
Dim intReturned As Integer
Dim intColumns As Integer
Private Sub Form_Activate()
`
` main control routine
`
strDBName = App.Path & "\..\..\Data\books5.mdb"
strRSName = "Titles"
OpenFiles
`
` show title
Me.Cls
Me.Print "Comparing Recordset Types (Dynaset, Snapshot, & Table)"
Me.Print
`
` show first compare
Me.Print ">First Pass"
CountRecs rsDynaset, "Dynaset"
CountRecs rsSnapshot, "Snapshot"
CountRecs rsTable, "Table"
Me.Print
`
` save rec, delete it, count
SaveDynasetRec
DeleteDynasetRec
Me.Print ">After Dynaset Delete"
CountRecs rsDynaset, "Dynaset"
CountRecs rsSnapshot, "Snapshot"
CountRecs rsTable, "Table"
Me.Print
`
` restore rec and count
RestoreDynasetRec
Me.Print ">After Dynaset Restore"
CountRecs rsDynaset, "Dynaset"
CountRecs rsSnapshot, "Snapshot"
CountRecs rsTable, "Table"
Me.Print
`
End Sub
Public Sub OpenFiles()
`
` open database and
` populate objects
`
Set db = DBEngine.OpenDatabase(strDBName)
`
With db
Set rsDynaset = .OpenRecordset(strRSName, dbOpenDynaset)
Set rsSnapshot = .OpenRecordset(strRSName, dbOpenSnapshot)
Set rsTable = .OpenRecordset(strRSName, dbOpenTable)
End With
`
End Sub
Public Sub CountRecs(rsTemp As Recordset, strType As String)
`
` count records in the object
`
Dim intCount As Integer
`
With rsTemp
.MoveFirst
.MoveLast
intCount = .RecordCount
End With
`
Me.Print vbTab, "Total for " & strType & ":"; intCount
`
End Sub
Public Sub SaveDynasetRec()
`
` save a single record
`
With rsDynaset
.MoveFirst
varRecords = .GetRows(1)
End With
`
End Sub
Public Sub DeleteDynasetRec()
`
` remove first record in the collection
`
With rsDynaset
.MoveFirst
.Delete
End With
`
End Sub
Public Sub RestoreDynasetRec()
`
` add saved rec back in
`
Dim intLoop As Integer
`
With rsDynaset
.AddNew
For intLoop = 0 To UBound(varRecords, 1)
.Fields(intLoop).Value = varRecords(intLoop, 0)
Next
.Update
End With
`
End Sub
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.
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 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 BOOKS5.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.
| Control | Property | Setting |
| Form | Caption | Database Objects Demo |
| WindowState | Maximize | |
| DataControl | Alignment | Align Bottom |
| DatabaseName | "BOOKS5.MDB" | |
| RecordSource | Authors |
Private Sub Form_Activate()
`
` show high-level database objects
`
Dim tb As TableDef
Dim fl As Field
Dim ix As Index
Data1.DatabaseName = App.Path & "\..\..\data\books5.mdb"
Data1.Refresh
`
For Each tb In Data1.Database.TableDefs
Me.Print "Table Info:"
Print " "; tb.Name
For Each fl In tb.Fields
Print " -"; fl.Name
Next
MsgBox "Press OK to continue"
Me.Cls
Next
`
On Error Resume Next ` in case there's no index
`
For Each tb In Data1.Database.TableDefs
Me.Print "Index Info:"
Print " "; tb.Name
For Each ix In tb.Indexes
Print " -"; ix.Name;
Print "[";
Print ix.Fields;
Print "]"
Next
MsgBox "Press OK to continue"
Me.Cls
Next
`
End Sub
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 BOOKS5.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 BOOKS5.MDB.
In today's lesson, you learned that there are three main types of Visual Basic Recordset data objects:
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.
