12.  Database Projects

Databases and Database Management Systems:

3. Accessing Databases through Data Access Objects(DAOs):

DAO is the object oriented interface to the Microsoft Jet database engine.  DAO onkects enable you to access and manipulate data programmatically in local and remote databases.   You use DAO to manage databases with the help of their structures.  The DAO objects are organised in a hierarchial fashion starting from the  DBEngine as shown below.

DBEngine

Workspace

Database

TableDef  QueryDef    RecordSet

                  Field            Field

                  Parameters

The Workspace object defines a session for the user.   When your application starts, the Jet engine creates a default workspace object.   You can open additional workspaces if required and each workspace has a userID and a password associated with it.

You must start with declaring an object variable db for the Database and assign it to a particular database using the Set  statement as

    Dim db As Database

   Set db = OpenDatabase(“payrollo.mdb”)

A Recordset object contains a set of records from the database.   You must create the Recordset by declaring a variable for the Recordset object and then assigning it to the table as

Dim rs As Recordset

Set rs = db.OpenRecordSet(“employee”, dbOpenTable)

The type of recordset can be either dbOpenTable or dbOpenDynaset or dbOpenSnapShot.  There are no bound controls for the DAO objects and so you have to copy data from a Recordset to a textbox one by one.   A specifi field, say empno  is assigned to a text box as

Text1.text = rs(“empno”)

The DAO objects cannot be bound to Grid Controls.   So we will use the Debug window to display the database table.  Displaying the individual Records in Text Boxes can be done as in the previous example.

Start with the Standard EXE Project Form.   Before starting to work with DAO, you must first make the DAO object library available by making a reference to it.   Select references from the project menu and select the DAO Object Library version 3.5 as shown in the figure 5.  .Drag a Command button   to the design window form, and caption it as  shown in the figure 6.    

                 Figure 5

                                Figure 6

Enter the code in the button’s code window as shown in the figure 6a.   Save and run the project.   The output in the debug window appears as shown in the figure 7. 

Private Sub display()

Dim db As Database

Dim rs As Recordset

Set db = OpenDatabase("\vbproj\payroll.mdb")

Set rs = db.OpenRecordset("employee", dbOpenTable)

Do While Not rs.EOF

Debug.Print rs(0), rs(1), rs(2), rs(3)

rs.MoveNext

Loop

rs.Close

End Sub

Private Sub Command1_Click()

Call display

End Sub

       Figure 6a

                                   
   Figure 7