Transcript Vb6-Ch8

8
Chapter 8
Relational
Databases
ActiveX Database
Controls
By Carlotta Eaton
Exploring Microsoft Visual Basic 6.0
Copyright © 1999 Prentice-Hall, Inc.
Objectives...
1. Basic terminology such as tables and
relationships
2. Use controls specialized for databases
such as the data control, data-bound list
box, data-bound combo box, and databound grid
3. Differences between FlexGrid control and
data-bound grid control
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
2
Objectives
4. Implement a front-end application
for an existing Access database
5. Use Visual Data Manager
6. Create a new database application
with Application Wizard
7. Create new forms with the Data
Form Wizard
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
3
Overview
Access is the relational database
program included with Office
Professional Edition
Minimize code using data-bound
controls
Use standard ActiveX controls and
the FlexGrid control
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
4
Relational Databases...
Relational databases - composed of
tables and relationships
Table - consists of rows of records and
columns of fields
Relationship - association between
fields in two tables



one-to-one
one-to-many
many-to-many
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
5
Relational Databases...
Primary key - field(s) that uniquely
determines a record in a database
Foreign key - used to defined
relationships between tables - a field
in a table that also serves as a
primary key in another table
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
6
Birthday Access Database
Birthdays
Table
Occupations
Table
Primary
Key
Foreign
Key
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
7
Database Controls...
Data control - use for each table we
want to work with in the database
Data control Properties
 DatabaseName
property - path and
filename of the existing database
 RecordSource property - select from
the list of tables in the database
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
8
Birthday Access Database
Data Control for
the Occupations
Table
Data Control for
the Birthdays
Table
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
9
ActiveX Database Controls...
Data-bound list control - displays a
list box using a database field
Data-bound combo control - displays
combo box using a database field
Data-bound grid control - displays a
grid using a database field(s)
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
10
ActiveX Database Controls...
Properties to set before utilizing



Row Source
List Field
Text
Properties used to update and manage
the database
Data Source
 Data Field
 Bound Column

Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
11
ActiveX Database Controls
Properties must be set in order to
work correctly
1) Row Source
2) List Field
3) Text Property
4) Data Source
5) Data Field
6) Bound
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
12
Database Tools
Picture
Label
Text box
Combo box
Check box
List box
Data
OLE container
Image
DBList
DBGrid
DBCombo
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
13
More Properties
Data-bound Grid Control
 Data
Source property
Intrinsic Data-Aware Control
Properties
 Data
Source property
 Data Field property
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
14
Birthday Database
Using Access
Hands-On Exercise 1
Create project, then add and edit forms
 Add the Data control
 Edit the module code
 Edit and verify the Main form code
 Test the Splash and Main forms
 Add another Data control, and a Data-bound
combo box
 Test the Data-bound combo control

Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
15
Managing and Displaying
Records
Recordset - a record(s) selected from
a table(s) in a database
Table recordsets - limited to a single table, and
can modify records
Dynaset recordsets - can be used for multiple
tables, and can modify records
Snapshot recordsets - can be used for multiple
tables, but cannot modify records
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
16
Navigating Records with
Data Control Methods
Name.Recordset.Method
Where
Name is the name of the data control
Method is one of the following:
MoveFirst moves to the first record
MovePrevious moves to the previous record
MoveNext moves to the next record
MoveLast moves to the last record
Example:
datBirthdays.Recordset.MoveFirst
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
17
Finding Records with
the Data Control
Name.Recordset.Method
Where
Name is the name of the data control
Method is one of the following:
FindFirst finds the first record
FindPrevious finds the previous record
FindNext finds the next record
FindLast finds the last record
Example:
datBirthdays.Recordset.FindFirst “LastName = Dion”
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
18
Adding and Deleting Records
with the Data Control
Name.Recordset.Method
Where
Name is the name of the data control
Method is one of the following:
AddNew adds a new record to the end of the database
Refresh redisplays the current record
Update modifies the fields in the current record
Delete deletes the current record from the database
Close closes the recordset, and frees the memory
allocated for storage
Example: datBirthdays.Recordset.AddNew
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
19
Finish the Birthday
Access Project
Hands-On Exercise 2
Edit the Module code
 Test the Add Record and Delete Record code
 Add code and test the Find Record button and
procedure
 Modify the List form and add the Grid controls
 Test the List form

Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
20
More Database Tools...
Different ways to access data
 ActiveX
Data Objects (ADO)
 Remote Data Objects (RDO)
 Data Access Objects (DAO)
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
21
More Database Tools
Learning Edition Tools


ActiveX database controls
Intrinsic data-aware controls
Professional Edition Tools



Data View windows (new to VB 6.0)
Data Environment Design
Data Report feature
Enterprise Edition Tools


Exploring MS Visual
Basic 6
Query Designer
Database Designer
Copyright 1999 Prentice-Hall, Inc.
22
Add-Ins
Add-ins: tools that are programs that
customize and extend the Visual Basic
environment
1) Add-ins: generic term
2) Builders: helps view or set control properties
3) Wizards: leads a user step-by-step through a
task
4) Utilities: add-in that may be run outside Visual
Basic
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
23
Visual Data Manager
Add a Field
Exploring MS Visual
Basic 6
Remove a Field
Copyright 1999 Prentice-Hall, Inc.
24
Visual Data Manager and the
Birthday Database
Hands-On Exercise 3
 Create
a new project & new database
Create a new table and add fields
 Add a record to the database
 Use the Application Wizard
 Use the Data Form Wizard
 Finish the Application Wizard
 Test the Update form and the Grid form
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
25
Summary ...
Access included with Microsoft
Office Professional Edition
Relational databases composed of
several tables and relationships
Relationships established between
two tables using a foreign key
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
26
Summary ...
ActiveX database controls include
 Data-bound
list box
 Data-bound combo box
 Data-bound grid control
 FlexGrid control
Data control must be used for each
table we want to access
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
27
Summary ...
Navigate database using recordsets
 Table
recordset
 Dynaset recordset
 Snapshot recordset
Define new databases using the Visual
Data Manager add-in included in all 3
editions of Visual Basic
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
28
Summary
Data Form Wizard (new with VB 6.0)
helps create database forms
Application Wizard creates general
forms such as Splash and About
forms for applications
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
29
Practice with Visual Basic
1. Demonstration: Data Browser Program
2. Sample Program: Bibliography Database
Browser
3. Demonstration: Music Review Notepad
4. Personal Address Book
5. Access Demo
6. Access Demo Front End
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
30
Case Studies
Relational Database Design
Database Design Implementation
Access Office VB Advisor
Universal Access Web Site
Exploring MS Visual
Basic 6
Copyright 1999 Prentice-Hall, Inc.
31