Hour 27


During this hour you will learn

Accessing Databases

This lesson explains how to access databases. A database is a collection of files that work together to form a complete data-management system. A database system, such as Microsoft Access, creates the database that your VB applications may need to connect to. By using the Data controls, DAO programming, and the Data Form Wizard, your applications can manage and access a database without disturbing its integrity.

Topic 1: The Data Controls

Visual Basic includes the Data control and some cousin controls that perform application access to data outside the realm of ordinary data files. Although you learned how to read, write, and update files in Hour 21, "Accessing Files," the concepts presented there didn't help you interact with database-created data. A database system is a program, such as Microsoft Access or Paradox, that creates and manages data for you in a special format. As you'll learn in this lesson, VB's Data controls interact with databases and lighten the programming required to access such databases.

The Data control and its related controls are difficult to cover in a single chapter because they're difficult to cover in a single book! This lesson provides you with the guidance you need to get started with the Data control and the related controls. All editions of Visual Basic support the Data control and additional controls that you'll find helpful in accessing data from external database files. The Professional and Enterprise editions include an advanced set of database controls called DAO (for Data Access Objects) that perform faster and more advanced database interaction.

Overview

This topic section examines the Data controls. You'll learn to place the primary control (the Data control) and other forms of the Data control, and also learn how to bind (connect) those controls to your form's application. The Data controls take care of most of your database access. You can be concerned with your application's goals and not worry so much about the tedium of file access when you begin using the Data controls.

Database Data

This lesson assumes that you want to connect your applications to a database file. Not every VB programmer has access to a database system, so you may wonder where your application's database data will come from.

Visual Basic includes a sample Microsoft Access database file called Biblio.mdb, and this lesson will use that file. Therefore, you can practice with this lesson's exercises even if you don't have access to an outside database source. Remember, however, that this lesson offers only a small overview of the whole VB-to-database picture, and you must master fairly advanced concepts in database theory to use all of VB's database concepts.

Visual Basic does include an add-in tool called VisData that lets you create, edit, and manage database files for the following database programs, even if you don't have those database programs on your computer:

From the Add-Ins menu, choose Visual Data Manager to start VisData. You can create your own database files with VisData and, if you ever get one of the listed database applications, you can load your VisData files directly into the database.

VB's Professional and Enterprise editions come with the source code for VisData. The VisData add-in program is a Visual Basic application that you can study to learn more about advanced coding.

Terminology

In database terminology, a table is a file, and the fields and records relate to columns and rows in the file. A database varies greatly from a normal file because a database file generally contains multiple tables, as well as access techniques (called queries) that return data in a predetermined format. A database file also can contain predefined report and screen form definitions so that database users can see the data needed.

Today, most database systems are relational, meaning that no two records are exactly alike and file redundancy is eliminated as much as possible. Microsoft Access is such a relational database. For database files that aren't relational, such as pre-dBASE 4.0 files, you have to add fairly complex VB code to change the database to relational access before performing I/O with the database.

Database tables usually have at least one index defined. An index is a key field with unique values for every record. The index works just as a book's index works; when you need to access a particular row in the table, you can specify the index value and the database can jump directly to the row without having to search the entire table as you would have to do when writing sequential file-access routines.

When working with selected data, you'll often define or use a recordset, which is a subset of the actual table. For example, a customer balance recordset might contain only those customers from the customer file who owe more than $100 to the company. Often, a database query creates this recordset.

Bound Controls

Bound controls are controls bound to your database data in a way that makes traversing the database simple for your program. In a nutshell, you can bind many VB controls, such as a text box, to a database; when users step through the database, the text box displays the field you've set up. When you bind the control to the database, you don't have to worry about displaying the field data because Visual Basic does all the work.

If you need to perform a unique operation on the data before displaying the data in a bound control, you'll have to use VB's database commands to process the database.

