Visual Basic Data Control, Part One

.

The Visual Basic Data Control can be used to write VB programs that can easily manipulate databases created with Microsoft Access, FoxPro, dBase, Paradox, Btrieve, Excel, or any ODBC*-compliant database, such as Oracle and SQL Server databases. (*Open DataBase Connectivity). Visual Basic's native database format is Access, and the Access "Jet" Database Engine is included with VB.

The Data Control has two key properties: DatabaseName is the filename of a compatible database. RecordSource is the name of a table in the database, or the result of a SQL (Structured Query Language) query.

Many VB controls are "data-aware", and can be "bound" to the Data Control. That is, as you move from record to record using the Data Control, the bound controls show you the field contents of each record. Bound controls generally have a DataSource property, which should be the name of the Data Control, and a DataField property, which is the name of the field you want displayed in that control.


Creating a Database

Visual Basic uses the Microsoft Access database engine. Use Microsoft Access to create a new database, or use the Data Manager application provided with Visual Basic. You can launch Data Manager directly from VB, from the Add-Ins menu.

You can also use databases created with dBase III, dBase IV, FoxPro 2.0 or 2.5, Paradox, Btrieve, Excel, or any ODBC-registered source. To use an option other than Access, you need to set the Connect property for the Data Control. For details, search Visual Basic's Help file for the Connect Property.

Office 2000 note:

Visual Basic is not easily compatible with Access 2000's database format. If you create a database using Access 2000, one of the menu items in Access is "Save As Previous Version". Use that to save in Office 97 format.

There IS a way to open Access 2000 databases in VB, but it involves extra code that will make these first two database lessons more cumbersome. For those who need the secret, the notes are at the very bottom of this page.

Trying to use Visual Data Manager

Note: the Data Manager that came with VB3 had a few flaws. Microsoft did a much better job with the new Data Manager in VB4. However, they seem to have lost their minds with the VB5 version. Its interface is "interesting", to put it most mildly.

  1. Launch Visual Data Manager from the VB Add-Ins menu.
  2. Choose File/New..., choose Access 7.0, and give the new database a name.
  3. Right-click in the Database Window. (There appears to be no other way to do this, nor any instructions that would lead you to this. I accidentally found it when I was about to give up!) Click New Table in the pop-up menu. In the resulting dialog box, give the new table a name.
  4. Click the Add Field button. Enter a field name and field type. Click the Okay button to add the new field to the list. Keep repeating for a few more fields. When done, click Close to stop adding fields and then close the table dialog.
  5. In the Database Window, you'll see the name of your new table. Double-click to open the table so you can add records to the table. (That was obvious, wasn't it? Again there appears to be no other way to get to this point. If you single-click, you just see a list of properties that are mostly meaningless to you at this point.) You can come back and add more records any time.
  6. Exit VisData. You now have a database file. Phew!

Quick Start with the Data Control

  1. To use a Data Control, first select the control from the toolbox and draw it on your form. Its default name is Data1, which we'll use in the examples below.
  2. In the properties window, set the DatabaseName property to the filename of the database you want. Click on the ellipsis button (...) to use a "file open" dialog.
  3. Set the RecordSource property to the name of a database table within the database you selected. If you've already selected a database as in step 2, a drop-down list of choices will be provided.
  4. Put a Text Box on your form. (You can also use other data-aware controls, such as CheckBoxes, Labels, Image controls, and PictureBoxes.)
  5. Set the DataSource property for the text box to Data1.
  6. Set the DataField property for the text box to the field you wish to display. If steps 2-5 have been accomplished with a valid database, a drop-down list of choices will be provided.
  7. You may wish to put a Label control in front of the text box with the fieldname selected in step 6.
  8. Repeat steps 4-7 for each field you wish to display.
  9. Run your application.

Another Way: Grid View

  1. Start a new project and put a Data Control on the form

  2. Set the DatabaseName property of Data1

  3. Set the RecordSource property

  4. From the Project menu, choose Components. Check either the Microsoft FlexGrid control or the Microsoft DataBound Grid control. The FlexGrid is read-only, but it has features such as data-pivoting. The DataBound Grid control allows the user to edit its fields.

  5. Put a DBGrid or FlexGrid control on your form. Make it fairly large.

  6. Set the DataSource property of the grid to Data1

  7. Run your program

Have you noticed that we have yet to write ANY code?


The Data Control's RecordSource and RecordSet Properties

