12.  Database Projects

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

4. Project for querying from multiple tables using Querydef:

Suppose you have two tables named student and course in a database college as shown in the figure 7 and 8.   You want to make a query involving data from both tables and print them in the sorted order of the student numbers.  

               Figure 7

                   Figure 8

Enter the code as shown in the figure 7a.   The output of the query appears as shown in the figure 9.

Dim dbscollege As Database

 Sub CreateQueryDefX()

 Dim qdfTemp As QueryDef

  Set dbscollege = OpenDatabase("d:\rr2\college.mdb")

 With dbscollege

 Set qdfTemp = .CreateQueryDef("", "select student.sno, student.sname, student.major, result.cno, result.grade from student, result where student.sno = result.sno order by student.sno")

  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(dbOpenDynaset)

   Do While Not rsttemp.EOF

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

 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 7a

 
                     Figure 9