In particular, if you want to display one record at a time, use bound controls. Bound controls usually display only the current record, or a field within the current record. (As a user traverses the database, the current record changes to reflect the user's position within the selected table.)

Use the special bound grid control named DBGrid to display multiple records at one time, as well as DBList and DBCombo. The DBGrid control is the only control that lets users update the displayed data, however.

A Data control has five parts:

You'll see the Data control's parts when you add the control to a form in the next example.

Example

This example creates an application that uses the Data control. Create a new project and perform these steps to add the Data control:

  1. Double-click the Data control to add the Data control to the form. Change the Data control's Width to 4620 to make room for text that will appear on the control. Move the Data control to the bottom of the Form window. Your Form window should look like the one in Figure 27.1.

FIG. 27.1

The Data control is now on the form.

  1. Name the Data control dtaBooks.
  2. Use the DatabaseName property to connect the Data control to the database. Click the DatabaseName ellipses and select the file named Biblio.mdb from the VB folder. Biblio.mdb is the Microsoft Access database that comes with Visual Basic.
  3. Remember that a database file can hold multiple tables, so you must tell the Data control which table the control should access within the database. Open the RecordSource property to display a list of tables (see Figure 27.2) that appear in the selected database. Select Titles.

FIG. 27.2

Tell the Data control which table to access from the database.

  1. Change the Data control's Caption property to Click to change titles.
  2. Add a Label control to the form with these properties: Font.Size: 18, Height: 2115, Name: lblBooks, Top: 1275, and Width: 4200.
  3. Open the label's DataSource property drop-down list and select dtaBooks. If the form contained multiple Data controls, you would have to select the one that targets the label.
  4. Not only must you tell Visual Basic which data control provides the data, but you also must tell Visual Basic the field to display. Open the label's DataField property drop-down list to display a list of all the fields with the Data control's selected table. Select Title.
  5. You're ready to run the application. As soon as you execute the application, the first title appears in the label. Click the various Data control buttons to traverse the database table. As you click, the label bound to the Data control updates to show the current row's title (see Figure 27.3).

FIG. 27.3

The Data control does all the work.

Next Step

If you choose Components from the Project menu, you can add additional Data controls by selecting the options labeled Microsoft Data Bound Grid Control and Microsoft Data Bound List Controls 5.0. Three new tools--DBCombo, DBList, and MSFlexGrid--will appear in the Toolbox when you click OK. Remember these controls if you want to display multiple records at one time, so users can select from a drop-down list rather than click the Data control to retrieve the fields you need.

Be sure to use a check box to display Yes/No database data. The check box indicates that the Yes/No value is Yes when selected and No when not selected.

If you display the field data in a text box, users can modify the data. When users then click the Data control, the changes stay recorded in the database file. If you use a Label control, users will only be able to see the data but not change it.

You should know that you can accomplish a lot more with the Data control than is taught in this topic section. Nevertheless, the section has introduced you to the power and ease that the Data control and its related data controls bring to applications that must interact with databases. As your programming skills improve, you'll begin to use some of the Data control methods to perform the database traversal. For example, the Move... methods move the current record pointer to the same locations as the Data control's buttons. The following methods would move the record pointer to the first, last, next, and previous records in the database table pointed to by the Data control's DataSource property:

dtaBooks.Recordset.MoveFirst    ' Move to the 1st record

dtaBooks.Recordset.MoveLast     ' Move to the last record

dtaBooks.Recordset.MoveNext     ' Move to the next record

dtaBooks.Recordset.MovePrevious ' Move to the previous record

Remember that a recordset is a collection of records from a table. The Data control's recordset is defined by the property values you set. If you added a command button to the Data control example that included a Click event procedure which in turn contained one of these methods, the label would display that record selected by the method every time users clicked the control. In addition to the record-movement controls, methods exist that add and delete records.

Use the BOF and EOF Boolean properties to see whether the record pointer is at the beginning or end of the table. VB programmers often use a Do...While loop to step through every table record, and the loop terminates when dtaBooks.Recordset.EOF is equal to True.

Topic 2: Data Access Objects (DAO)

If you use either the Professional or Enterprise editions, you can learn how to program Visual Basic by using DAO (Data Access Objects). Data Access Objects are database objects that you create and manage with your program code.

The primary reason for mastering DAO is because it offers several advantages over the Data control. DAO gives you more control and speed in accessing databases. Although using DAO takes a little more knowledge than using the Data control and its related controls, DAO is the choice among most VB programmers due to its powerful advantages.

Overview

In the first topic section, you learned some recordset methods that apply to the Data control. Data Access Objects also use recordset methods, as you'll see in this topic section. This section discusses the advantages and disadvantages of using DAO over the Data controls, so you can more easily determine whether DAO is for you.

DAO Advantages and Disadvantages

The Data control is simple to use but doesn't offer extremely fast database access. Although today's computers run quickly, you'll notice speed degradation when you use the Data control in large database tables, especially ODBC-based databases.

When you use DAO, you must write more program code than you have to write with the Data control. As you saw in the first topic section, you can program the Data control primarily through setting property values. Although you can write code that accesses various Data control methods, straightforward database access is less involved with the Data control.

The DAO lets you control the data access in a much stricter way than with the Data control. The ease of the Data control reflects its inability to be flexible. Also, the overhead of the Data control doesn't burden DAO-based programs. DAO uses the recordset concept for most of its operations, and you can create a recordset variable just as you can create other kinds of object variables in Visual Basic.

Database Variables

When you need to work with a database, you'll need to declare a variable of the data type named Database. The following statement declares such a variable:

Dim dbBooks As Database   ' Declare a database variable

The database variable will provide the connection to the database throughout your code.

You'll also need to create Dynaset and Snapshot variables from time to time. A dynaset is a recordset that remains active; if the recordset data changes, the corresponding dynaset data changes also. Think of a dynaset as being a link to the database table's records. A snapshot is a one-time picture of the recordset. The recordset works like a copy of a selected recordset; when the original recordset changes, the copy doesn't change.

The OpenDatabase() Function

When you declare a database variable, you must specify the OpenDatabase() function to open the selected database. Remember that a database contains many tables. Before you can access one of the tables, you must open the database. The OpenDatabase() function informs Visual Basic that you want to access the database.

The following code declares a database variable and opens a database:

Dim dbBooks As Database

Set dbBooks = OpenDatabase("Biblio.mdb")

After OpenDatabase() completes its task, Visual Basic has prepared the database and connected the database file to your Database data-typed variable. The Set command works almost like an assignment statement. Set is used to create a reference to an object. The dbBooks variable can't really hold the entire database, but dbBooks is a reference to the database.

The OpenDatabase() function generates an error if the database file doesn't exist or if a hardware error occurs when the OpenDatabase() function executes. Also, you may want to search the online help for the OpenDatabase() function options that exist for particular database types. For example, you can include a password if the database is protected, and you can open a database for exclusive access so that others on the network can't access the database until you complete your work.

The global Database data-typed variables are the few exceptions to the rule of local variable usage. The database resides outside your entire application. In every real respect, the database is global to your application due to its separation from your application. Variables, on the other hand, are internal to your program, so local variables provide safety barriers to keep some parts of the code away from some variables.

The OpenDatabase() function opens both ODBC databases and Jet engine databases. A Jet engine database is a Microsoft-designed database specification that provides quick access to databases. The database can reside in any folder and on any computer networked to yours.

Working with Dynasets

A dynaset, being a selected subset of a database, lets you work with a smaller set of data than the entire database table. The nice thing about dynasets is that as soon as you make a change to a dynaset, the linked table updates in the database.

One problem is that the creation of dynaset variables often requires a full working knowledge of SQL (pronounced sequel), a database-access language that works among many database products. This and subsequent sections will show you some recordset and dynaset object creation and manipulation by example, but much more can be involved in the coding of DAO. As stated earlier, DAO can be much tougher to code than the Data controls, but DAO provides speed and flexibility that you don't get with the Data controls.

One common dynaset variable you can create references an entire table from the database. The following code defines a database and sets a dynaset variable named dsTitles to the Titles table inside the Biblio.mdb database:

Dim dbBooks As Database

Dim dsTitles As Recordset   ' A dynaset is a special recordset

' Create reference to database

Set dbBooks = OpenDatabase("Biblio.mdb")

' Create reference to dynaset (the entire table)

Set dsTitles = dbBooks.OpenRecordset("Titles", dbOpenDynaset)

When you supply a table name for OpenRecordset()'s first argument, as done here, the entire table is referenced from the dynaset variable named dsTitles. Therefore, subsequent code can access information from the dynaset that corresponds to the entire table. vbOpenDynaset makes the recordset a dynaset, as opposed to a snapshot that would result if you used the vbOpenSnapshot option. You can also pass OpenRecordset() the name of an existing query. Many database systems, such as Microsoft Access, let you specify queries that retrieve records and fields based on a criterion. You can save those queries and reference them in the OpenRecordset() function. The existing named query sets the dynaset variable to that subset of data generated by the query.

A big query advantage is that a query can access data from multiple tables within the database. The recordset acts as though the data all came from a single location.

Also, if you know SQL, you can place SQL language directly in the OpenRecordset() function, like this:

Set dsTitles = dbBooks.OpenRecordset("Select * FROM " _

  & "Titles WHERE Year Published = '1998'"), dbOpenDynaset)

