|
|
|
|
Connecting to a Database using Microsoft Access Visual Basic supports connections to databases using various different methods. Corporations may tend to using something called SQL (Structured Query Language). However, to make things easier and more simple, we'll be connecting our project to a Microsoft Access database. To do this, we're going to need to use something known as ADO. What this does is it enables us to easily pull records from our Access database and display them in VB. If you don't know what a record is then take a look at the following table of definitions:
This illustration should help make the above definitions more clear:
The ADO (ActiveX Data Object) Data Control is a very well-built Microsoft control that makes adding, removing and updating data (which is pulled from a data source: Access in this case) extremely easy. In order to achieve this sort of connection between a database and your VB project, two main steps should be completed: 1. Setting up an ADO Data Control 2. Binding it to your Access database If you don't have any experience with Access, then you can download a sample database for use with this lesson.
1. Setting up an ADO Data Control Click
on Project, and then Components. Place a tick next to Microsoft
ADO Data Control 6.0 (OLEDB) and click on OK. Now click on the ADO
Data Control
The first thing we need to do is specify the database that we want to use and the connection method. This is done through the ConnectionString property of the ADO Data Control. Click on the "..." next to the ConnectionString property. Select the Use Connection String option and click on Build. Switch to the Provider tab and choose Microsoft Jet 3.51 OLE DB Provider and click Next. We're choosing this option since our data source will be Access. Now under number 1 click on the "..." and locate your database. If you don't have one, you can download a sample database from the link above. Leave everything else alone and click on Test Connection. You should get a message box informing you that the connection succeeded. Click OK and then again OK to close all the windows. The next thing to do is to set the RecordSource property. Click on the "..." next to RecordSource and you'll get a new window. Here, you'll need to set the Command Type. There are several different options in the drop down list, however, we'll choose adCmdTable since we want to connect to a database with a table and several columns. In the list below it, choose the Employees table. This is a table within our sample Access database. We are now done with setting up our connecting and are ready to bind our database's fields to certain "data-aware" bound controls, such as text boxes.
2. Binding your Data Control to a Database Binding database fields to controls on our project is like mapping each field to a control, resulting in a user-friendly display of our database contents. It is always best to use data-aware controls because they require less code. Here is a list of the most common data-aware controls in VB:
Continuing with our current project, we'll use three text boxes to bind three fields from our database. Insert three long text boxes above your ADO Data Control and name them: txtField1 txtField2 txtField3 Also, insert three labels on the form, each next to a text box. See the picture below and enter the same captions for your three labels:
Now click on txtField1 and set its DataSource property to Adodc1, which is the name of our ADO Date Control. Next, set its DataField property to Employee ID. The DataField property displays a specific field from the database in the selected control. Do the same for the second and third text box, but this time choose First Name for txtField2's DataField and Last Name for txtField3's DataField. Save your work and run your project. Try pressing the left and right arrows and you should get the specified fields from the database. Really nice!
There are many things you can do with your ADO Data Control like adding, updating, deleting and refreshing your data fields. However, that would need a whole new lesson. Briefly, here are some of the commands that you can try with your ADO Data Control's recordset property: Adodc1.Recordset.Update: Update's the current record Adodc1.Recordset.AddNew: Allows you to add a new record Adodc1.Recordset.Delete: Deletes the current record Adodc1.Recordset.MoveNext: It's a good idea to move to the next record after deleting the current one. |
and place it somewhere on you form. It should look like this:
