![]() |
|||
![]()
|
![]() |
![]() |
![]() |
Day 3Visual Basic Database ObjectsIn 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.
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.
Dataset-Oriented Versus Data Record-OrientedBefore 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 and Data ObjectsVisual Basic database objects are dataset-oriented. Visual Basic programs generally perform better when data operations are done with a dataset than when data operations are done on single records. Some Visual Basic objects work well when performing record-oriented operations; most do not. The Visual Basic table-type Recordset object is very good at performing record-oriented processing. The Visual Basic Dynaset- and snapshot-type Recordset objects do not perform well on record-oriented processes. A common mistake made by database programmers new to Visual Basic is to create programs that assume a record-oriented database model. These programmers are usually frustrated by Visual Basic's slow performance on large data tables and its slow response time when attempting to locate a specific record. Visual Basic's sluggishness is usually due to improper use of Visual Basic data objects--most often because programmers are opening entire data tables when they only need a small subset of the data in order to perform the required tasks. Dataset Size Affects Program PerformanceUnlike record-oriented systems, the size of the dataset you create affects the speed at which Visual Basic programs operate. As a data table grows, your program's processing speed can deteriorate. In heavily transaction-oriented applications, such as accounting systems, a dataset can grow quickly and cripple your application's ability to process information. If you are working in a network environment where the machine requesting data and the machine storing the data are separated, sending large datasets over the wire can affect not only your application, but all applications running on the network. For this reason, it is important to keep the size of the datasets as small as possible. This does not mean you have to limit the number of records in your data tables! You can use Visual Basic data objects to select the data you need from the table instead. For example, you might have a data table that contains thousands of accounting transactions. If you want to modify the payment records in the data table, you can create a data object that contains all of the records (quite a big set), or you can tell Visual Basic to select only the payment records (a smaller set). Or, if you know that you only need to modify payment records that have been added to the system in the last three days, you can create an even smaller dataset: The smaller the dataset, the faster your program can process the data. Visual Basic data objects give you the power to create datasets that are the proper size for your needs. The Dynaset-Type Recordset Data ObjectThe Visual Basic Dynaset-type Recordset data object is the most frequently used data object in Visual Basic programs. It is used to dynamically gain access to part or all of an existing data table in a database, hence the name Dynaset. When you set the DatabaseName and RecordSource properties of a Visual Basic data control, you are actually creating a Visual Basic Dynaset-type Recordset. You can also create a Dynaset-type Recordset by using the CreateDynaset method of the Database object. When you create a Visual Basic Dynaset-type Recordset, you do not create a new physical table in the database. A Dynaset exists as a virtual data table. This virtual table usually contains a subset of the records in a real data table, but it can contain the complete set. Because creating a Dynaset does not create a new physical table, Dynasets do not add to the size of the database. However, creating Dynasets does take up space in RAM on the machine that creates the set (the one that is running the program). Depending on the number of records in the Dynaset, temporary disk space can also be used on the machine requesting the dataset. Strengths of the Dynaset-Type Recordset ObjectThere 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.
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).
When you open a Dynaset using Visual Basic code instead of using the data control, you must create two Visual Basic objects: a Database object and a Recordset object. Listing 3.1 shows how you create the objects in Visual Basic code. Listing 3.1. Creating a Database object and a Recordset object.Private Sub Form_Load() ` ` creating Dynaset-type recordsets ` Dim db As Database ` the database object Dim rs As Recordset ` the recordset object ` End Sub
Listing 3.2. Declaring database and data table variables.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
Before you continue with the chapter, save this form as DYNASETS.FRM and save the project as DYNASETS.VBP. Now that you have created the data objects, created variables to hold database properties, and initialized those variables with the proper values, you are ready to actually open the database and create the Dynaset-type Recordset. The code in Listing 3.3 shows how to do this using Visual Basic code. Listing 3.3. Opening the database and creating the Dynaset.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
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.
The code in Listing 3.3 is all that you need to open an existing Microsoft Access database and create a Dynaset-type Recordset ready for update. However, for this project, you want to see a bit more. Let's add some code that tells you how many records are in the Titles data table. You need one more variable to hold the record count. You also use the MoveLast method to move the record pointer to the last record in the Recordset. This forces Visual Basic to touch every record in the collection, and therefore gives you an accurate count of the total number of records in the table. You get the count by reading the RecordCount property of the Recordset. When you have all that, you display a Visual Basic message box that tells you how many records are in the Recordset. Listing 3.4 contains the code to add. Listing 3.4. Counting the records in a Dynaset.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
Let's modify DYNASETS.VBP to create a smaller Dynaset-type Recordset from the existing Recordset. You need to create a new Recordset object and a new variable called strFilter to hold the criteria for selecting records. The code in Listing 3.5 shows how to add the object and variable to the existing DYNASETS.VBP project. Listing 3.5. Adding a new Recordset object and string variable.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
Listing 3.6. Using the Filter property to create a Recordset.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
Listing 3.7. Displaying the record count of the filtered Recordset.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
Now let's make one more series of changes to DYNASETS.VBP that illustrate the Clone method for Recordsets. Cloning a Recordset makes a duplicate of the set. Add another data object (rs3), and add the Clone Recordset program code in Listing 3.8. Listing 3.8. Cloning a new Recordset.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
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.
Refer to Figure 3.5 as a guide for sizing and locating the controls on the form. Figure 3.5. Laying out the Bookmark Demonstration form.
Listing 3.9. Coding the cmdSaveBookmarks_Click event for BOOKMARKS.VBP.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
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." Limitations of the Dynaset-Type Recordset Data ObjectAlthough the Dynaset is an excellent data object, it has a few drawbacks that must be considered. Chief among these is that Dynasets do not allow you to specify an existing index, and you cannot use the Visual Basic Seek method to quickly locate a single record in the Dynaset. Also, errors can occur when displaying records in a Dynaset if the records in the underlying data table have been altered or deleted by another user. Dynaset Access and Seek Limitations Dynasets cannot make use of Index objects that exist in a database because the Index is built to control the entire data table and not just a subset of the data. Because Dynasets could be subsets of the data table, the Index is useless. Also, because you cannot specify an Index object for a Dynaset, you cannot use the Visual Basic Seek method on a Dynaset. These are only minor limitations. If you have defined an Index in the underlying table with the Primary flag turned on, the Visual Basic data engine uses the primary key index when creating the Dynaset. This usually puts the Dynaset in optimal order. Even though you cannot use the Seek method on a Dynaset, you can use the FindFirst, FindNext, FindPrevious, and FindLast methods. Even though they are not true index searches, they are fast enough for operations on small- to medium-sized Dynasets. You learn more about Seek, Find, and Move in Day 10, "Creating Database Programs with Visual Basic Code." Dynamic Membership-Related Errors If your program opens a database and creates a Dynaset from an underlying table while another user has also opened the same database and created a Dynaset based on the same underlying data table, it is possible that both users will attempt to edit the same data record. If both users edit the same record and both attempt to save the record back to the underlying table, the second person who attempts to save the record receives a Visual Basic error. When the second person tries to save the record, Visual Basic discovers that the original record in the underlying data table has been altered. In order to maintain database integrity, Visual Basic does not allow the second person to update the table. When to Use the Dynaset-Type Recordset Data ObjectThe Dynaset object should be used in most database programs you write. In most cases, the Visual Basic Dynaset data object is the most effective data access object to use. It offers you a way to create a dynamic, updatable subset of data records in one or more data tables. The Dynaset object is the default object created by the bound data control and is the only updatable data object you can use to access ODBC data sources. The Dynaset is not a good data object to use when you need to do a great deal of record-oriented processing on large datasets, such as index look-ups on large transaction files. If you have a Visual Basic program that uses Dynasets and is showing slow database performance, look for places where you can limit the size of Dynasets by narrowing the selection criteria. The Table-Type Recordset Data ObjectThe 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. Strengths of the Table-type Recordset Data ObjectThe 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.
Refer to Figure 3.6 as a guide for placement and positioning of the controls listed in Table 3.2. Figure 3.6. Laying out the TbSeek form.
After you have placed the controls on the form and sized them, you need to place the code from Listing 3.10 in the declaration section of the form. This code declares several variables that you use throughout the form. Listing 3.10. Declaration code for the TBSEEK.VBP project.Option Explicit ` ` form-level variables ` Dim db As Database Dim rs As Recordset ` Dim strDBName As String Dim strRSName As String Dim strIndex As String Dim strField As String
Listing 3.11. Coding the Form_Load routine of TBSEEK.VBP.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
Listing 3.12. Coding the LoadList routine of TBSEEK.VBP.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
Now the fun starts. The Do While..Loop construct reads each record in the table and creates a single line of text (strLine) that contains each of the fields separated by a single space. Notice that you need to use the CStr() function to convert the numeric fields in the data table (YearPub, ISBN, and Pub_ID) into string values before you can add them to strLine. After the line is built, the strLine is added to the list box using the lstRecordset.AddNew method. After the line is added to the list box, the record pointer is advanced using the rs.MoveNext method. This goes on until there are no more records in the table. The following three code segments go behind the appropriate command button to set the indexes. They set values for selecting the index, setting the display, and calling the routine to load the list box. Place this code in the cmdTitle_Click event: Private Sub cmdTitle_Click() ` ` set for Title index ` strIndex = "Title" strField = "Title" LoadList ` End Sub Place this code in the cmdISBN_Click event: Private Sub cmdISBN_Click() ` ` set for ISBN index ` strIndex = "PrimaryKey" strField = "ISBN" LoadList ` End Sub Place this code in the cmdPublisher_Click event: Private Sub cmdPublisher_Click() ` ` set for PubID index ` strIndex = "PubID" strField = "PubID" LoadList ` End Sub The Seek routine shown in Listing 3.13 calls an input box to prompt the user for a search value, performs the seek, and reports the results of the search. The routine first checks to see whether the user has filled the list box by selecting an index. If the list box contains data, the routine calls the Visual Basic InputBox function to get user input, and then invokes the Seek method of the table object. If the record is not found, you see a Seek Failed message. If you entered a record that is on file, you see a Record Found message. Listing 3.13. Coding the Seek routine for TBSEEK.VBP.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
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.
|
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.
![]() |
|
Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details. |