The ampersand (the concatenate operator) lets you break the OpenRecordset() more easily into two lines.

After the OpenRecordset() finishes its task, the dynaset associates with its variable. To assign the dynaset's current record field value to a control such as a Label control, use this code:

If IsNull(dsTitles!Title) Then    ' Don't assign Null

  lblBookTitle.Caption = ""

Else

  lblBookTitle.Caption = dsTitles!Title

End If

You can shorten the code by using the IIf() function, like this:

lblBookTitle.Caption = __

  IIf(IsNull(dsTitles!Title), "", dsTitles!Title)

Consider the following when studying these If tests:

Example

This example works with the Biblio.mdb database through DAO object code. Create the form shown in Figure 27.4. All the buttons at the bottom of the form are command buttons that let you manage the database. You've worked with the Titles table from the Biblio.mdb database throughout this lesson, and this example uses DAO to access that same data. The text boxes on the form correspond to the seven fields from the Titles table. Set the title's Text Box control to a Multiline value of True, and add vertical scroll bars so that users can see long book titles. Draw the lines that separate some of the controls with the Line control.

FIG. 27.4

You can create a form that accesses and manages a database.

The first task is to declare your database variables. Enter the following code in the form module's Declarations section:

Dim dbBooks As Database

Dim dsBooks As Recordset    ' Dynaset

Dim blnNewRec As Boolean    ' Adding a new record?

The global variable named blnNewRec will come in handy in this project and later if you expand the project. The variable keeps track of the users' add or edit modes so that an appropriate update can be made to the database. Again, the database is global to your program, and such a variable attached so closely to the database is best left global so that all the routines accessing the database will know the database status.

You must open the database and create the dynaset. In the Form_Load() procedure, code this:

Private Sub Form_Load()

  ' Open the database and initialize the dynaset

  ' Set a path in the following line if needed

  Set dbBooks = OpenDatabase("Biblio.mdb")

  Set dsBooks = dbBooks.OpenRecordset("Titles", dbOpenDynaset)

  Call DispForm   ' Display the form and its data

End Sub

Although you must display the first data record on the form when the form first appears, you should code a separate procedure named DispForm () to display the data inside the form's text boxes. Although you could enter the text box initialization inside Form_Load(), you need to initialize the text boxes elsewhere when users move to other records in the table. Therefore, good coding principles dictate that you put the code in a separate procedure so that you can call the record-displaying routine from elsewhere. When the application first opens the database and creates the recordset, the record pointer points to the first record in the file, so you can call the record-displaying routine without adjusting the record pointer.

The DispForm () procedure in Listing 27.1 assigns the current record's fields to the text boxes.

Listing 27.1 Tbrecord.bas: Assigning Record Fields to Text Boxes

Private Sub DispForm ()

  ' Fill the text boxes with the initial record

  Dim varButton As Variant   ' MsgBox() return

  If Not dsBooks.EOF Then   ' If not at end of file

    ' Use default Text property

    txtTitle = _

      IIf(IsNull(dsBooks!Title), "", dsBooks!Title)

    ' Place multiple word field names inside brackets

    txtYear = _

      IIf(IsNull(dsBooks![Year Published]), "", dsBooks![Year Published])

    txtISBN = _

      IIf(IsNull(dsBooks!ISBN), "", dsBooks!ISBN)

    txtPubID = _

      IIf(IsNull(dsBooks!PubID), "", dsBooks!PubID)

    txtSubject = _

      IIf(IsNull(dsBooks!Subject), "", dsBooks!Subject)

    txtDescription = _

      IIf(IsNull(dsBooks!Description), "", dsBooks!Description)

    txtComments = _

      IIf(IsNull(dsBooks!Comments), "", dsBooks!Comments)

  Else

    varButton = MsgBox("No records exist in the file")

  End If

