Topic: Creating a Database and a Table

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

1. Creating a Database and a Table:

INSERT INTO AUTHOR VALUES(AUID, ANAME) VALUES(24, ‘KKKK’)

Any number of records can be added, but each time the enter data  button is pressed.

Dim ws As Workspace
Dim db As Database
Dim td As TableDef

Private Sub Command1_Click()
'program for creating a database
Dim i As Integer
Dim flds(2) As Field
Set ws = DBEngine.Workspaces(0)
Set db = ws.CreateDatabase("c:\vbproj\publisher", dbLangGeneral, dbVersion25)
Set td = db.CreateTableDef("author")
Set flds(0) = td.CreateField("auid", dbLong)
Set flds(1) = td.CreateField("aname", dbText)
flds(1).Size = 10
td.Fields.Append flds(0)
td.Fields.Append flds(1)
db.TableDefs.Append td
MsgBox ("database created")
End Sub

Private Sub Command2_Click()
'program for inserting data
Set db = OpenDatabase("c:\vbproj\publisher")
Set rs = db.OpenRecordset("author", dbOpenTable)
db.Execute SQLTEXT, dbFailOnError
End Sub
Private Sub Command3_Click()
Set db = OpenDatabase("c:\vbproj\publisher")
Set rs = db.OpenRecordset("author", dbOpenTable)
Do While Not rs.EOF
Debug.Print rs(0), rs(1)
rs.MoveNext
Loop
rs.Close
End Sub

 Figure 1a

                                  Figure 1

              Figure 2

      Figure3

2. Project using DAO object TableDef:

Dim db As Database
Dim rs As Recordset
Private Sub Command1_Click()
Set db = OpenDatabase("d:\rr2\college")
Set rs = db.OpenRecordset("author", dbOpenTable)
rs.AddNew
rs("authorid") = Text1.Text
rs("authorname") = Text2.Text
rs.Update
rs.Close
End Sub

Private Sub Command2_Click()
Set db = OpenDatabase("d:\rr2\college")
Set rs = db.OpenRecordset("author", dbOpenTable)
Do While Not rs.EOF
Debug.Print rs(0), rs(1)
rs.MoveNext
Loop
rs.Close
End Sub

Private Sub Command3_Click()
Set db = OpenDatabase("d:\rr2\college")
Set rs = db.OpenRecordset("author", dbOpenTable)
rs.MoveLast
rs.Delete
rs.Close
End Sub

Private Sub Command4_Click()
Data1.Refresh
End Sub

Figure 4a

                                     Figure 4

                     Figure 5

3.  Project using QueryDef Object:

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 Function

Private Sub Command1_Click()
Call CreateQueryDefX
End Sub

Figure 6a

                    Figure 6

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

                    Figure 9

.
Data Access through ODBC:

      A Data Access Object can be connected  by a  ODBC driver and data objects can be accessed and printed in the Debug window.   First you set up a form with a single command button as shown in the figure 10..   In the code window of the command button enter the code as shown in the figure 10a.   Save and run the project.    You will get the output for all the SQL queries entered in the program.   You can modify the program so as to enter the queries from an input text box at the time of execution.   A typical output is shown in the figure 11.

Private Sub Command1_Click()
Dim wRKodbc As Workspace
Dim conpubs As Connection
Dim D As Database
Dim r As Recordset
Set wRKodbc = CreateWorkspace("NewODBCWorkspace1", _
        "admin", "", dbUseODBC)
           
'Set conpubs = wRKodbc.OpenConnection("Connection1", dbDriverPrompt)
 Set conpubs = wRKodbc.OpenConnection("Connection2", , , _
        "ODBC;DATABASE=PAYROLL;UID=ADMIN;PWD=SYSTEM;DSN=D1")
Debug.Print ""
Set r = conpubs.OpenRecordset("SELECT * FROM EMPLOYEE")
Do While Not r.EOF
Debug.Print r(0), r(1), r(2), r(3)
r.MoveNext
Loop

End Sub

Figure 10a

      Figure10

                          Figure11