The RecordSource tells where the data will come from. You can think of the Recordset property as the actual data that you've requested using the RecordSource.

The RecordSource can be the name of a table in a database, the name of an Access database query, or the text of a SQL query. If you change the RecordSource at run-time, you must use the method Data1.Refresh to actually go out and get the data and assign it to the RecordSet.

Once you have a RecordSet, you can use the following methods in your program:

RecordSet Methods
Data1.RecordSet.AddNew
adds a new record to the recordset
Data1.RecordSet.Delete
deletes the current record
Data1.RecordSet.MoveNext
moves to the next record in the recordset
Data1.RecordSet.MovePrevious
moves to the previous record
Data1.RecordSet.MoveFirst
moves to the first record
Data1.RecordSet.MoveLast
moves to the last record
Data1.UpdateRecord
saves a changed record. This is also done automatically when you use any of the Move methods. With VB6, this is also done automatically when the control is unloaded (when your application exits).

RecordSet Properties

Data1.RecordSet.EOF
True if MoveNext has moved us past the last record
Data1.RecordSet.BOF
True if MovePrevious has moved us in front of the first record
Data1.RecordSet.RecordCount
The number of records in the RecordSet. This is accurate with small RecordSets. For large RecordSets you may have to perform a MoveLast to get this actual value.
Data1.RecordSet.AbsolutePosition
The number of the current record in the RecordSet. Starts at zero. This property does not appear to be reliable in VB6. I put code to display this in the Data Control's Reposition event, and sometimes it worked, sometimes it gave odd results.
Data1.RecordSet!fieldname
The contents of a specific field in the current record of the RecordSet. Replace fieldname with the valid name of a field.

Yet Another (Bad) Way to Get Started: The Dataform Designer

Visual Basic 4 provided one other way to generate the beginning of a database application: the Dataform Designer. It automatically creates a form, similar to what we created with the first example, and includes some code for adding and deleting records. However, you should plan to make some code modifications if you use this.

In Visual Basic 5, they've taken this component and put it in .... VisData. Yikes! Read on to the last paragraph of this section before you spend a lot of time with this: the code generated by Dataform Designer has serious flaws in both VB4 and VB5!

  1. From the Add-Ins menu, choose Visual Data Manager again.
  2. In Visual Data Manager, choose File/Open Database, and open a database file, such as the one you created a few minutes ago.
  3. In the Utility menu, choose DataForm Designer...
  4. Fill in a name for your form at the top of the Dataform Designer dialog, and set the RecordSource to a (the) table in your database.
  5. Click the >> button to add all fields to this form.
  6. Click on Build the Form. When it is done, Close the dialog.

If you set this form to be your startup form (in VB5, Project menu/Project Properties/General/Startup Object), you can now run the program!

If you inspect the code, you will find that the Dataform Designer created event procedures for clicking each of the five command buttons, plus procedures for the events Data1_Error, Data1_Reposition, and Data1_Validate. Unfortunately, their code allows the user to easily crash the program, and the Update button is very misleading. (Update usually occurs whether you click it or not!). I recommend deleting the Refresh and Update buttons, altering the code for the cmdAdd and cmdDelete click events, and adding Form_Load and Form_Unload events. The recommended code is given a few sections below.

Data Control Events: Error, Reposition, Validate

The Error event handles any error that occurs with the database. Use this event to code what you want to happen when an error occurs.

The Reposition event occurs when the user moves to another record in the RecordSet.

The Validate event occurs prior to updates, either because of moving to another record or explicitly asking for an update to occur. Use this event to do any validation on any of your form fields prior to writing to the database. If a field is invalid, you can cancel the update and the action that caused it by setting Action=0 and Save=False.


Basic Code for the Data Control

Although browsing and editing require no code, you must write code to add and delete records. Furthermore, you should add a couple lines of code in your Form_Load event to avoid an empty Recordset.

A problem that has plagued VB from the original database version is how it handles "no current record". There are several situations where your "form" can be blank. It appears to be an empty record or a record in which you can enter data, but it isn't! There's no current record displayed. Information you type won't be saved, and trying a "delete" will crash your program. It's important to avoid those situations.

Before VB6, when adding a record, if you didn't immediately UpdateRecord, the user could crash the program by clicking Delete. They changed that in VB6. Now the program doesn't crash.... instead you wind up deleting some other record!

