12. Database
Projects
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
   |
