Beginning Visual Basic 6 Database Programming

870 Pages·2003·45.58 MB·English

by  John Connell (auth.)

Summary of Contents
What This Book ls About
Chapter 1: What ls a Database?
Chapter 2: Meet the ActiveX Data Objects (ADO) Data Wizard
Chapter 3: Programming the Data Control
Chapter 4: Designing a User Interface for the Data Control
Chapter 5: Programming a Bulletproof User Interface
Chapter 6: Completing the User Interface
Chapter 7: Building a Data Control Class Module
Chapter 8: Getting the Data You Want from the Database
Chapter 9: Database Design, Construction, and Analysis
Chapter 10: Programming the Address Book
Chapter 11: Universal Data Access Using ADO
Chapter 12: Creating ADO Data Bound ActiveX Controls
Chapter 13: ADO and Active Server Pages
Chapter 14: Advanced ADO Programming-Data Mining
Chapter 15: Making Our Data Available Universally
Chapter 16: Where Next?
Appendix A: One Standard, Many Flavors ...
Appendix B: Salutions
Appendix C: Summary of Microsoft Access Field Types
Index
Beginning
Visual Basic 6
Database
Programming
John Connell
APress Media, LLC
Beginning Visual Basic 6 Database Programming
Beginning Visual Basic 6 Database Programming
Copyright© 2003 by John Connell
Originally published by Apress in 2003
All rights reserved. No part of this work may be reproduced or transmitted in any form
or by any means, electronic or mechanical, including photocopying, recording, or by
any information storage or retrieval system, without the prior written permission of
the copyright owner and the publisher.
ISBN 978-1-59059-251-9 ISBN 978-1-4302-5192-7 (eBook)
DOI 10.1007/978-1-4302-5192-7
Trademarked names may appear in this book. Rather than use a trademark symbol
with every occurrence of a trademarked name, we use the names only in an editorial
fashion and to the benefit of the trademark owner, with no intention of infringement of
the trademark.
In the United States: phone 1-800-SPRINGER, email orders@springer-ny. com, or
visit http: I lwww. springer-ny. com. Outside the United States: fax +49 6221
345229, email orders@springer. de, or visit http: I lwww. springer. de.
For information on translations, please contact Apress directly at 2560 Ninth Street,
Suite 219, Berkeley, CA 94710. Phone [phone], fax [phone], email
info@apress. com, or visit http: I lwww. apress. com.
Theinformation in this book is distributed on an "as is" basis, without warranty.
Although every precaution has been taken in the preparation of this work, neither the
author(s) nor Apress shall have any liability to any person or entity with respect to any
loss or darnage caused or alleged to be caused directly or indirectly by the information
contained in this work.
The source code for this book is available to readers at http: I lwww. apress. com in
the Downloads section.
Credlts
Editorial Board Cover/Design/Layout
Dan Appleman Kurt Krames
Craig Berry
Gary Comell Technical Reviewers
Tony Davis Humberto Abreu
Steven Rycroft Richard Bonneau
Julian Skinner Mike Erickson
Martin Streicher Antoine Guisto
Jim Sumser John Kauffmann
Karen Watterson Emma Morgan
Gavin Wright Boyd Nolan
John Zukowski Mike Sussman
Additional Material Copy Edit
David Whitney George Briggs
Alex Zoro
Exercises Bamey Zoro
Mike Erickson
Index
Developmental Editor Craig Berry
Dominic Shakeshaft Diane Brenner
Editors
Kate Hall
Andy Corsham
Dedication
This book is dedicated to my wife Janell and my two wonderful sons Garrett and
Grady. This would not have been possible without their constant support and
encouragement. Thanks guys.
About the Author
John Connell is a Vice President at First Chicago NDB Bank. He has a Master's Degree
in Computer Science and a Master's Degree in Business Administration. John teaches
computer programming classes at DePaul University in Chicago.
John has designed and written several financial programs in Visual Basic that are
currently in use in many of the largest companies in the United States.

Beginning
Visual Basic 6
Database
Programming
What thls Book ls About 1
Who IsThis Book Aimed At? 2
New Database Features in Visual Basic 6.0 2
Our Approach 3
What Do I Need To Use This Book? 3
Customer Support 4
Where To Find the Sampie Code For This Book 4
Feedback and Suggestions 4
Conventions Used In This Book 4
The Road Map for This Book 6
Chapter 1: What ls a Database? 9
Why Use a Database? 9
What is a Database? A More In-Depth View 10
Storing Information in Our Filing Cabinet 11
Querying Our Database -Getting the Information We Need 13
Flat Filesand Relational Databases 14
Relational Database Components 15
The Relational Database in More Detail 15
What Exactly is a Table? 16
Database Filesand Tables 16
Our Sampie Database-Biblio.mdb 16
Looking at the Data in Biblio.mdb 17
Indexes 'R' Us 18
What's a Relationship Again? 20
Relationships Between Tables 20
The Key to Successful Relationships 21
Who's Got the Key? 22
Orphaned Records 23
Normalizing Our Data 24
Moving On... 24
AResurne of Some Important Terms 24
Our Two Vehicles -Access and Visual Basic 24
Why Should We Use Access? 25
The Biblio.mdb relational database 25
Create a New Sub-Directory 25
Where Does Our VB Program Come In? 26
The User Interface 27
The Database Engine 27
Jet-A Specific Database Engine 27
DLLs-The Fuel Inside the Jet Engine 27
Jet is Self-Contained 28
,..ble Of Contelila
Beginning V86 Databases
The Data Store 29
Getting Our Feet Wet 29
Tables, Records and Fields in Biblio.mdb 29
The Publishers Table 29
The Authors Table 30
The Title Author Table 31
Building a Relationship 31
The Way(s) Ahead ... 32
DAOorAOO? 33
Beyond the DAOI ADO Conundrum 33
Some Pros and Cons of DAO and ADO 34
Enjoying the Complexity 34
Choosing Your Route and Reusability 35
The Good News Again 35
Summary 35
Take-Horne Points 36
The Next Step 36
Exercises 37
Chapter 2: Meet the ActlveX Data Object (ADO) Data Wizard 39
What is the AOO Data Control? 40
The Recordset 40
Meet the VB Data Form Wizard 42
What is an Add-In? 42
The Final Product in Design Mode 54
The Final Product at Run-Time 56
Navigating the Recordset 57
Looking at the Same Information in Another Way 58
TheMaster /Detail Form in Design Mode 63
TheMaster /Detail Format Run-Time 64
MS HFlexGrid Layout 65
The MS Chart Layout 69
Pay No Attention to the Man Behind the Curtain 75
The Dark Side of the Wizard 75
A Word on Sorting 76
ASCII Sort Order - A Primer 76
Summary 79
What We Leamed 80
Exercises 81
Chapter 3: Programmlng the Data Control 83
Why Not Just Let the Wizard Do It? 83
The Data Control and Bound Controls 84
Intrinsic Controls 84
What is a .DLLAgain? 85
Dynamic Linking 86
The Intrinsic DAO Data Control 86
What is a Recordset Exactly? 87
What is a Data Bound Control? 88
Types of Bound Controls Available to Us 89
lntrinsic Controls 89
ActiveX Controls 89
Working With Data Controls 91
Changing the Data 95
The Sequence of Events 96
How Does It Know That? 96
viii
I I
Table Of Contents
Parent/Child Data Control Relationship 98
Who Was That Masked Control? 104
Introducing the New ADO ActiveX Data Control 105
The ADO Data Control and the Bound Da taList Control 106
The Mysterious BoundColumn Property. 114
The VB 6.0 Hierarchical FlexGrid Bound Control 119
What Do All These Controls Have in Common? 122
Summary 123
What We Learned 123
Exercises 124
Chapter 4: Deslgning a User Interface for the Data Control 127
Designing the User Interface 127
The Visual Basic Form 128
Properties Explained 130
Event-Driven Programming Explained 131
The Important Form Event Sequence 134
Into the Heart of the Data Control 134
Duplicating the Functionality of the Wizard 135
Let's Do it Ourselves this Time 135
Bound Controls Revisited 143
Recordsets Revisited 145
The Strange and Terrible Saga of Control Arrays 147
Under the Hood of the Data Control 148
The BOPAction Property 149
The EOFAction Property 150
The Connect Property 150
JustAdd Code! 151
Reduce the Dots -Efficient Object Reference 153
The IntelliSense Feature 155
Why is the Code Placed in Form_Activate()? 157
The Data Control Reposition Event 158
Eye Candy for the User 158
Make Some Enhancements 159
Where Do We GoFrom Here? ... 163
What We Learned 163
Onw ard! 163
Exercises 164
Chapter 5: Programming a Bulletproof User Interface 167
The Data Control-Right or Wrong? 167
Building an Enhanced User Interface 168
The Class System 168
Back to the Data Control... 168
Properties of the Data Control 169
Methods of the Data Control 170
Properties of the Recordset 170
Methods of the Recordset 171
The State-Machine Concept 172
Designing a Robust User Interface 172
Houston, We Have a Problem 178
Enhancing Record Navigation 179
A Level of Indirection 180
Reusing the Code 180
ix