12. Database
Projects
In this lesson we will consider some projects using
DAOs, ODBC and
ADOs..
1. Creating a Database and a Table:
The dbEngine object is used to create the
workspace for the database file.
The TableDef object is used to
create the table. Drag three command
buttons in the form, caption them, size them and position them as shown in the
figure 1. Open the code window and
enter the codes as shown in the figure 1a.
Data are inserted in the table by the sql command ‘insert’. The syntax of the Insert statement is:
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.
Save and run the project and see how the three
commands work. You will get an error
message if you try to create a database which is already present. You can see the database table in the
Immediate window by pressing the display button. In order to see the database table in the
DBGrid, open another
form. Place a data control and a DBGrid control box. Bind the data control to the database
‘publisher’ and the Record Source
to ‘author’. DBGrid is bound to ‘Data1’..
and The form2 is shown in the
figure 2 and the output is shown in the figure 3. Notice the two forms are not related in the program and so is
separately run. To run the form2,
open the properties dialog table by pressing the properties item from the
project menu and select form2 for the startup object from the dropdown list.
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 1
Figure 2
Figure3
   |
