Early on in our VB class we learned how to use VB to open a database and retrieve records from it.

Now we will add to that basic application many of the features that you would like to see in a full database interface.

These features include; search, add, delete, and back-up.

Create the interface by adding to your form the following:

1 Data object named datBiblio

1 text box named txtTitle and with the label Book Title

1 text box named txtInfo and with the label Description

1 text box named txtISBN and with the label ISBN

1 text box named txtYear and with the label Year

1 Cmmand button named cmdFind

1 Quit button named cmdQuit

Set the Properties

We used the RecordsetType property to identify our dtabase.

This recordset object includes special properties and methods that let you search for, sort, add, and delete records.

Let’s double click the Find button and add the following code for search.

Private Sub cmdFind_Click()

prompt$ = "Enter the full (complete) book title."

'get the string to be used in the Title field search

SearchStr$ = InputBox(prompt$, "Book Search")

datBiblio.Recordset.Index = "Title" 'use title

datBiblio.Recordset.Seek "=", SearchStr$ 'and search

If datBiblio.Recordset.NoMatch Then 'if no match

datBiblio.Recordset.MoveFirst 'go to first record

End If

End Sub

This event procedure displays a search dialog box to get a search string from the user.

If it finds no match it displays a message and it returns to the first entry.

To add a new record to a database all we need to do is to set the data object’s ReadOnly property to False in design mode.

The we can use the AddNew method in an event procedure to open a new record in the database.

First, add a Command button to the form and name it cmdAdd.

Private Sub cmdAdd_Click()

prompt$ = "Enter new record, and click left arrow button."

reply = MsgBox(prompt$, vbOKCancel, "Add Record")

If reply = vbOK Then 'if the user clicks OK

txtTitle.SetFocus 'move cursor to Title box

datBiblio.Recordset.AddNew 'and get new record

'set PubID field to 14 (this field is required

datBiblio.Recordset.PubID = 14 'by Biblio.mdb)

End If

End Sub

The procedure first displays a dialog box containing data entry instructions for the user.

The MsgBox function uses the vbOKCancel argument to display a dialog box that has an OK and Cancel buttons.

The procedure also uses the SetFocus method to place the cursor in the Title text box.

The last statement in the If decision structure uses program code to set the PubID field in the Biblio.mdb database.

Each new record requires this field.

You may now add records to your database.

First let’s add a delete command button named cmdDelete.

To delete a record you display the record you want and then use the delete method to remove it.

We will also use the MoveFirst method to display the first record in the database.

Private Sub cmdDelete_Click()

prompt$ = "Do you really want to delete this record?"

reply = MsgBox(prompt$, vbOKCancel, "Delete Record")

If reply = vbOK Then 'if the user clicks OK

datBiblio.Recordset.Delete 'delete current record

datBiblio.Recordset.MoveNext 'move to next record

End If

End Sub

Delete Code Explained

If the user presses OK it is deleted. Cancel cancels the operation.

If deleted the Delete method is executed and the next record is displayed by the MoveNext method.

It is often times desirable to create a backup of your database for many reasons.

We can use VB to create that backup copy for us by using the FileCopy statement.

FileCopy makes a separate copy of the file.

The syntax is:

FileCopy sourcepath destinationpath

Where source path is the pathname of the file you want to copy and detinationpath in the file you will create.

The code we will create will go in the Form Load procedure so that the program will prompt us for a backup when we start.

Private Sub Form_Load()

prompt$ = _

"Would you like to create a backup copy of the database?"

reply = MsgBox(prompt$, vbOKCancel, datBiblio.DatabaseName)

If reply = vbOK Then 'copy the database if the user clicks OK

FileNm$ = InputBox$ _

("Enter the pathname for the backup copy.")

If FileNm$ <> "" Then _

FileCopy datBiblio.DatabaseName, FileNm$

End If

End Sub

This procedure displays a message box when the program starts asking to create a backup.

If the user clicks OK, an input box receives the pathname of the backup file and the FileCopy statement copies the file.

We now have a complete Database Interface!