End Sub

The assignments demonstrate several programming aspects. The lines are long, so the underscore (_) works as a line-continuation character in the assignment statements. As stated earlier in this topic section, you shouldn't assign null values to fields, so the IIf() function guards against assigning nulls. If the field contains Null, an empty string goes into the text box. Notice also that if a field name contains blanks, you must enclose the field name inside brackets.

The code does not list the Text Box control's Text property in the assignment. In other words, the control named txtYear is assigned the table's year, not txtYear.Text, which is the Text Box control value that you usually assign to. All controls have default property values that are the most common properties usually assigned to the control. Therefore, the following statements are identical, but the first one is simpler to type because the Text value is understood:

txtFName = "Laura"       ' The Text property is assumed

txtFName.Text = "Laura"

Before running the application, you should code the Next (>) and Exit command buttons so that you can step through the table and exit the program. Enter the event procedures in Listing 27.2.

Listing 27.2 Nextend.bas: Coding the Next and Exit Command Buttons

Private Sub cmdExit_Click()

  dbBooks.Close    ' Close the database

  ' A great idea in case user hides the form

  End

End Sub

Private Sub cmdNext_Click()

  ' Display the next record

  Dim varButton As Variant   ' MsgBox() return

  If Not dsBooks.EOF Then

    dsBooks.MoveNext   ' Updates the pointer

    ' Check to see if at end of file and a blank rec

    If dsBooks.EOF Then

      dsBooks.MovePrevious  ' Re-adjust pointer

    End If

  End If

  Call DispForm

End Sub

The extra If inside cmdNext_Click() keeps the next record button from displaying DispForm ()'s message box that indicates no records exist in the file.

Run the application to display the form in Figure 27.5, and look through the titles. You still have some coding left to do, but the fundamentals are already in the application. Some Biblio.mdb fields contain strange values, such as empty braces in the Comments field, but the title and other fields generally contain valid data.

FIG. 27.5

The book titles appear, thanks to DAO.

Next Step

The code in Listing 27.3 takes care of the previous record command button (<).

Listing 27.3 Previous.bas: Code for the Previous Record Button

Private Sub cmdPrevious_Click()

  ' Display the previous record

  If Not dsBooks.BOF Then   ' Check for file beginning

    dsBooks.MovePrevious

    ' Keep DispForm()'s No Data message box out

    If dsBooks.BOF Then

      dsBooks.MoveNext  ' Back to beginning

    End If

  End If

  Call DispForm

End Sub

The Data control automatically updates the underlying database record if users make a change in a bound text box, but you must do the edit processing for DAO data. The event procedure in Listing 27.4 is just a start for updating the record when users make a change to a field and click the Save button to save those record changes.

Listing 27.4 Recsave.bas: Saving the Record's Changes

Private Sub cmdSave_Click()

  ' Assign all the text boxes to the fields

  ' First you must prepare the DAO for editing

  If blnNewRec Then

    dsBooks.AddNew  ' Prepare to add a record

  Else

    dsBooks.Edit    ' Prepare for editing

  End If

  ' Assign non-null values to fields

  dsBooks!Title = _

    IIf(txtTitle = "", "N/A", txtTitle)

  ' Don't save

  dsBooks![Year Published] = _

    IIf(txtYrPub, txtYrPub, Empty)

  dsBooks!ISBN = _

    IIf(txtISBN = "", "N/A", txtISBN)

  dsBooks!PubID = _

    IIf(txtPubID = "", "N/A", txtPubID)

  dsBooks!Subject = _

    IIf(txtSubject = "", "N/A", txtSubject)

  dsBooks!Description = _

    IIf(txtDescription = "", "N/A", txtDescription)

  dsBooks!Comments = _

    IIf(txtComments = "", "N/A", txtComments)

  ' Make the actual update to dynaset

  dsBooks.Update

  If blnNewRec Then  ' If adding a record

    dsBooks.MoveLast ' Add to end of dynaset

    blnNewRec = False

  End If

End Sub

