VB and MS-Access Database Connectivity using DAO

 Accessing MS-Access database using DAO

Data control is one of the standard controls used to provide access to data. Data control internally uses DAO to access data. It means when we use data control, we are using DAO to access data.

 Accessing BIBLIO.MDB using data control

Let us understand how to access MS-Access database, BIBLIO.MDB, using data control.

Note: BIBLIO.MDB and NWIND.MDB are two sample databases supplied along with Visual Basic.

 To access the data of AUTHORS table of BIBLIO.MDB:

 1.      Place Data Control on the form.

2.      Invoke properties window and select DatabaseName property and click on three dots displayed on the right of the property.

3.      Visual Basic invokes DatabaseName Dialog Box. Select BIBLIO.MDB file using the dialog box.

4.      The complete path of the file is placed in Properties windows.

5.      Select RecordSource property and click on down arrow to the right of the property.

6.      Visual Basic displays the list of tables from the selected database – BIBLIO.MDB.

7.      Select AUTHORS table from the list of tables.

8.      Change Caption property to “Author Details”

Note: You may also use SELECT command in RecordSource property of the data control.

 9.      Let us now take three text boxes to display author id, name and year born. So, place three text boxes and required labels

Two properties that you have to change to bind a control to data control are – DataSource and DataField.

Change the properties of three textboxes as follows:

Control	Property	Value
Text1	Datasource	Data1
	Datafield	Au_id
	Name	txtauid
Text2	Datasource	Data1
	Datafield	Author
	Name	txtauthor
Text3	Datasource	Data1
	Datafield	Year born
	Name	txtyb

Test Run

Run the project to test how it works.

1.      Run the project using F5.

2.      You should see first record of AUTHORS table

 Add Four Command buttons Add Author, Delete, Search, Quit and change their properties as follows….

Caption = “&Add Author”

Name    = cmdAdd

 Caption = “&Delete”

Name =cmdDelete

 Caption = “&Search”

Name = cmdSearch

 Caption = “&Quit”

Name = cmdQuit

Code of the application:

Private Sub cmdAdd_Click()

  'add new record to recordset

  Data1.Recordset.AddNew

  'set focus to author name

  txtauthor.SetFocus

End Sub

Private Sub cmdDelete_Click()

 With Data1.Recordset

   ' delete current record

   .Delete

   ' move to next record

   .MoveNext

   ' if you have reached eof then go to last record

   If .EOF Then

     .MoveLast

   End If

 End With

End Sub

Private Sub cmdQuit_Click()

  Unload Me

End Sub

Private Sub cmdSearch_Click()

Dim con As String

Dim currec As Variant

 ' take condition from user

 con = InputBox("Enter a condition", "Condition")

 With Data1.Recordset

   currec = .Bookmark

   .FindFirst con

   If .NoMatch Then

     .Bookmark = currec

     MsgBox "No record found", , "Search Error"

   End If

 End With

End Sub

  Now run the project to test 

Add, Delete, & searching a record .

1.      Run the project usig F5.

2.      Click on search button and enter  au_id = 15  as the condition in inputbox and click on Ok.

You will see details of author with author id 15.