In the previous day's lesson, you learned how to use the Data Manager to create databases, data tables, and indexes. Today you will learn about the programmatic data objects of Visual Basic 4.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 will create small Visual Basic programs that illustrate the special features of each of the data objects.
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.
These are the three main data objects used in Visual Basic programs:
Any one of these 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 will learn how these three data objects differ and when it is best to use these objects in your programs.
You'll also learn about two additional data objects today:
The Database object and the Recordset object are two special Visual Basic data objects. You can use the Database object to get information about the connected database. The Recordset object can be used to get information about the selected data table. These objects are runtime only objects that exist as subsets of a Visual Basic data control. In this chapter, you will learn about the general properties and behaviors of the Database and Recordset objects of the data control and how you can use them in your programs.
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 will be better able to 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. Stuctured Query Language (SQL) databases are data set-oriented systems. Understanding the difference between record-oriented processing and data set-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
Price=Price*1.10
Write Record
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 data set-oriented database.
UPDATE Inventory SET Price=Price*1.10
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). As you can see, in data set-oriented databases, you create a single statement that selects only the records you need to perform a database operation. After you identify the data set, you apply the operation to all records in the set. In data set systems, indexes are used to maintain database integrity more than to speed the location of specific records.
Visual Basic database objects are data set oriented. Visual Basic programs generally perform better when data operations are done with a data set 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 object is very good at performing record-oriented processing. The Visual Basic Dynaset and Snapshot 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 data set you create affects the speed at which Visual Basic programs operate. As a data table grows, your program's processing speed can deteriorate. In heavy transaction-oriented applications, such as accounting systems, a data set 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 data sets 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 data sets as small is 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 data set: The smaller the data set, the faster your program can process the data. Visual Basic data objects give you the power to create data sets that are the proper size for your needs.
The Visual Basic Dynaset 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. You can also create a Dynaset by using the CreateDynaset program statement.
When you create a Visual Basic Dynaset, 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 memory 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 data set.
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 are more detailed listings of the strengths of the Dynaset data object.
Visual Basic Dynasets use relatively little workstation memory, even for large data sets. When you create a Dynaset, Visual Basic selects the records you requested, creates temporary index keys to each of these records, and then 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 the diagram in Figure 4.1.
Dynasets contain keys that point to the actual records.
NOTE: The actual data request engine used by Visual Basic is called the Microsoft Jet, or Joint Engine Technology, data engine. In pure SQL systems all requests for data result in a set of data records. Data requests to the Microsoft Jet result in a set of keys that point to the data records. By returning keys instead of data records, the Microsoft Jet 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—in a temporary file on a local disk drive. As you scroll through the data set, 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 will retrieve 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 data set will be retrieved by Visual Basic. Visual Basic also caches records at the workstation to reduce requests to the physical data table to speed performance.
If the Dynaset is very large, it is possible to end up with a key set that is so large that it requires more RAM and temporary disk space than the local machine can handle. In that case, you will receive an error message from Visual Basic. For this reason, it is important that you use care in creating your criteria for populating the data set. The smaller the data set, the smaller the key set.
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 will 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, 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 will report an error.
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.
Often in Visual Basic programs, you need to create a secondary data set 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. Clone Dynasets take up slightly less room than the original Dynaset.
Let's put together a short code sample that explores Dynasets. You'll do this all in Visual Basic code, too, instead of using the Visual Basic data control.
First start a new Visual Basic project. Double-click on the form to open the code window to the Form_Load event. You will 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 Dynaset object. Listing 4.1 shows how you create the objects in Visual Basic code.
Listing 4.1. Creating a database object and a Dynaset object.
Sub Form_Load () ' ' create data object variables Dim dat As Database ' create a database object Dim dyn1 As Dynaset 'create a dynaset 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, first you'll create two variables that correspond to the DatabaseName and Recordsource properties of the Visual Basic data control. The code sample in Listing 4.2 shows how it is done.
TIP: The code sample Listing 4.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 will 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 will always point to the correct drive and directory for the required file.
Listing 4.2. Declaring database and data table variables.
Private Sub Form_Load()
'
' create data object variables
Dim dat As Database ' create a database object
Dim dyn1 As Dynaset 'create a dynaset object
'
' create standard variables
Dim cDbName As String
Dim cRecSource As String
'
' initialize variables
cDbName = App.Path + "\books.mdb"
cRecSource = "Titles"
End Sub
TIP: Notice that you created two string variables and both variable names start with the letter c. This c stands for character type. The first letter 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 DBPROJ01.FRM and save the project as DBPROJ01.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. The code in Listing 4.3 shows how to do this using Visual Basic code.
Listing 4.3. Opening the database and creating the Dynaset.
Private Sub Form_Load()
'
' create data object variables
Dim dat As Database ' create a database object
Dim dyn1 As Dynaset 'create a dynaset object
'
' create standard variables
Dim cDbName As String
Dim cRecSource As String
'
' initialize variables
cDbName = App.Path + "\books.mdb"
cRecSource = "Titles"
'
' set values
Set dat = OpenDatabase(cDbName) ' open the database
Set dyn1 = dat.CreateDynaset(cRecSource) ' create the dynaset
End Sub
There are two added lines in Listing 4.3. The first added line opens the BOOKS.MDB database and sets the Visual Basic database object dat to point to the database. Now you can use the dat data object to represent the open database in all other Visual Basic code in this program. The second line creates a Dynaset that contains the records in the Titles table. The Visual Basic dyn1 object is set to point to this set of records. Notice that the CreateDynaset method is applied to the datdatabase object.
The code in Listing 4.3 is all that you need to open an existing Microsoft Access database and create a Dynaset 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 Dynaset method to move the record pointer to the last record in the Dynaset. This forces Visual Basic to touch every record in the Dynaset, 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 Dynaset. When you have all that, you display a Visual Basic message box that tells you how many records are in the Dynaset. Listing 4.4 contains the code to add.
Listing 4.4. Counting the records in a Dynaset.
Sub Form_Load () ' ' create data object variables Dim dat As Database ' create a database object Dim dyn1 As Dynaset 'create a dynaset object ' ' create standard variables Dim cDbName As String Dim cRecSource as String Dim nRecs as Integer ' ' initialize variables cDbName = "c:\abc\examples\ver11\access\biblio.mdb" cRecSource ="Titles" ' ' set values Set dat = OpenDatabase(cDbName) ' open the database Set dyn1 = dat.CreateDynaset(cRecSource) ' create the dynaset dyn1.MoveLast ' move to end of set to force count nRecs = dyn1.RecordCount ' get count MsgBox cSelect + " :" + Str$(nRecs), 0, "Total Records in Set" ' ' exit program End End Sub
Save the form (DBPROJ01.FRM) and project (DBPROJ01.VBP) again and run the program. You'll see a message box telling you that there are 50 records in the Dynaset.
You can use the CreateDynaset command on an existing Dynaset to create a smaller subset of the data. This is often done when the user is allowed to create a record selection criterion, and then if the data set returned is too large, the user is allowed to further qualify the search by creating additional criteria to apply to the data set.
Let's modify DBPROJ01.VBP to create a smaller Dynaset from the existing Dynaset. You'll need to create a new Dynaset object and a new variable called cFilter that will hold the criteria for selecting records. The code in Listing 4.5 shows how to add the object and variable to the existing DBPROJ01.VBP project.
Listing 4.5. Adding a new Dynaset object and string variable.
Private Sub Form_Load()
'
' create data object variables
Dim dat As Database ' create a database object
Dim dyn1 As dynaset ' create a dynaset object
Dim dyn2 As dynaset ' create dynaset object
'
' create standard variables
Dim cDbName As String
Dim cRecSource As String
Dim nRecs As Integer
Dim cFilter As String
'
' initialize variables
cDbName = App.Path + "\books.mdb"
cRecSource = "Titles"
cFilter = "[Year Published]>1990"
'
' set values
Set dat = OpenDatabase(cDbName) ' open the database
Set dyn1 = dat.CreateDynaset(cRecSource) ' create the dynaset
'
dyn1.MoveLast ' move to end of set to force count
nRecs = dyn1.RecordCount ' get count
MsgBox cRecSource + " :" + Str$(nRecs), 0, "Total Records in Set"
'
' exit program
End
'
End Sub
Now that you have the object and the variable, you can add code that will create a new Dynaset. First you set the Filter property of the existing Dynaset using the variable you just created. Then you create the new Dynaset from the old one. See the last two lines of the code in Listing 4.6.
Listing 4.6. Using the Filter property to create a Dynaset.
Private Sub Form_Load()
'
' create data object variables
Dim dat As Database ' create a database object
Dim dyn1 As dynaset ' create a dynaset object
Dim dyn2 As dynaset ' create dynaset object
'
' create standard variables
Dim cDbName As String
Dim cRecSource As String
Dim nRecs As Integer
Dim cFilter As String
'
' initialize variables
cDbName = App.Path + "\books.mdb"
cRecSource = "Titles"
cFilter = "[Year Published]>1990"
'
' set values
Set dat = OpenDatabase(cDbName) ' open the database
Set dyn1 = dat.CreateDynaset(cRecSource) ' create the dynaset
'
dyn1.MoveLast ' move to end of set to force count
nRecs = dyn1.RecordCount ' get count
MsgBox cRecSource + " :" + Str$(nRecs), 0, "Total Records in Set"
'
' create subset of dynaset and count records
dyn1.Filter = cFilter
Set dyn2 = dyn1.CreateDynaset()
'
' exit program
End
'
End Sub
Now that you've created the new Dynaset 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 Dynaset, get the RecordCount, and show it in a message box. Listing 4.7 shows the completed program.
Listing 4.7. Displaying the record count of the filtered Dynaset.
Private Sub Form_Load()
'
' create data object variables
Dim dat As Database ' create a database object
Dim dyn1 As dynaset ' create a dynaset object
Dim dyn2 As dynaset ' create dynaset object
'
' create standard variables
Dim cDbName As String
Dim cRecSource As String
Dim nRecs As Integer
Dim cFilter As String
'
' initialize variables
cDbName = App.Path + "\books.mdb"
cRecSource = "Titles"
cFilter = "[Year Published]>1990"
'
' set values
Set dat = OpenDatabase(cDbName) ' open the database
Set dyn1 = dat.CreateDynaset(cRecSource) ' create the dynaset
'
dyn1.MoveLast ' move to end of set to force count
nRecs = dyn1.RecordCount ' get count
MsgBox cRecSource + " :" + Str$(nRecs), 0, "Total Records in Set"
'
' create subset of dynaset and count records
dyn1.Filter = cFilter
Set dyn2 = dyn1.CreateDynaset()
dyn2.MoveLast ' move to end of set to force count
nRecs = dyn2.RecordCount ' get count
MsgBox cFilter + " :" + Str$(nRecs), 0, "Total Records in Set"
'
' exit program
End
'
End Sub
Save and run the code to see the results. Notice that the first record count (the full data set) is larger than the second record count (the filtered data set). It is also important to notice that the second Dynaset object was created from the first Dynaset object. This a very powerful feature of Visual Basic. When you want to get a smaller data set, you don't have to re-load the data from the database, you can use an existing Dynaset as the source for a new data set.
Now let's make one more series of changes to DBPORJ01.VBP that will illustrate the Clone method for Dynasets. Cloning a Dynaset makes a duplicate of the set. Add another data object (dyn3), and add the clone Dynaset program code in Listing 4.8.
Listing 4.8. Cloning a new Dynaset.
Private Sub Form_Load()
'
' create data object variables
Dim dat As Database ' create a database object
Dim dyn1 As dynaset ' create a dynaset object
Dim dyn2 As dynaset ' create dynaset object
Dim dyn3 As dynaset ' create dynaset object
'
' create standard variables
Dim cDbName As String
Dim cRecSource As String
Dim nRecs As Integer
Dim cFilter As String
'
' initialize variables
cDbName = App.Path + "\books.mdb"
cRecSource = "Titles"
cFilter = "[Year Published]>1990"
'
' set values
Set dat = OpenDatabase(cDbName) ' open the database
Set dyn1 = dat.CreateDynaset(cRecSource) ' create the dynaset
'
dyn1.MoveLast ' move to end of set to force count
nRecs = dyn1.RecordCount ' get count
MsgBox cRecSource + " :" + Str$(nRecs), 0, "Total Records in Set"
'
' create subset of dynaset and count records
dyn1.Filter = cFilter
Set dyn2 = dyn1.CreateDynaset()
dyn2.MoveLast ' move to end of set to force count
nRecs = dyn2.RecordCount ' get count
MsgBox cFilter + " :" + Str$(nRecs), 0, "Total Records in Set"
'
' clone a dynaset
Set dyn3 = dyn1.Clone()
dyn3.MoveLast ' move to end of set to force count
nRecs = dyn3.RecordCount ' get count
MsgBox "Cloned Dynaset :" + Str$(nRecs), 0, "Total Records in Set"
'
' exit program
End
'
End Sub
Notice that all you have to do to Clone a Dynaset is to use the Clone method to load a new Dynaset object variable. When you run the program this time, you will see that the first Dynaset that is created contains 50 records. The second Dynaset—created by setting the Filter property of the first Dynaset—contains only 18 records. Notice also that the Dynaset created using the Clone method contains the same number of records as its parent.
Dynasets can use Bookmarks, Filters, and Sorts. These properties of a Dynaset can be used to reorder data for display (Sort) or create a subset of the Dynaset (Filter). Using the Visual Basic Find method on a Dynaset forces Visual Basic to start at the first record in the Dynaset and read each one until a match is found. If no match is found when Visual Basic reaches the end of the Dynaset, the record pointer is simply left at the end of the Dynaset. If you want to get back to the record you started on, you'll need to remember where you started from. That's what Visual Basic Bookmarks do. They remember where you were.
When you search for a record in the data set using one of the Find methods, you should set bookmarks before your search to remember where you started from. This is especially handy if your Find criteria result in a null record. Then, instead of leaving the user at some new (unwanted) location in the data set, you can recall 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 4.1 to create a small form with a data control, two bound input controls, two label controls, and a single command button.
Table 4.1. Controls for BKMARK01.FRM.
| Control | Property | Setting |
|---|---|---|
| Form1 | Caption | Bookmark Demonstration |
| Command1 | Caption | &Save Bookmark |
| Label1 | Caption | Author ID |
| Label2 | Caption | Author Name |
| Data1 | DatabaseName RecordSource Caption | BOOKS.MDB Authors Authors |
| Text1 | DataSource DataField | Data1 Au_ID |
| Text2 | DataSource DataField | Data1 Author |
Refer to Figure 4.2 as a guide for sizing and locating the controls on the form.
Form layout for the BKMARK01.VBP project.
When you have completed the form layout, add the following code behind the command button. The code in Listing 4.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.
Listing 4.9. Coding the Command1_Click event for BKMARK01.VBP.
Sub Command1_Click ()
Static BkFlag
Static cBkMark
'
If BkFlag = 0 Then
'
' flip flag and set caption
BkFlag = 1
command1.Caption = "&Restore Bookmark"
'
' save bookmark pointer for later
cBkMark = data1.Recordset.Bookmark
Else
'
' flip flag and set caption
BkFlag = 0
command1.Caption = "&Save Bookmark"
'
' restore pointer to old bookmark
data1.Recordset.Bookmark = cBkMark
End If
End Sub
TIP: Listing 4.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 BKMARK01.FRM and the project as BKMARK01.VBP, and then run the program. The program will open the BOOKS.MDB file, create a Dynaset of all the records in the Authors data table, and present the first record on the form. Note that that the command button caption says Save Bookmark. Click on the command button to create a bookmark that points to this record of the Dynaset. The caption changes to Restore Bookmark. Now use the arrow buttons on the data control to move to another record on the form. Click on the command button. You will see that the record pointer has been returned to the first record in the Dynaset. This is because the Dynaset bookmark property was reset to the value you stored earlier.
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 Dynaset. You will learn more about ODBC connected databases on Day 19, "Data Access via ODBC."
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.
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 will use 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 indexes searches, they are fast enough for operations on small- to medium-sized Dynasets. You will learn more about Seek, Find, and Move in Day 11, "Programming with Code."
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 will 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 data sets, 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 your selection criteria.
The Visual Basic Table 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 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 will also see the changes.
Visual Basic Table objects have their drawbacks, too. If you want to use the Table object, you must open the database via program code; you cannot use the data control. 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.
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 will be available to the Table object. Also, as soon as a user deletes a record from the table, the Table object will be updated to reflect the deletion..
The Visual Basic Table 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 (TBSEEK01.VBP) demonstrates the use of Visual Basic Table objects, indexes, and the Seek method. It opens the Titles table of the BOOKS.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 on 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 project that will demonstrate the use of Visual Basic Table objects, indexes, and the Seek method.
Table 4.2. Controls for the TBSEEK.VBP project.
| Control | Property | Setting |
|---|---|---|
| Form1 | Caption | Table Index and Seek Demo |
| Command1 | Name Caption | |
| Command2 | Name Caption | cmdISBN &ISBN |
| Command3 | Name Caption | cmbPublisher &Publisher |
| Command4 | Name Caption | cmdSeek &Seek |
| Command5 | Name Caption | cmdExit E&xit |
| Label1 | Caption Autosize | Titles Tabe—Indexed by; True |
Refer to Figure 4.3 as a guide for placement and positioning of the controls listed in Table 4.2.
The form layout for project TBSEEK01.VBP.
After you have placed the controls on the form and sized them, you need to place the code from Listing 4.10 in the declaration section of the form. This code declares several variables that you will use throughout the form.
Listing 4.10. Declaration code for the TBSEEK01.VBP project.
Option Explicit Dim datObject As Database Dim tblObject As Table ' Dim cDbName As String Dim cTblName As String Dim cIndex As String Dim cField As String
Place the code from Listing 4.11 in the Form_Load event of the form. This code opens the BOOKS.MDB database and opens the Titles table.
Listing 4.11. Coding the Form_Load routine of TBSEEK01.VBP.
Sub Form_Load () ' cDbName = App.Path + "\books.mdb" cTblName = "Titles" ' ' open database and data table Set datObject = OpenDatabase(cDbName) Set TblObject = datObject.OpenTable(cTblName) End Sub
Place the procedure shown in Listing 4.12 in the declaration section. This is the procedure that sets the table index and loads the list box in the proper order.
Listing 4.12. Coding the LoadList routine of TBSEEK01.VBP.
Private Sub LoadList()
Dim cLine As String
List1.Clear
'
tblObject.Index = cIndex
tblObject.MoveFirst
'
On Error Resume Next
'
While Not tblObject.EOF
cLine = tblObject.Fields("Title")
cLine = cLine + "/" + Str(tblObject.Fields("[Year Published]"))
cLine = cLine + "/ " + Str(tblObject.Fields("Au_ID"))
cLine = cLine + "/" + Str(tblObject.Fields("ISBN"))
cLine = cLine + "/" + Str(tblObject.Fields("PubID"))
List1.AddItem cLine
tblObject.MoveNext
Wend
'
Label1.Caption = "Titles Table - Indexed by [" + cField + "]"
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 While..Wend loop reads each record in the table and creates a single line of text (cLine) that contains each of the fields separated by a single space. Notice that you need to use the Str() function to convert the numeric fields in the data table (Year Published, Au_ID, and Pub_ID) into string values before you can add them to cLine. After the line is built, the cLine is added to the list box using the List.AddNew method. After the line is added to the list box, the record pointer is advanced using the TblObject.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 cmdAuthor_Click event:
Sub cmdAuthor_Click () cIndex = "Au_ID" cField = cIndex LoadList End Sub
Place this code in the cmdISBN_Click event:
Sub cmdISBN_Click () cIndex = "Primarykey" cField = "ISBN" LoadList End Sub
Place this code in the cmdPublisher_Click event:
Sub cmdPublisher_Click () cIndex = "PubID" cField = cIndex LoadList End Sub
The Seek routine shown in Listing 4.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 if 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 Search Failed message. If you entered a record that is on file, you see a Record Found message.
Listing 4.13. Coding the Seek routine for TBSEEK01.VBP.
Private Sub cmdSeek_Click()
Dim cSearch As String
'
If List1.ListCount = 0 Then
MsgBox "Select Index First", 0, "Missing Index"
Else
cSearch = InputBox("Enter Search Value for " + cField)
tblObject.Seek "=", cSearch
If tblObject.NoMatch Then
MsgBox cSearch + " not in Table", 0, "Search Failed"
Else
MsgBox tblObject.Fields("Title"), 0, "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.
Sub cmdExit_Click () End End Sub
When you have completed the coding, save the form as TBSEEK01.FRM and the project as TBSEEK01.VBP, and then run the program. Click on the Author, 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 on 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 will see an error message.
Even though the Visual Basic Table 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.
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.
If you plan to do any work with ODBC Data Sources, you will have to forget using the Visual Basic Table object. It does not matter if the ODBC source is a SQL Server data source or a spreadsheet on your local workstation. You will not be able to establish 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 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 will need to process sets of data instead of single records, the Table object will not work as easily or as quickly as a Dynaset or Snapshot object.
Visual Basic Snapshot objects are almost identical to Dynasets in behavior and properties. However, there are two major differences between Snapshot objects and Dynaset objects. These two differences with Dynasets are the two 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.
You'll 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 will be 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 data set; 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 data set will 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.
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 following project (Listing 4.14) illustrates the static aspect of Snapshot data objects compared to the dynamic aspect of Dynaset and Table data objects. There are no controls in this project. The entire source code is listed. Enter it into a single form and save it as SNAPDYNA.FRM and SNAPDYNA.VBP.
Listing 4.14. Comparing Snapshots and Dynasets.
Option Explicit
'
' declare form-level variables
Dim datObject As Database
Dim dynObject As Dynaset
Dim snpObject As Snapshot
Dim tblObject As Table
Dim cDbName As String
Dim cSelect As String
Dim cTblname As String
'
Dim vFields As Variant
Dim nFields As Integer
Sub CountDynaset ()
Dim nCount As Integer
'
' count records in dynaset
dynObject.MoveFirst
dynObject.MoveLast
nCount = dynObject.RecordCount - 1
Me.Print , "Total Dynaset Records:"; nCount
End Sub
Sub CountSnapshot ()
Dim nCount As Integer
'
' count records in snapshot
snpObject.MoveFirst
snpObject.MoveLast
nCount = snpObject.RecordCount - 1
Me.Print , "Total Snapshot Records:"; nCount
End Sub
Sub CountTable ()
Dim nCount As Integer
'
' count records in dynaset
tblObject.MoveFirst
tblObject.MoveLast
nCount = tblObject.RecordCount
Me.Print , "Total Table Records:"; nCount
End Sub
Sub DelDynRecord ()
'
' delete first record in dynaset
dynObject.MoveFirst
dynObject.Delete
End Sub
Sub Form_Activate ()
'
' set variables
cDbName = App.Path + "\books.mdb"
cSelect = "Titles"
cTblname = "Titles"
nFields = 5
'
' put up title
Me.Cls
Me.Print "Comparing Dynaset, Snapshot, and Table Objects"
Me.Print
'
' open files and print counts
OpenFiles
Me.Print ">First Pass"
CountDynaset
CountSnapshot
CountTable
Me.Print
'
' save a record, delete it, then count again
SaveDynRecord
DelDynRecord
Me.Print ">After Dynaset Delete"
CountDynaset
CountSnapshot
CountTable
Me.Print
'
' restore the record, then count aagin
RestoreDynRecord
Me.Print ">After Dynaset Restore"
CountDynaset
CountSnapshot
CountTable
Me.Print
End Sub
Sub OpenFiles ()
'
' open database, dynaset, & snapshot, & table
Set datObject = OpenDatabase(cDbName)
Set dynObject = datObject.CreateDynaset(cSelect)
Set snpObject = datObject.CreateSnapshot(cSelect)
Set tblObject = datObject.OpenTable(cTblname)
End Sub
Sub RestoreDynRecord ()
Dim x As Integer
'
' add a new rec, write out, and save
dynObject.AddNew
For x = 0 To nFields - 1
dynObject.Fields(x).Value = vFields(x)
Next x
dynObject.Update
End Sub
Sub SaveDynRecord ()
Dim x As Integer
'
' save record before deleting
dynObject.MoveFirst
For x = 0 To nFields - 1
vFields(x) = dynObject.Fields(x).Value
Next x
End Sub
When you run the SNAPDYNA.VBP program, you'll 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.
Visual Basic Snapshot 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 data set each time you need to verify user input.
If the data set 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 data set for use in calculating reports. This way, any changes in the data set 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 64K 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.
Visual Basic has two special data objects which are both actually runtime properties of the data control. The Database object enables you to access properties of an underlying database attached to the data control with the DatabaseName property. The Recordset object enables you to access properties of an underlying data table attached to the data control with the RecordSource property.
Because the Recordset data object is actually a property of a Visual Basic data control, you can only use the Recordset as a subobject of the data control. The Recordset acts the same as a Visual Basic Dynaset. It allows you access to all the properties and methods associated with the data set object created by the data control. This data set object is always a Visual Basic Dynaset data object. The Recordset data object is only available as a property of the data control. Any operations you would normally perform on a Dynaset object can be performed on a Recordset object.
The Database property 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 hardcoded field names and data types. TableDefs objects are covered in more detail in Day 10. For now, though, let's write a short routine that lists all the tables, fields, and indexes in the BOOKS.MDB database.
Use the information in Table 4.3 to set the form property settings and place the data control on the form.
Table 4.3. The controls for project 04ABC1.MAK.
| Control | Property | Setting |
|---|---|---|
| Form | Caption WindowState | Database Demo Maximize |
| DataControl | DatabaseName RecordSource | BIBLIO.MDB Authors |
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 will print on the form itself. Enter the program code in Listing 4.15 in the Form_Activate event.
Listing 4.15. Listing Database objects.
Private Sub Form_Activate()
Dim i As Integer
Dim j As Integer
'
' open the database
Data1.DatabaseName = App.Path + "\books.mdb" ' Set Database file.
Data1.Refresh
'
' Read and print the table info
For i = 0 To Data1.Database.TableDefs.Count - 1
Me.Print "Table Info"
Print " "; Data1.Database.TableDefs(i).Name
For j = 0 To Data1.Database.TableDefs(i).Fields.Count - 1
Print " -"; Data1.Database.TableDefs(i).Fields(j).Name
Next j
MsgBox "Press OK to Continue."
Me.Cls
Next i
'
' read and print index info
On Error Resume Next
'
For i = 0 To Data1.Database.TableDefs.Count - 1
Me.Print "Index Info"
Print " "; Data1.Database.TableDefs(i).Name
For j = 0 To Data1.Database.TableDefs(i).Indexes.Count - 1
Print " -"; Data1.Database.TableDefs(i).Indexes(j).Name;
Print " ["; Data1.Database.TableDefs(i).Indexes(j).Fields; "]"
Next j
MsgBox "Press OK to Continue."
Me.Cls
Next i
End
'
End Sub
After you enter the code, save the form as DBO.FRM and the project as DBO.VBP, and then run the program. You will 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 on to the next table. After clicking OK through the table listing, you will 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 4.4 for tables and the one in Figure 4.5 for indexes.
NOTE: As you click through the database tables, you will 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 will also notice that each Index object consists of a unique name and one or more fields (displayed in brackets). You will not see a data table associated with the index since 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!).
Using the Database object to create a Table Info listing.
Using the Database object to create an Index listing.
In today's lesson, you learned that there are three main types of Visual Basic data objects:
You also learned that there are two other Visual Basic data objects, both associated with the Visual Basic data control.
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.