The dynaset's Edit method prepares the database for an edit. If another networked user has the record, you won't be able to edit it, and an error will occur. Although this tutorial omits most error-processing code to focus on the current topic, you should add an On Error Goto routine to the code to handle such potential problems. This procedure only begins to handle data errors. You probably don't want to write Null values back to the database, so the procedure writes either N/A (for not applicable) or Empty, depending on the data type (string or numeric) of the field being updated.

As you can see, programming with DAO isn't a trivial task. You've been working on this example for a while and the functionality--although getting closer to being final--is incomplete. Consider the following issues required to complete the task:

To really master DAO, consider getting Que Publishing's Special Edition Using Visual Basic 5, which explains DAO in detail and completes your study of the subject.

Topic 3: Visual Basic Wizard

The preceding topic section only scratched the surface of DAO, but you now know the fundamental requirements and issues that surround DAO programming. You deserve a break, so this final topic section demonstrates a way that you can let Visual Basic do all the work--writing a database application by responding to a few dialog boxes from the VB Application Wizard.

Overview

The VB Application Wizard lets you add database capabilities to your project without extensive programming. As you'll see when you follow this topic section's example, the resulting code is fairly complete and forms the basis for a true database application.

The New Project dialog box contains the VB Application Wizard that you've used a few times throughout this book. When you get to the dialog box shown in Figure 27.6, you can click the Yes option to add database support to your application.

FIG. 27.6

The VB Application Wizard creates database-based applications.

After you select the database option, the rest of the wizard changes dramatically from the dialog boxes that you've seen so far. The next "Example" and "Next Step" sections describe the wizard in more detail.

Before writing a DAO application, create the initial shell with the VB Application Wizard and modify the application to suit your specific needs. You won't have to write as much code if you start with the wizard's shell.

Example

From the File menu choose New Project, double-click the VB Application Wizard icon, and click Next six times to accept all the VB Application Wizard defaults and to display the dialog box you saw earlier in Figure 27.6. Perform these steps to begin the database-aware application:

  1. Click Yes to request that the wizard add a database form to the application. You must now select a database on which the wizard will base the form's fields.
  2. Click the Browse button and locate the Biblio.mdb database located in the VB directory. Notice that you can select from a wide variety of database systems by opening the Database Format drop-down list. (For this application, retain the default database, Access.) Click Next to display the Select Tables dialog box (see Figure 27.7).

FIG. 27.7

Visual Basic must know the table you want to access.

  1. Your application can retrieve data directly from the database tables or from any defined queries. As you might recall from earlier in this lesson, a query is a predefined selection criterion for records and fields. A query is just a named instruction set that produces a subset of the database data. The big advantage of queries over table access is that a query, if predefined by users of the database system that generated the database file, can contain data from multiple database tables. The Biblio.mdb database contains only one query, named All Titles (you can click the Queries option to see the query name).
  2. This dialog box lets you add multiple tables, so you can select data from more than one table even if no query exists, but the query is often more efficient if defined properly. For this application, however, you'll select two tables: Authors and Titles.
  3. Click the Authors table and then click the > button to send the Authors table to the Selected list. Now send the Titles table to the Selected list.
  4. Click Next and Finish to finish the wizard and create the project.

The wizard generates a substantial amount of database code for you and designs forms that make the two tables available to the application. When you run the application, choose Authors from the View menu to display the Form window shown in Figure 27.8.

FIG. 27.8

The wizard created the form that updates the Authors table.

From the View menu, choose Titles to open the Titles dialog box. Perhaps the most surprising feature of the application appears when you click Grid. Visual Basic formats the table's data into the worksheet-like format shown in Figure 27.9. You can resize columns, sort on columns (by clicking a column title), and scroll through the grid to view multiple records at one time. The grid comes from the DBGrid control that the wizard added to the application's Toolbox window during the project's creation.

FIG. 27.9

The grid shows more information at one time.

Next Step

Visual Basic contains a special Data Form Wizard that lets you design advanced forms that you can insert into your applications. These forms offer unique DAO and RDO (Remote Data Objects that might appear on a network) capabilities that you can select by answering the wizard's dialog box questions. The Data Form Wizard is an add-in program that you have to set up before you can use it. (You must also have the Professional or Enterprise VB editions before you can add the Data Form Wizard.)

