12.  Database Projects

In this lesson we will consider some  projects using DAOs, ODBC and ADOs..

3.  Project using QueryDef Object:

In this project we make use of the QueryDef object to execute a stored  sql query.   The input form contains only a command button.    Enter the code as shown in the figure 6a.    The output window appears as shown in the figure 6.

Dim dbspayroll As Database

 Sub CreateQueryDefX()

 Dim qdfTemp As QueryDef

  Set dbspayroll = OpenDatabase("c:\vbproj\payroll.mdb")

 With dbspayroll

 Set qdfTemp = .CreateQueryDef("", "select * from Employee where salary > 7000")

 GetrstTemp qdfTemp

 .Close

 End With

 End Sub

 Function GetrstTemp(qdfTemp As QueryDef)

 Dim rsttemp As Recordset

 With qdfTemp

 Debug.Print .Name

 Debug.Print " " & .SQL

 Set rsttemp = .OpenRecordset(dbOpenSnapshot)

 Do While Not rsttemp.EOF

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

 rsttemp.MoveNext

 Loop

 With rsttemp

  .MoveLast

 Debug.Print "number of records = " & .RecordCount

 .Close

 End With

 End With

 End Function

Private Sub Command1_Click()

Call CreateQueryDefX

End Sub

Figure 6a

                     Figure 6