Chapter 17

Database Access

• Visual Basic Data Access
  • Using a Microsoft Access Database
  • Other Data Sources Accessible to the Data Control
  • More Choices: Using Open Database Connectivity
  • Data-Aware Controls in Visual Basic
  • How Database Access Works
• Providing Data Awareness in Your ActiveX Control
• Summary

For business users, database access is one of the most compelling - and easy-to-implement features of Visual Basic. Even if you're not a captain of industry, using VB as a way to display and edit information in a database is easy and powerful.

This chapter is split into two sections. The first section deals with how data access works in Visual Basic. The second deals with how to set up your ActiveX control to connect to a database.

Visual Basic Data Access

There are a number of ways to connect your application to a database in Visual Basic. This chapter is concerned with the simplest: connecting ActiveX controls to a database using the Visual Basic Data control.

But if you've never used Visual Basic before, you should know that there are a number of other database programming topics not directly related to control creation. Briefly summarized, they are:

• Data Access Objects (DAO)-An object model for referring to databases in Visual Basic code. Using DAO, you can create Recordset objects, which can then be manipulated programmatically. For example, you might use DAO to create a custom database report in Visual Basic or perform regular automated queries based on preprogrammed criteria.
• Remote Data Objects (RDO)-A way of accessing client-server data from Visual Basic. Remote data objects are geared toward data processing rather than the user-interface. Visual Basic provides a custom control, the RemoteData control, for accessing client-server databases; the Data control is to the RemoteData control as DAO is to RDO.

Using a Microsoft Access Database

Database access has been a part of VB's feature set for quite some time. Since VB 3.0, Visual Basic has included the Microsoft Access database engine, which gives you a number of benefits:

• You can use Access's powerful and intuitive development environment to develop a database.
• Both Microsoft Access and Visual Basic users can access your database simultaneously.
• If you're an experienced Access developer, you will find that much of the code you've written in Access is usable in Visual Basic. This is because Access includes a dialect of Visual Basic known as Visual Basic for Applications. In many cases you can copy and paste code directly between Access and Visual Basic.

As of the time you're reading this, the current version of Access is Microsoft Access 97; it is part of the Microsoft Office 97 suite. Visual Basic has the ability to access Microsoft Access databases created with versions of Access as early as Access 2.0.

Other Data Sources Accessible to the Data Control

If for some reason you can't or don't wish to use a Microsoft Access database, you have a plethora of other options. The Access engine supports a number of other database formats as well, such as Paradox and the dBASE format used by FoxPro and a number of other database management applications.

You use the Connect property of the data control to designate which type of database you wish to connect to. Choices of database formats provided by the Connect property include:

• Microsoft Access (the default)
• dBASE III, IV, or 5.0
• Excel versions 3.0 to 7.0
• FoxPro versions 2.0 through 3.0
• Lotus WK1, WK3, and WK4 formats
• Paradox versions 3.0 through 5.x
• Text files

More Choices: Using Open Database Connectivity

If the native database formats available to you aren't enough, you can also connect to a database using a Windows technology called Open Database Connectivity (ODBC). ODBC provides a set of drivers that enables your application to access a data source no matter what format that data source is in. You can access any ODBC data source from a Visual Basic Data control.

If your application needs to access a client-server database (such as Oracle or Sybase), an ODBC driver is one part of the equation that connects your application to the database. But ODBC isn't just for client-server connections; there are ODBC drivers for many applications that store and process data-even for applications that are not relational databases. For example, there is an ODBC driver for Microsoft Excel, enabling you to access data stored in an Excel spreadsheet as if it were stored in a relational database. (The Microsoft Excel format is also supported natively by the VB data control as well.)

The good news is that from the control creation perspective, none of this matters. This is because the data source is abstracted at such a high level that your control only sees a record source and a field, as you'll see in the sections that describe data-aware controls later in this chapter.

Data-Aware Controls in Visual Basic

Visual Basic comes with a number of data-aware controls. You can use these controls as examples of how data-aware controls behave; you can also use these controls as constituent controls of your control project. The data-aware controls that come with all versions of Visual Basic are:

• TextBox
• PictureBox
• Label
• CheckBox

The Professional and Enterprise editions of Visual Basic include these additional data-aware controls:

• RichTextBox
• DBGrid
• DBList
• DBCombo

There are also a number of data-aware third-party controls. For example, data-aware grids, which can display two-dimensional lists of data displayed in rows and columns, are particularly abundant.

How Database Access Works

You connect an ActiveX control to a database through the Data control. The Data control handles the connection between the application and the database, while the ActiveX control displays the data and permits the user to edit the data that it displays.

The Visual Basic Data control is illustrated in Figure 17.1.

Figure 17.1 : Visual Basic Data control.

You only need to set two properties of the Data control to connect it to a data source: the DatabaseName and RecordSource properties. These properties are outlined in Table 17.1.

In addition to providing a connection to the database, the Data control provides a user interface for a form that contains data-aware controls. The four buttons in the Data control are illustrated in Figure 17.2.

Figure 17.2 : Data control buttons.

When the user is navigating through the recordset using the Data control, she has the ability to edit records (unless the Data control's ReadOnly property has been set to True). When an edit occurs, the edit is saved to the database as soon as the user moves to a different record or when your application executes the Update method of the Data control's Recordset object.

In addition to navigating through the recordset, you can use the Data control to create a new record. When the Data control's EOFAction property is set to 2-New Record, you can create a new record by clicking on the Move Last button, then clicking on the Move Next button. This behavior mimics the behavior of the navigation buttons in Microsoft Access, which also create new records when the user moves past the last record in a recordset.

Properties of Data-Aware Controls

A control that can be connected to a data control is said to be bound or data-aware. Data-aware controls expose the DataSource and DataField properties, as shown in Table 17.2.

When a control is bound to a data control, the data displayed is dependent on what's in the database. For example, a bound TextBox control's Text property is almost never set by the developer. Instead, the developer sets the DataSource and DataField properties and the Text property is set by the Data control.

So in summary, the steps a developer goes through to create a database application based on the Visual Basic Data control are:

1. Create a Data control on a form.
2. Set the Data control's DatabaseName property to point to the location of the database file.
3. Set the Data control's RecordSource property to refer to the database table or query you wish to use.
4. Create one or more bound controls on the form.
5. Set the controls' DataSource property to refer to the Data control.
6. Set the controls' DataField property to refer to the field in the database.

One of the advantages of setting up a database application in this way is that you can often create an application that requires a grand total of zero lines of code to provide basic functionality.

Providing Data Awareness in Your ActiveX Control

Giving your ActiveX control the ability to connect to a data control is a fairly straightforward process involving the ever-popular Procedure Attributes dialog box.

To demonstrate this, you'll create a data-aware control that will graphically display the value of a database field. This control will be used by an ice cream factory to provide a visual display of different types of ice cream for a production-tracking database application. To do this:

1. Create a new control project. Give the UserControl the name DataPicture.
2. Add a constituent Picture control to the control designer. Give this control the name picDisplay.
3. Change the AutoSize property of the control to True. Change the BorderSize property of the control to 0 None. Set the Picture control of the property to the file type.bmp, found on the CD-ROM that accompanies this book.
4. Set the Top and Left properties of picDisplay to zero.
5. Add four more Picture controls to the control designer. Name them picChocolate, picIce, picSherbert, and picNull.
6. Set the Picture properties of the four new controls to chocolate.bmp, ice.bmp, sherbert.bmp, and type.bmp, respectively.
7. Set the Visible property of picChocolate, picIce, picSherbert, and picNull to False. This will make the controls invisible at runtime. Your control designer should look something like Figure 17.3.

Figure 17.3 : DataPicture control designer.

Next you'll enter code to make the picture displayed by the control change according to the contents of the database field. To do this, enter the following code in the Click event of picDisplay:

' Declarations
Private mvDataValue As Variant

Public Property Get DataValue() As Variant
    DataValue = mvDataValue
End Property

Public Property Let DataValue(ByVal vNewValue As Variant)
    mvDataValue = vNewValue
    Select Case vNewValue
        Case Null
        picDisplay.Picture = picNull.Picture
        
        Case 0
        picDisplay.Picture = picChocolate.Picture
        
        Case 1
        picDisplay.Picture = picIce.Picture
        
        Case 2
        picDisplay.Picture = picSherbert.Picture
        
        Case Else
        picDisplay.Picture = picNull.Picture
        
    End Select
    
    ' ***** Very important for data-bound properties
    PropertyChanged "DataValue"
    
End Property

You can see that this code simply switches the picture displayed by picDisplay to the appropriate type of ice cream. There are no new concepts in this code, although you'll want to note that the PropertyChanged statement (which you should include in a Property Let statement anyway) is particularly important. This is because it triggers a database update in a data-aware control.

Next, add code that will enable the user to change the value of the field to which the control is bound. For this control, the user can change the value of the field by clicking on the control, so enter the following code in picDisplay's Click event:

Private Sub picDisplay_Click()
    Select Case mvDataValue
        Case 0
        DataValue = 1
        
        Case 1
        DataValue = 2
        
        Case 2
        DataValue = 0
        
        Case Else
        DataValue = 0
        
    End Select
    
    PropertyChanged "DataValue"
    
End Sub

This procedure also contains nothing conceptually new; it simply cycles through the available pictures, changing the value of the database field each time the user clicks on the control until the user has selected the value he wants.

Now enter a Resize event for the UserControl. This procedure will resize the UserControl to conform to the dimensions of the constituent picture control picDisplay:

Private Sub UserControl_Resize()
    ' Make the UserControl match picDisplay
    ' because picDisplay is AutoSized
    UserControl.Height = picDisplay.Height
    UserControl.Width = picDisplay.Width
End Sub

Finally, to enable your control to connect to a data control, you must designate one of the properties of your control as bindable. Binding a control to a field in the database means that whenever the bound property of your control changes, Visual Basic writes the new value back to the database field to which your control is bound.

To mark the DataValue property as bindable to a database field, do the following:

1. Select the menu command Tools, Procedure Attributes.
2. The Procedure Attributes dialog box appears. Click on the Advanced button. The dialog box expands.
3. Select the checkbox labeled Property is data bound. Then select the checkbox labeled This property binds to data field. The Procedure Attributes dialog box looks like Figure 17.4.
   Figure 17.4 : Procedure Attributes dialog box.
   
   
4. Click on OK. The DataValue property is marked as a bindable property.

Your data-aware control is now complete. To test it:

1. Close the code window and close the control designer.
2. Open the EXE form.
3. Place a Data control on the EXE form. Remember, the Data control is only available in the Professional or Enterprise edition of Visual Basic; if you only have Visual Basic 4.0, that will suffice.
4. Set the Data control's DatabaseName property to icecream.mdb. This Microsoft Access database is on the CD-ROM that accompanies this book.
5. Set the Data control's RecordSource property to tblIceCream.
6. Add three labels and three text boxes to the form so that it looks like Figure 17.5. Name the text boxes txtFactory, txtDate, and txtQuantity.
   Figure 17.5 : Running data-Access application.

7. Set the DataSource property of all three text boxes to Data1.
8. Set the DataField property of txtFactory to the field Factory. Set the DataField property of txtDate to the field Date. Set the DataField property of txtQuantity to the field Quantity.
9. Run the application to make sure that everything is hooked up correctly. You should be able to browse data from the database through the interface you've just built, as illustrated in Figure 17.5, above.
10. Now add an instance of the DataPicture control to the form. Set the control's DataSource property to Data1, and set its DataField property to IceCreamType.
11. Run the application. You should be able to see that for each production record, a different type of ice cream appears. The value that is stored in the database field is a long integer, but the value displayed by the control is a type of ice cream, as illustrated in Figure 17.6.
    Figure 17.6 : Application with DataPicture control.
    
    
12. In one of the records, click on the DataPicture control to see that you can change the ice cream type. If you click several times, the control cycles through the available types. Any change you make to the data is saved to the database.

Summary

In this chapter, you learned about how database access works in Visual Basic. We went through the various options for hooking up to a database available to the Visual Basic developer and demonstrated how to add database features to your ActiveX control.