Here is safe code for adding and deleting. The only issue you may have is if your database has required fields. Before you use the UpdateRecord command, you should assign default data to those fields. Otherwise, you must use an alternate method for adding a new record.

The code below assumes you have Command Buttons for Add and Delete.

Private Sub cmdAdd_Click()
    ' Add a blank record and save it so it is the current record. It gets put at the end.
    Data1.RecordSet.AddNew
    ' Put code here if you have required fields in your database. Assign default values.
    Data1.UpdateRecord
    Data1.Recordset.MoveLast
End Sub

Private Sub cmdDelete_Click()
    Data1.RecordSet.Delete
    ' Now attempt to move forward to a valid record:
    Data1.RecordSet.MoveNext
    ' But if we just deleted the Last record, we need to move back instead:
    If Data1.RecordSet.EOF Then
        Data1.RecordSet.MovePrevious
        ' Now check to see if we've moved off the front...
        ' if so, we've just deleted the ONLY record! So add a new one...
        If Data1.RecordSet.BOF Then
            Data1.RecordSet.AddNew
            Data1.UpdateRecord
            Data1.Recordset.MoveLast
        End If
    End If
End Sub

Private Sub Form_Unload()
    ' add the following to Form_Unload to make sure any last changes are saved:
    Data1.UpdateRecord
End Sub

This "UpdateRecord" in the Form_Unload event may not be necessary in VB6. In earlier versions it was needed, but the VB6 Data Control appears to automatically update the last record when you exit the program.

If you have an Exit button, or a File menu with the Exit option (you should!), you should use the line "Unload Me" to exit your program. In earlier programs it did not matter if we used the other choice: "End". Now it does.


DatabaseName and Form_Load

There are two things you should add to Form_Load. First, when you are done editing the properties of the controls on your form, you should go back to the Data Control and set the DatabaseName property to blank. While setting the DatabaseName property makes it convenient at design-time (table and field names in the database automatically show in drop-down lists), the path and filename for the database get written to your EXE file unless you clear DatabaseName. If your database is in a directory called "C:\VBPROGS\DATATEST", when you install your EXE on someone else's machine, it will look on their hard disk for that same directory name!

The better way is to clear DatabaseName prior to making your EXE, install your database file into the same directory as your EXE file, and use App.Path to set the DatabaseName at run-time.

The following code for Form_Load does that. It assumes a database file named "MYDATA.MDB". Use whatever is appropriate. The one other check made by Form_Load is that the RecordSet is not empty. If it is, a new record is added.

Private Sub Form_Load()
    Dim DBPath as String
    DBPath = App.Path
    If Right(DBPath,1) <> "\" Then DBPath = DBPath & "\"
    Data1.DatabaseName = DBPath & "mydata.mdb"
    Data1.Refresh

    If Data1.RecordSet.EOF Then
        Data1.RecordSet.AddNew
        Data1.UpdateRecord
        Data1.Recordset.MoveLast
    End If
End Sub

More Database Programming Tips

Accessing data directly, without a bound control:

	Dim s as String
	' assign the value of a ZipCode field to s:
	s = Data1.Recordset!ZipCode

Looping through the whole recordset:

	' Perform some operation on every record in the recordset
	Data1.Recordset.MoveFirst
	Do While Data1.Recordset.EOF = False
		' Insert your code here to do whatever to this record:


		Data1.Recordset.MoveNext
	Loop

Office 2000 notes (only for those who absolutely need to know how to do this. Otherwise, ignore.)

To use an Access 2000 database (if you choose not to "Save As Previous Version" in Access), you should do the following:

(1) Under Project/References in Visual Basic, click on Microsoft DAO 3.6, and de-select any previous version of the DAO (Data Access Object).

(2) In General Declarations, add the following:

    Private db As Database, rs As Recordset

(3) Do NOT set the DatabaseName property of the Data Control.

(4) In Form_Load, add the following:

    Set db = DBEngine(0).OpenDatabase("mydatabase.mdb") 
     'replace mydatabase with the path and name of your Access 2000 mdb file
    Set rs = db.OpenRecordset("mytable")
    ' replace mytable with the name of a table in your database
    Set Data1.Recordset = rs

Note: If you choose this method, you will also have to use rs.Close and the last two lines (Set rs... and Set Data1...) when you use SQL queries in the next database lesson.




Back to this lessonCourse Index ©1996-2000, Mark Pelczarski