Data Access Objects

Connecting to a Database Using DAO

In this exercise, you will use DAO to connect to a database in Visual Basic.

* To make a reference to DAO in Visual Basic

1. Start Visual Basic
2. Create a new Standard EXE project.
3. From the Project menu, click References .
4. Set a reference to Microsoft DAO 3.5 Object Library , then click OK .
5. From the Project menu, click Components .
6. Select Microsoft Common Dialog Control 5.0 , then click OK .
7. Add a common dialog control to your form.
8. In the Load event of your form, type the following code:

    Dim DB As Database CommonDialog1.ShowOpen Set DB = OpenDatabase(CommonDialog1.filename) If Not DB Is Nothing Then     MsgBox "Successfully connected!" Else     MsgBox "Could not connect." End If 

9. From the Run menu, click Start .
10. When the common dialog appears, open the WA\Practice\Nwind.mdb file.

    When you connect to the database, you will get a message box displaying the connection status.

11. Stop the application and close Visual Basic.

Building Recordsets with DAO

A Recordset is the data you get when you query a database. For example, if you open a database connection and request all customers that have more than 25 orders, the resulting group of records is called a Recordset. The Recordset object manages recordsets created with DAO.

The Recordset Object

The Recordset object defines a group of records so they can be manipulated or viewed . A Recordset object represents the records in a base table or the records that result from running a query. While a recordset can contain many records, only one record can be the current record.

Example

This example opens a recordset in Visual Basic:

 Dim db As Database Dim rs As Recordset ' Open the Northwind database Set db = OpenDatabase("Northwind.mdb") ' Open a recordset Set rs = db.OpenRecordset("SELECT * FROM Employees", _ dbOpenDynaset, dbReadOnly) ' Add code here to process the records . . . ' When finished close the objects rs.Close db.Close