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.
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:
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:
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.
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:
|Database Access And The Control Creation Edition|
Although the Visual CCE enables you to create controls that are data-aware, it doesn't include database access. This can put you in the unamusing position of being able to create a control that you cannot test. The control may work fine; you just can't connect it to a data control to prove this fact for yourself, at least not in the CCE.
Database access is available in the Professional and Enterprise editions of Visual Basic. If you're a user of the Professional or Enterprise editions of Visual Basic 4.0 and you haven't upgraded yet, you're in luck, because you can use those editions of VB to test your control. (And, of course, you can use the control in any of the retail editions of Visual Basic 5.0.) It is a hassle, yes, because you have to switch back and forth between two different versions of VB, but whaddya want for nothing?
Even if you don't have an edition of VB that can test your data-aware control, you can still follow along through this chapter to learn how to create such a control. You'll just need to skip over the parts where you test your control.
These data sources are the sources that were supported natively by the Visual Basic 4.0 data control. It's likely that this list will expand somewhat in the shipping version of VB 5.0, possibly (for example) to include data files created by Microsoft Excel 97.
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.
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:
The Professional and Enterprise editions of Visual Basic include these additional data-aware controls:
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.
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.
|DatabaseName||The name of the database. This can be a file on disk (for a Microsoft Access database), a directory (for a dBASE-style database), or an ODBC data source (for a client-server database or other type of database not directly supported by Visual Basic).|
|RecordSource||The name of the table in the database to which the Data control is connected. For relational databases, this can be the name of a table, a query stored in the database, or a text string in Structured Query Language (SQL).|
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.
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.
|DataSource||The name of the Data control that maintains the connection to the database|
|DataField||The name of the field of the table in the database to which the control is bound|
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:
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.
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:
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:
At this point, if you're going to test your control in a container other than Visual Basic 5.0, you'll need to compile your control using the File, Make menu command.
Your data-aware control is now complete. To test it:
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.