Topic: Database Creation using Data Control and Data Access Object

DataBases and Database Management Systems:

     Visual Basic provides tools for creating and accessing databases.   The two major tools are

1. The Data Control
2.  The Data Access Object

In this lesson we will consider the following projects;

1. Making SQL Queries in Databases using Data Control
2. Binding Data Controls with Text Boxes
3. Accessing DAO Objects in  Debug Window

     The Data control gives access to the Database without any programming, since it is a powerful ActiveX Control.     The Data Access Object is a structure of objects for accessing the database through programming.

    We know that a Database is a grouping of related information organized for easy retrieval and processing.   The actual data are stored in tables in a row-column format.   A row is called a record.  

     Recordsets are objects that represent collections of records from one or more tables.   Recordsets are the equivalent of variables in regular programming.   You can access the tables of a database only by manipulating the Recordset objects.   A Recordset is a view of some data in the database, selected from the database according to user-specified criteria.   There are three types of Recordsets, namely

1. DynaSets which are updatable views of data
2. SnapShots which are static(read-only) views of data
3. Tables, which are direct views of tables.

1. SQL Queries using Data Control:

      Drag a DB grid Control,  a Data Control, a label control,  two Command buttons, a Common Dialog Control  and a Textbox Control on the form, size them, and position them on the form as shown in the figure 1 .   Set the visible property of the Data Control  false so that it will not be visible at run time.   Set the Name property of the Textbox as txtsql, multiline property as true and scrollbars property as 2-Vertical.  Caption the command buttons as in the figure.   Open the code window for the two command buttons and enter the code as shown in the figure 1a.   Save and run the project.   On clicking the open database button, ‘file open dialog box’ appears.   Select the database file.   The name of the database file appears on the label  box at the top.   Then enter the sql query in the text box and press the execute button.   The result appears on the DBGrid control box.   A typical example is shown in the figure 2.

Private Sub Command1_Click()
On Error GoTo nodatabase
CommonDialog1.CancelError = True
CommonDialog1.Filter = "databases|*.MDB"
CommonDialog1.ShowOpen
Data1.DatabaseName = CommonDialog1.filename
Data1.Refresh
If Err = 0 Then
Label1.Caption = CommonDialog1.filename
Else
MsgBox Err.Description
End If
nodatabase:
On Error GoTo 0
End Sub

Private Sub Command2_Click()
On Error GoTo sqlerror
Data1.RecordSource = txtsql
Data1.Refresh
Exit Sub
sqlerror:
MsgBox Err.Description
End Sub

               Figure 1a

                         Figure 1

                                  Figure 2

2. Binding Data Control with Text Boxes:

     In the last example the Data Control was bound to the DBGrid control so that the output was displayed in the DBGrid box.   This time we will bind the Data control with text boxes binding each field of the database with a text box.    In addition we will add facilities for add, delete, edit, find and seek methods to operate on the database.   Drag a Data Control,  four label boxes, four text boxes and thirteen command buttons, size them, caption them and position them as shown in the figure 3.    Set the Database Name property by actual browsing and the record source property for the Data control object.     For each of the text box set the data source property as D1 and data field property as the name of the field which it is to display.  It may be noted that the seek method will work only with Table type  recordset and so set the Recordset type property of the data control  as ‘0-Table’.  For working with the Find method, set the Recordset type property as 1-Dynaset.  Open the code window for the different command buttons and enter the codes as shown in the figure 3a.   Run the project and check the function of each command button.   A typical output is shown in the figure 4.

Private Sub add_Click()
Data1.Recordset.AddNew
Text1.SetFocus
End Sub

Private Sub delete_Click()
Data1.Recordset.delete
End Sub
Private Sub edit_Click()
Data1.Recordset.edit
End Sub

Private Sub exit_Click()
Unload Me
End Sub

Private Sub fieldcount_Click()
MsgBox "Number of fields " & Data1.Recordset.Fields.Count
End Sub

Private Sub find_Click()
Dim c As String
c = InputBox("enter the empno whose record is required")
Data1.Recordset.FindFirst "empno =" & "'" & c & "'"
End Sub

Private Sub movefirst_Click()
Data1.Recordset.movefirst
End Sub

Private Sub movelast_Click()
Data1.Recordset.movelast
End Sub

Private Sub movenext_Click()
Data1.Recordset.movenext
If Data1.Recordset.EOF Then
MsgBox "you are on the last record"
Data1.Recordset.movelast
End If
End Sub

Private Sub moveprevious_Click()
Data1.Recordset.moveprevious
If Data1.Recordset.BOF Then
MsgBox "you are on the first record"
Data1.Recordset.movefirst
End If
End Sub

Private Sub reccount_Click()
Data1.Recordset.movelast
MsgBox "Number of Records " & Data1.Recordset.RecordCount
End Sub

Private Sub save_Click()
Data1.Recordset.Update
End Sub

Private Sub seek_Click()
Dim c
c = InputBox("enter the empno whose record is required")
Data1.Recordset.Index = "empno"
Data1.Recordset.seek "=", c
End Sub
     Figure 3a

                             Figure 3

          Figure 4

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

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.    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 5

                                Figure 6

          Figure 7