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.
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.
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.
Have you noticed that we have yet to write ANY code?
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:
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!
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.
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.
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.
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
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