accessing and manipulating database using ADO

Figure 25.1: The Runtime Interface

　

 The property settings of all  the controls are listed as in Table 25.1 below:

Table 25.1: Property Settings

Control Property	Setting
Form Name	frmBookTitle
Form Caption	Book Titles -ADOApplication
ADO Name	adoBooks
Label1 Name	lblApp
Label1 Caption	Book Titles
Label 2 Name	lblTitle
Label2 Caption	Title :
Label3 Name	lblYear
Label3 Caption	Year Published:
Label4 Name	lblISBN
Label4 Caption	ISBN:
Labe5 Name	lblPubID
Label5 Caption	Publisher's ID:
Label6 Name	lblSubject
Label6 Caption	Subject :
TextBox1 Name	txtitle
TextBox1 DataField	Title
TextBox1 DataSource	adoBooks
TextBox2 Name	txtPub
TextBox2 DataField	Year Published
TextBox2 DataSource	adoBooks
TextBox3 Name	txtISBN
TextBox3 DataField	ISBN
TextBox3 DataSource	adoBooks
TextBox4 Name	txtPubID
TextBox4 DataField	PubID
TextBox4 DataSource	adoBooks
TextBox5 Name	txtSubject
TextBox5 DataField	Subject
TextBox5 DataSource	adoBooks
Command Button1 Name	cmdSave
Command Button1 Caption	&Save
Command Button2 Name	cmdAdd
Command Button2 Caption	&Add
Command Button3 Name	cmdDelete
Command Button3 Caption	&Delete
Command Button4 Name	cmdCancel
Command Button4 Caption	&Cancel
Command Button5 Name	cmdPrev
Command Button5 Caption	&<
Command Button6 Name	cmdNext
Command Button6 Caption	&>
Command Button7 Name	cmdExit
Command Button7 Caption	E&xit

To be able to access and manage a database, you need to connect the ADO data control to a database file. We are going to useBIBLIO.MDB that comes with VB6. To connect ADO to this database file , follow the steps below:

a) Click on the ADO control on the form and open up the properties window.

b) Click on the ConnectionString property, the Property Pages dialog box will appear, as shown in Figure 25.2.

Figure 25.2: Property Pages

When the dialog box appear, select the Use Connection String's Option. Next, click build and at the Data Link dialog box, double-Click the option labeled Microsoft Jet 3.51 OLE DB provider.

Figure 25.3: Data Link Properties

 After that, click the Next button to select the file BIBLO.MDB. You can click on Text Connection to ensure proper connection of the database file. Click OK to finish the connection.

Finally, click on the RecordSource property and set the command type to adCmd Table and Table name to Titles. Now you are ready to use the database file.

Figure 25.4

Now, you need to write code for all the command buttons. After which, you can make the ADO control invisible.

　

　

For the Save button, the program codes are as follow:

Private Sub cmdSave_Click()

adoBooks.Recordset.Fields("Title") = txtTitle.Text
adoBooks.Recordset.Fields("Year Published") = txtPub.Text
adoBooks.Recordset.Fields("ISBN") = txtISBN.Text
adoBooks.Recordset.Fields("PubID") = txtPubID.Text
adoBooks.Recordset.Fields("Subject") = txtSubject.Text
adoBooks.Recordset.Update

End Sub

For the Add button, the program codes are as follow:

Private Sub cmdAdd_Click()

adoBooks.Recordset.AddNew

End Sub

For the Delete button, the program codes are as follow:

Private Sub cmdDelete_Click()

Confirm = MsgBox("Are you sure you want to delete this record?", vbYesNo, "Deletion Confirmation")
If Confirm = vbYes Then
adoBooks.Recordset.Delete
MsgBox "Record Deleted!", , "Message"
Else
MsgBox "Record Not Deleted!", , "Message"
End If

End Sub

For the Cancel button, the program codes are as follow:

Private Sub cmdCancel_Click()

txtTitle.Text = ""
txtPub.Text = ""
txtPubID.Text = ""
txtISBN.Text = ""
txtSubject.Text = ""

End Sub

For the Previous (<) button, the program codes are

Private Sub cmdPrev_Click()

If Not adoBooks.Recordset.BOF Then
adoBooks.Recordset.MovePrevious
If adoBooks.Recordset.BOF Then
adoBooks.Recordset.MoveNext
End If
End If

End Sub

For the Next(>) button, the program codes are

Private Sub cmdNext_Click()

If Not adoBooks.Recordset.EOF Then
adoBooks.Recordset.MoveNext
If adoBooks.Recordset.EOF Then
adoBooks.Recordset.MovePrevious
End If
End If

End Sub

==============================

In this sample, you will create a ADO database application to manage your home library.  First of all, create a database in MS Access and name home_Library. In this database, create a table with the following field names;

Title:

Author:

Publisher:

Year:

Category:

and save the table as booktitle.mdb

　

Design the Interface as follow:

Key in the codes as follows:

Private Sub cmdCancel_Click()
txtTitle.Text = ""
txtAuthor.Text = ""
txtPublisher.Text = ""
txtYear.Text = ""
txtCategory.Text = ""
End Sub

Private Sub cmdDelete_Click()
Confirm = MsgBox("Are you sure you want to delete this record?", vbYesNo, "Deletion Confirmation")
If Confirm = vbYes Then
adoLibrary.Recordset.Delete
MsgBox "Record Deleted!", , "Message"
Else
MsgBox "Record Not Deleted!", , "Message"
End If
End Sub

Private Sub cmdExit_Click()
End
End Sub

Private Sub cmdNew_Click()
adoLibrary.Recordset.AddNew
End Sub

Private Sub cmdNext_Click()
If Not adoLibrary.Recordset.EOF Then
adoLibrary.Recordset.MoveNext
If adoLibrary.Recordset.EOF Then
adoLibrary.Recordset.MovePrevious
End If
End If
End Sub

Private Sub cmdPrevious_Click()
If Not adoLibrary.Recordset.BOF Then
adoLibrary.Recordset.MovePrevious
If adoLibrary.Recordset.BOF Then
adoLibrary.Recordset.MoveNext
End If
End If
End Sub

Private Sub cmdSave_Click()
adoLibrary.Recordset.Fields("Title") = txtTitle.Text
adoLibrary.Recordset.Fields("Author") = txtAuthor.Text
adoLibrary.Recordset.Update
End Sub