|
Home
Visual Basic 5/6 Programming Tutorials
Visual Basic 5/6 and Databases Table of
Contents
Visual Basic 5/6 and Databases - Table of Contents
1. Introducing Visual Basic and Databases
Preview 1-1
Course Objectives 1-2
Course Requirements 1-3
What is a Database? 1-4
Where Does Visual Basic Fit In? 1-6
Building a Visual Basic Application 1-8
Structure of a Visual Basic Application 1-8
Steps in Developing Application 1-9
Drawing the User Interface and Setting Properties 1-9
Setting Properties of Controls at Design Time 1-13
Setting Properties at Run-Time 1-14
How Names are Used in Object Events 1-14
Writing Code 1-15
Review of Variables 1-15
Visual Basic Data Types 1-15
Variable Declaration 1-16
Example 1. Mailing List Application 1-18
Summary 1-25
2. Introduction to Databases
Review and Preview 2-1
Database Structure and Terminology 2-2
Relational Databases 2-3
Sample Relational Database 2-4
Sample Database Structure 2-7
Virtual Database Tables 2-8
Creating a Database 2-10
Summary 2-11
3. Database Connection with the DAO Data Control
Review and Preview 3-1
DAO or ADO Whats the Difference? 3-2
DAO Data Control 3-3
DAO Data Control Properties 3-4
Recordset Object 3-5
Data Bound Controls 3-6
Data Bound Control Properties 3-6
Example 3-1. Accessing the Books Database 3-8
DAO Data Control Events 3-11
DAO Data Control Methods 3-11
DAO Data Control Recordset Properties 3-12
DAO Data Control Recordset Methods 3-13
DAO Data Control Recordset Navigation 3-14
Quick Example 1 - Recordset Navigation 3-15
Summary 3-17
Exercise 3. Northwind Traders Database 3-18
4. Database Connection with ADO Technology
Review and Preview 4-1
ADO Data Control 4-2
ADO Data Control Properties 4-3
ConnectionString Property 4-4
Recordset Object 4-5
Data Bound Controls 4-6
Data Bound Control Properties 4-6
Example 4-1. Accessing the Books Database 4-8
ADO Data Control Events 4-11
ADO Data Control Methods 4-12
ADO Data Control Recordset Properties 4-12
ADO Data Control Recordset Methods 4-13
ADO Data Control Recordset Navigation 4-14
Quick Example 1 - Recordset Navigation 4-15
DAO or ADO Whats the Difference? 4-17
ADO Data Environment 4-18
Connection Object 4-19
Command Object 4-19
Quick Example 2 Use of ADO Data Environment 4-20
Data Bound Controls with the Data Environment 4-23
Example 4-2. Drag and Drop Controls 4-24
Recordsets in the Data Environment 4-27
Quick Example 3 Record Navigation with the Data Environment 4-28
DAO to ADO ADO to DAO 4-30
Summary 4-32
Exercise 4-1. Northwind Traders Database 4-33
Exercise 4-2. Data Environment with Northwind Traders Database 4-36
5. Database Queries with SQL
Review and Preview 5-1
SQL Background 5-2
Basics of SQL 5-3
Where Does SQL Fit In Visual Basic? 5-5
SQL with the DAO Data Control 5-6
Quick Example 1 - SQL with the DAO Data Control 5-7
SQL with the ADO Data Control 5-9
Quick Example 2 - SQL with the ADO Data Control 5-10
SQL with the ADO Data Environment 5-12
Quick Example 3 - SQL with the ADO Data Environment 5-14
Example 5-1. SQL Tester 5-17
A Brief (Hopefully) Interlude for Visual Basic 6 Users 5-18
SELECT/FROM SQL Statement 5-23
ORDER BY Clause 5-25
WHERE Clause 5-28
Single Table WHERE Clause 5-29
Multiple Table WHERE Clause 5-32
INNER JOIN Clause 5-37
OUTER JOIN Clause 5-41
Visual Basic Functions with SQL 5-43
SQL Aggregate Functions 5-45
SQL Construction Tools 5-47
Building SQL Commands in Code 5-51
Example 5-2. Searching the Books Database 5-52
Summary 5-58
Exercise 5. Northwind Traders Database 5-59
6. Visual Basic Interface Design
Review and Preview 6-1
Interface Design Philosophy 6-2
Example 6-1. Mailing List Revisited 6-4
Visual Basic Standard Controls 6-5
Form Control 6-5
Command Button Control 6-6
Label Control 6-7
Text Box Control 6-8
Check Box Control 6-9
Option Button Control 6-10
Frame Control 6-10
Picture Box Control 6-11
Image Control 6-11
Example 6-2. Authors Table Input Form 6-12
Message Box 6-19
Example 6-3. Authors Table Input Form (Message Box) 6-22
Application State 6-24
Example 6-4. Authors Table Input Form (Application State) 6-26
Entry Validation 6-29
Example 6-5. Authors Table Input Form (Entry Validation) 6-31
Input Validation 6-32
Example 6-6. Authors Table Input Form (Input Validation) 6-34
Error Trapping and Handling 6-37
Example 6-7. Authors Table Input Form
(Error Trapping and Handling) 6-42
On-Line Help Systems 6-43
Example 6-8. Authors Table Input Form (On-Line Help Systems) 6-51
Application Testing 6-54
Custom Controls 6-55
Masked Edit Control 6-56
Quick Example 1 Masked Edit Control 6-57
UpDown Control 6-58
Quick Example 2 UpDown Control 6-59
Tabbed Dialog Control 6-61
Quick Example 3 Tabbed Dialog Control 6-62
Toolbar Control 6-63
Quick Example 4 Toolbar Control 6-64
Data Bound List Control 6-66
Quick Example 5 Data Bound List Control 6-68
Data Bound Combo Control 6-70
Data Bound Grid Control 6-71
Quick Example 6 Data Bound Grid Control 6-72
Data Bound FlexGrid Control 6-73
Quick Example 7 Data Bound FlexGrid Control 6-74
Calendar Control 6-75
Quick Example 8 Calendar Control 6-76
Common Dialog Control 6-77
Quick Example 9 Common Dialog Control 6-79
Additional ADO Custom Controls 6-81
Hierarchical FlexGrid Control 6-82
Quick Example 10 Hierarchical FlexGrid Control 6-83
Chart Control 6-84
Quick Example 11 Chart Control 6-85
Month View Control 6-86
Quick Example 12 Month View Control 6-87
Date Time Picker Control 6-88
Quick Example 13 Date Time Picker Control 6-89
Data Repeater Control 6-90
Example 6-9. Data Repeater Control 6-91
Summary 6-96
Exercise 6. Publisher Table Input Form 6-97
Build Interface 6-98
Add Message Box(es) 6-102
Code Application State 6-103
Perform Entry Validation 6-104
Perform Input Validation 6-105
Add Error Trapping and Handling 6-105
Add On-Line Help System 6-106
Application Testing 6-107
7. Database Management
Review and Preview 7-1
Database Management with the DAO Data Control 7-2
Editing Records with the DAO Data Control 7-3
Example 7-1. Editing Records with the DAO Data Control 7-6
Adding Records with the DAO Data Control 7-13
Example 7-2. Adding Records with the DAO Data Control 7-15
Deleting Records with the DAO Data Control 7-18
Example 7-3. Deleting Records with the DAO Data Control 7-20
Database Management with the ADO Data Control 7-22
Editing Records with the ADO Data Control 7-23
Example 7-4. Editing Records with the ADO Data Control 7-26
Adding Records with the ADO Data Control 7-33
Example 7-5. Adding Records with the ADO Data Control 7-35
Deleting Records with the ADO Data Control 7-38
Example 7-6. Deleting Records with the ADO Data Control 7-40
Database Management with the ADO Data Environment 7-42
Editing Records with the ADO Data Environment 7-43
Example 7-7. Editing Records with the ADO Data Environment 7-45
Adding Records with the ADO Data Environment 7-54
Example 7-8. Adding Records with the ADO Data Environment 7-56
Deleting Records with the ADO Data Environment 7-59
Example 7-9. Deleting Records with the ADO Data Environment 7-61
Finding Records in a Database 7-63
Finding Records with the DAO Data Control 7-64
Quick Example 1 - Finding Records with the DAO Data Control 7-66
Finding Records with the ADO Data Control 7-67
Quick Example 2 - Finding Records with the ADO Data Control 7-69
Finding Records with the ADO Data Environment 7-70
Quick Example 3 -
Finding Records with the ADO Data Environment 7-72
Modifying Records in Code 7-73
Quick Example 4 Accessing Records in Code 7-75
Stopping a Database Application 7-76
Example 7-10 Stopping a Database Application 7-78
Example 7-11. Authors Table Input Form 7-80
Additional Navigation Capabilities 7-81
Editing Records 7-83
Adding Records 7-87
Deleting Records 7-94
Stopping the Application 7-95
Example 7-12. Publishers Table Input Form 7-96
Additional Navigation Capabilities 7-97
Editing Records 7-100
Adding Records 7-104
Deleting Records 7-107
Stopping the Application 7-108
Multiple Table Database Management 7-109
Database Keys 7-110
Database Modifications 7-111
Quick Example 5 Editing with Multiple Tables 7-112
Form Navigation 7-114
Quick Example 6 Multiple Forms 7-116
Final Application 7-118
Example 7-13. Books Database Management System 7-119
Basic Book Titles Input Form 7-120
Year Published Problem 7-127
Finding Records 7-130
Adding Publisher Name 7-135
Adding Publisher Editing 7-139
Modify Publishers Input Form 7-142
Modify Authors Input Form 7-147
Adding Author Names 7-152
Quick Example 7 Database Detective 7-153
Viewing Author Selections 7-155
Viewing Author Names 7-160
Saving Author Names 7-164
Adding Author Editing 7-166
Input Control Navigation 7-168
Entry and Input Validation 7-170
Titles Form On-Line Help 7-175
Summary 7-179
Exercise 7. Take a Break 7-180
8. Database Reports
Review and Preview 8-1
Data Report Development 8-2
Printer Object 8-3
Printer Object Properties 8-4
Printer Object Methods 8-5
Printer Object Coordinates 8-6
Print Method 8-7
Graphics Methods 8-8
Printer Object with Databases 8-9
Example 8-1. Database Report with Printer Object 8-10
Crystal Reports 8-14
Quick Example 1 Titles Listing with Crystal Reports 8-15
Crystal Reports Report Design 8-17
Crystal Reports Custom Control 8-19
Quick Example 2 Displaying Titles Listing with Crystal Reports 8-21
Example 8-2. Database Report with Crystal Reports 8-23
Missing Titles 8-29
ADO Data Environment Data Report 8-30
Data Report Properties 8-31
Data Report Methods 8-31
Data Report Designer 8-32
Quick Example 3 Titles Listing with Data Report 8-34
Data Report Printing and Exporting 8-38
Predefined Report Fields 8-40
Function Fields 8-41
Quick Example 4 Enhanced Titles Listing with Data Report 8-42
Example 8-3. Database Report with Crystal Reports 8-43
Summary 8-50
Exercise 8. Another Titles Listing 8-51
Visual Basic Interface 8-52
Printer Object Version 8-54
Crystal Reports Version 8-59
ADO Data Report Version 8-63
9. Distributing a Database Application
Review and Preview 9-1
Accessing Database Files in Code 9-2
Database File in Application Path 9-3
Database File Location with Common Dialog Control 9-4
Opening Database Files with DAO Data Control 9-6
Quick Example 1 Common Dialog with DAO Data Control 9-7
Example 9-1. Opening Database Files with DAO Data Control 9-9
Opening Database Files with ADO Data Control 9-11
Quick Example 2 Common Dialog with ADO Data Control 9-13
Example 9-2. Opening Database Files with ADO Data Control 9-15
Opening Database Files with ADO Data Environment 9-17
Quick Example 3 Common Dialog with ADO Data Environment 9-19
Example 9-3. Opening Database Files with ADO Data Environment 9-22
Assigning Icons to Your Database Application 9-24
Quick Example 4 - Assigning an Icon to the Titles Input Form 9-25
Designing Your Own Icon 9-26
Creating Visual Basic Executable Files 9-28
Quick Example 5 Creating an Executable File 9-30
Building a Distribution Package 9-31
Visual Basic 5 Application Setup Wizard 9-32
Step 1. Select Project and Options 9-32
Step 2. Distribution Method 9-33
Step 2a. Directory/Media Selection 9-34
Step 3. Data Access 9-36
Step 4. ActiveX Server Components 9-37
Step 5. Confirm Dependencies 9-38
Step 6. File Summary 9-39
Step 7. Finished! 9-40
Quick Example 6 Visual Basic 5 Distribution Package 9-41
Visual Basic 6 Package & Deployment Wizard 9-42
Step 1. Initial Information 9-42
Step 2. Package Type 9-43
Step 3. Package Folder 9-44
Step 4. DAO Drivers 9-45
Step 5. Included Files 9-46
Step 6. Cab Options 9-47
Step 7. Installation Title 9-48
Step 8. Start Menu Items 9-49
Step 9. Install Locations 9-50
Step 10. Shared Files 9-51
Step 11. Finished! 9-52
Final Step - Write Distribution Media 9-53
Quick Example 7 Visual Basic 6 Distribution Package 9-54
Summary 9-55
10. Database Design Considerations
Review and Preview 10-1
Database Design 10-2
Database Modeling 10-3
Information Requirements 10-4
Table Requirements 10-5
Field Requirements 10-8
Field Types 10-9
Null Values 10-11
Database Design Implementation 10-12
Building Databases with the Visual Data Manager 10-13
Example 10. Building the KWSALES Database 10-14
Customers Table 10-15
Orders Table 10-18
Purchases Table 10-19
Products Table 10-20
Database Testing and Design Refinement 10-21
Quick Example 1 Test Inputs with KWSALES 10-22
Summary 10-23
11. Sample Database Projects
Review and Preview 11-1
Overview of Database Projects 11-2
Example 11-1. Weather Monitor Project 11-3
Weather Monitor Database 11-4
Weather Monitor Interface 11-5
Record Weather Data Tab 11-6
View Temperature Data Tab 11-16
View Precipitation Data Tab 11-27
A Null Problem 11-34
Weather Monitor Printed Reports 11-36
Weather Data Report 11-36
Temperature Data Report 11-39
Precipitation Data Report 11-41
Weather Monitor Help System 11-43
Weather Monitor Icon 11-47
Weather Monitor Distribution Package 11-48
Suggested Improvements 11-49
Example 11-2. Home Inventory Project 11-50
Home Inventory Database 11-51
Home Inventory Interface 11-53
Database Navigation 11-56
Editing Records 11-58
Adding Records 11-62
Deleting Records 11-65
Entry Validation 11-67
Input Validation 11-70
Item Validation 11-70
Date Validation 11-71
Cost Validation 11-74
Inventory Report 11-75
Stopping the Application 11-79
Suggested Improvements 11-81
Example 11-3. Sales Order Form Project 11-82
Order Information 11-83
Existing Customer Information 11-87
Adding a New Customer 11-92
Product Selection 11-98
Submitting an Order 11-104
Printing an Invoice 11-107
Suggested Improvements 11-109
Summary 11-110
12. Advanced Database Topics
Review and Preview 12-1
Exporting Database Data 12-2
Opening a Sequential File for Output 12-3
Writing Data to a Sequential File 12-3
Saving a Sequential File 12-4
Quick Example 1 Exporting Database Data 12-5
Importing Database Data 12-7
Opening a Sequential File for Input 12-7
Reading Data from a Sequential File 12-8
Closing a Sequential File 12-8
Quick Example 2 Importing Database Data 12-9
Data Access Object (DAO) 12-13
Connecting to a Database with the DAO 12-15
Opening Recordsets with the DAO 12-17
Recordset Navigation with the DAO 12-18
Example 12-1. Recordset Navigation with the DAO 12-19
Finding Records with the DAO 12-23
Example 12-2. Finding Records with the DAO 12-25
Editing Records with the DAO 12-27
Example 12-3. Editing Records with the DAO 12-28
Adding Records with the DAO 12-32
Example 12-4. Adding Records with the DAO 12-34
Deleting Records with the DAO 12-37
Example 12-5. Deleting Records with the DAO 12-39
Creating a Database with the DAO 12-41
Example 12-6. Creating a Database with the DAO 12-45
ActiveX Data Object (ADO) 12-48
Connecting to a Database with the ADO 12-50
Opening Recordsets with the ADO 12-52
Recordset Navigation with the ADO 12-54
Example 12-7. Recordset Navigation with the ADO 12-55
Finding Records with the ADO 12-60
Example 12-8. Finding Records with the ADO 12-61
Editing Records with the ADO 12-63
Example 12-9. Editing Records with the ADO 12-64
Adding Records with the ADO 12-68
Example 12-10. Adding Records with the ADO 12-70
Deleting Records with the ADO 12-73
Example 12-11. Deleting Records with the ADO 12-75
Creating a Database with the ADO 12-77
Multi-User Considerations 12-77
Other Database Types 12-78
Summary 12-79
Exercise 12. The Final Application 12-80
Back to Top
Back to Visual Basic 5/6 and
Databases
|