Perform these steps to add the Data Form Wizard to your Add-Ins menu:

  1. From the Add-Ins menu, choose Add-In Manager to display the list of add-ins you can add to your Visual Basic development environment.
  2. Select the Data Form Wizard and click OK. When you open theAdd-Ins menu again, the Data Form Wizard appears on the menu.
  3. Choose Add-Ins, Data Form Wizard to start the wizard. After reading the opening dialog box, click Next to select a database type. You'll connect the Microsoft Access Biblio.mdb database to the wizard, so keep Access selected and click Next again.
  4. Select the Biblio.mdb database and click Next to display the Form dialog box in Figure 27.10. The three options determine the style of the form you want the wizard to generate.

FIG. 27.10

Select the kind of form you want the wizard to create from the data.

  1. The Single Record style displays records one form at a time and is most useful for adding new records to the database from the application. Grid presents the grid view (called the datasheet view) of the data in a worksheet format like you saw in Figure 27.9. The Master/Detail view provides an interesting combination for the data display. As you click each option, the dialog box updates its icon to show you what the resulting form will look like.
  2. Select Master/Detail and click Next. The next dialog box determines which table and fields you want to see in the resulting application's form. Select the Publishers table and then move the following fields to the Selected Fields list: PubID, Company Name, and State.
  3. You've entered the Master section of the form, and you must now click Next to move to the next dialog box and enter the Detail section of the form. The Master section will show one selected record, and the Detail section will list the multiple records beneath the Master record.
  4. Select the Titles table for the Detail section. A title list will appear beneath the publisher shown in the upper half of the form. Send these fields to the Selected Fields list: Title, Year Published, ISBN, and PubID.
  5. Select the Titles field in the Column to Sort By list box. The form will display the titles in alphabetical order by title and not in the table's actual order, which may be different. Click Next to move to the next dialog box.
  6. The Record Source Relation dialog box lets you connect the Master section to the Details section by selecting a common field. Click the PubID field in each column, and then click Finish to complete the wizard and generate the project.

The Data Form Wizard generates only the form, not a complete project. Therefore, you can add the form into whatever project that needs the form. To try the form, choose Properties from the Project menu and change Startup Object to frmTitles. When you run the application, you'll see the resulting Master/Detail form. As you click through the records, publishers with multiple titles in the database will appear as shown in Figure 27.11. Although you normally wouldn't show the PubID field (because the field will contain the same value for each record), you can see now how the PubID field connects the Master record to the Detail record.

FIG. 27.11

The Master and Detail record are synchronized.

After designing the form, you can save it and add it to future projects that need access to the data.

Summary

This lesson walked you through the ins and outs of database access with Visual Basic. The simplest way to access a database (assuming that you don't use a wizard) is to add the Data control to your form. The Data control takes care of updating the underlying database and changing the bound controls as you move between records.

The DAO VB interface requires extensive programming, but you get much more control and flexibility over your database access. Your application must update controls and move between records as users trigger these events, but you gain much faster database applications.

After you create database forms with the Data Form Wizard, your applications immediately take on a professional form appearance. The forms that the wizard creates will take care of all the tedious updating and maneuvering between records in the underlying tables.

In Hour 28, "Keyboard and Mouse I/O," you'll learn how your applications can take control of the keyboard and the mouse.

Hour 27 Quiz

  1. What tool does Visual Basic supply that lets you edit and look at database files?
  2. What is meant by a bound control?
  3. True or false: A table is a subset of a recordset.
  4. What's the difference between the Set statement and the assignment statement?
  5. What are the differences between a dynaset, a recordset, and a snapshot?
  6. Name two advantages of DAO over a Data control.
  7. Unlike variable names, a database field name can contain spaces. How do you specify field names inside DAO statements that include spaces in their names?
  8. What do the EOF and BOF values determine?
  9. What must you do to your development environment before using the Data Form Wizard?
  10. What's the difference between a Master view and the Detail view in a data form?

Hour 27 Homework Exercises

  1. Change the example program from Topic 1 to show three additional fields from the Titles table in the Biblio.mdb database. Make sure that all fields change when users change the position of the Data control. You can load the example from this book's CD-ROM if you didn't enter the code during this lesson.
  2. Add error correction to Topic 2's example DAO database access.
  3. Create a Master/Detail database form from the Biblio.mdb database that presents author information in the Master view and the author's books in the Detail view.

© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.