 
Teach Yourself
DATABASE PROGRAMMING
WITH VISUAL BASIC® 5
in 21 Days, Second Edition
Acknowledgments
There are a number of people to thank this time around. First, of course are the
thousands of kind people who read our first edition. We were lucky enough to receive
many comments from readers. Many of your suggestions and requests have been added
to this second edition.
We'd also like to thank all of those colleagues and clients who have become test
subjects for our theories and sample projects. Even though some of you were not really
"willing subjects," your support and encouragement were invaluable in allowing
us to continue to experiment with Visual Basic in the real world instead of at some
isolated test terminal.
Finally, the folks at Sams Publishing continued to provide invaluable assistance
and support as we worked through this new edition. So many people work on a book
that trying to name even a few only serves to point out many uncredited others. If
this book reads well, looks good on paper, and covers the topics most requested,
that is due in large part to the good work of the people at Sams. Any typos, errors,
and omissions that remain exist despite their tremendous efforts.
About the Authors
Mike Amundsen Mike Amundsen works as an IS Consulting and Training Specialist
for Design-Synergy Corporation, a consulting and project management firm specializing
in information technology services. He travels the U.S. and Europe teaching and consulting
on Windows development topics.
Mike's other book projects include authoring the MAPI, SAPI, and TAPI Developer's
Guide published by Sams Publishing, and contributing to Sams Publishing's Visual
Basic 4 Unleashed and Sams' VB4 Developer's Guide. Mike is a contributing editor
for Cobb's Inside Visual Basic for Windows newsletter and regularly contributes to
Cobb's Access Developer's Journal, Inside Microsoft Access, and Microsoft Office
Developer's Journal. His work has also been published in Visual Basic Programmer's
Journal magazine and VB Tech magazine.
When he's not busy writing or traveling to client sites, Mike spends time with
his family at his home in Kentucky. You can reach Mike at his CompuServe address--102461,1267
or at MikeAmundsen@msn.com on the Internet. Curtis Smith Curtis Smith has
been working in the computer industry for many years. He has a financial background,
which helps to bring a practical real-world flair to Teach Yourself Database Programming
with Visual Basic 5 in 21 Days. Curtis has worked in the federal government, and
in the banking, transportation, and pharmaceutical industries. He has significant
experience implementing financial, project management, inventory, and maintenance
software applications. Curtis currently holds an MBA from Miami University (Oxford,
Ohio), and is a Certified Public Accountant in the state of Ohio.
Tell Us What You
Think!
As a reader, you are the most important critic and commentator of our books. We
value your opinion and want to know what we're doing right, what we could do better,
what areas you'd like to see us publish in, and any other words of wisdom you're
willing to pass our way. You can help us make strong books that meet your needs and
give you the computer guidance you require.
Do you have access to CompuServe or the World Wide Web? Then check out our CompuServe
forum by typing GO SAMS at any prompt. If you prefer the World Wide Web,
check out our site at http://www.mcp.com.
NOTE: If you have a technical question about
this book, call the technical support line at 317-581-3833.
As the publishing manager of the group that created this book, I welcome your
comments. You can fax, e-mail, or write me directly to let me know what you did or
didn't like about this book--as well as what we can do to make our books stronger.
Here's the information:
Fax: 317-581-4669
E-mail: programming_mgr@sams.samspublishing.com
Mail: Greg Wiegand Sams Publishing
201 W. 103rd Street Indianapolis, IN 46290
Introduction
Welcome to Database
Programming in Visual Basic 5
Welcome to Teach Yourself Database Programming with Visual Basic 5 in 21 Days,
Second Edition. You cover a lot of ground in the next 21 lessons--from developing
fully functional input screens with fewer than 10 lines of Visual Basic code and
writing Visual Basic code libraries, to handling complex user security and auditing
in multiuser applications, to creating online help files for your Visual Basic programs,
and much more. Whether you are a power user, a business professional, a database
guru, or a Visual Basic programmer, you'll find something in this book to help you
improve your Visual Basic and database skills.
Each week you focus on a different aspect of database programming with Visual
Basic. In Week 1, you learn about issues related to building simple database applications
using the extensive collection of data controls available with Visual Basic. In Week
2, you concentrate on techniques for creating database applications using Visual
Basic code. In Week 3, you study advanced topics such as SQL data definition and
manipulation language, and issues for multiuser applications such as locking schemes,
database integrity, and application-level security. You also learn techniques for
creating ODBC-enabled Visual Basic applications.
Database Design
Skills
This book helps you develop your database design skills, too. Each week covers
at least one topic on database design. Day 2 covers Visual Basic database data types,
and Day 7 covers the use of the Visdata program to create and manage databases. Day
8 teaches you to use SQL SELECT statements to organize existing data into
usable datasets. On Days 13 and 15, you learn advanced SQL data definition and manipulation
techniques, and on Day 16 you learn the five rules of data normalization.
ActiveX DLLs and
Custom Controls
Throughout the book, we show you how to develop DLLs and custom controls that
you can reuse in all your future Visual Basic programs. This includes components
for input validation, error trapping, report printing, graphing data, creating input
forms, user log in/log out, program security features, audit trails, and the ODBC
API. All of these components can be added to existing and future Visual Basic programs
with very little, if any, modification. After you build these libraries, you can
modify them to fit your specific needs, and even add new libraries of your own.
Who Should Read
This Book
This book is designed to help you improve your database programming skills using
Visual Basic. You do not have to be a Visual Basic coding guru to use this book.
If you are a power user who wants to learn how to put together simple, solid data
entry forms using Visual Basic, you'll get a lot from this book. If you have some
Visual Basic experience and want to take the next step into serious database programming,
you'll find a great deal of valuable information here, too. Finally, if you are a
professional programmer, you can take many of the techniques and code libraries described
here and apply them to your current projects.
What You Need to
Use This Book
Most of the code examples in this book were built using Microsoft Visual Basic
5, Professional Edition (the Remote Data Control and Remote Data Objects can only
be used with the Enterprise edition of Visual Basic 5). Most of the examples work
using Visual Basic 4, Professional Edition but some do not. Version 5 of Visual Basic
has several new features not available with version 4. If you are using Visual Basic
4, you can still get a great deal out of this book, but we strongly encourage you
to upgrade to Visual Basic 5. There are lots of new features in Visual Basic 5 and
you'll be glad you upgraded.
If you have Visual Basic 5 Enterprise Edition, you can take advantage of some
new features not available in the Professional Edition, but this is not a necessity.
It also helps if you have Microsoft Word, which is used in the lesson on building
help files.
Visual Basic 5 is only available in a 32-bit version. That means you need to run
Visual Basic (and its completed projects) under Windows 95 or Windows NT.
Quick Course Summary
Here is a brief rundown of what you accomplish each week.
Week 1: Data Controls and Microsoft
Jet Databases
In the first week, you learn about the relational database model, how to use the
Visual Basic database objects to access and update existing databases, and how to
use the Visdata program to create and maintain databases. You also learn how to design
and code data entry forms (including use of the Visual Basic bound data controls),
and how to create input validation routines at the keystroke, field, and form levels.
Lastly, you learn how to use the Visual Basic Crystal Reports Pro report writer to
design simple reports, and how to use the Crystal Reports control to run those reports
from within your Visual Basic programs.
When you complete the work for Week 1, you will be able to build Microsoft Jet
databases, create solid data entry forms that include input validation routines,
and produce printed reports of your data.
Week 2: Programming with the Microsoft
Jet Database Engine
Week 2 concentrates on topics that are of value to developers in the standalone
and workgroup environments. We cover a wide variety of topics, including:
- How to use the Structured Query Language (SQL) to extract data from existing
databases.
- What the Microsoft Jet engine is, and how you can use Visual Basic code to create
and maintain data access objects.
- How to create data entry forms with Visual Basic code.
- How to use the Microsoft graph control to create graphs and charts of your data.
- How to use data-bound list boxes, data-bound combo boxes, and data-bound grids
to create advanced data entry forms.
- How to make applications more solid with error trapping.
When you complete the chapters for Week 2, you will be able to build advanced
database structures using the SQL language, and create complex data entry forms using
Visual Basic code, including bound lists and grids, and error-handling routines.
Week 3: Advanced Database Programming
with SQL and ODBC
In the third and final week, we cover several very important topics. This week's
work focuses on the database issues you encounter when you develop database applications
for multiple users and/or multiple sites. You learn advanced SQL language for manipulating
records within existing databases (DML). You also learn the five rules of data normalization
and how applying those rules can improve the speed, accuracy, and integrity of your
databases.
We cover Visual Basic database locking schemes for the database, table, and page
levels. We also explain the advantages and limitations of adding cascading updates
and deletes to your database relationship definitions. You learn how to use the Visual
Basic keywords BeginTrans, CommitTrans, and Rollback to
improve database integrity and processing speed during mass updates.
We show you how to write data entry forms that use the Remote Data Control, Remote
Data Objects, and ODBC API calls to link directly with the ODBC interface to access
data in registered ODBC data sources. You also learn how to install the ODBC Administrator
and create new ODBC data sources for your ODBC-enabled Visual Basic programs.
We review application-level security schemes such as user login and log out, program-level
access rights, and audit trails to keep track of critical application operations.
You also learn how to use the Microsoft Replication Manager to establish and maintain
database replication schemes to protect and update your mission-critical distributed
data.
When you finish the final week of the course, you will be able to use advanced
SQL statements to create and maintain databases. You will also be able to build solid
multiuser applications that include database locking schemes, cascades, and transactions;
ODBC API interfaces; application security and audit features; and you will be able
to manage distributed data through replication.
The Appendixes
There is additional material in the Appendixes, too. There's a detailed explanation
of the SQL-VB5 Interpreter used throughout the book (including the source
code) and a complete chapter on writing your own online help files for your Visual
Basic applications.
Whats Not Covered
in This Book
Although there is a lot of good stuff in this book, there are some important topics
we don't cover in these pages. For example, we don't talk in detail about Visual
Basic coding in general. If you are new to Visual Basic, you might want to review
the book Teach Yourself Visual Basic in 21 Days. This is an excellent introduction
to Visual Basic.
Although we discuss issues such as connecting to back-end databases such as SQL
Server and Oracle, we do not cover the specifics of these systems. We focus on techniques
you need for connecting your Visual Basic applications to remote databases, and not
on how to operate remote databases.
We also do not cover any third-party controls or add-ins for Visual Basic 4. That
isn't because we don't think they are useful. There are literally hundreds of new
and existing third-party products for Visual Basic, and many of them are very good.
We have included samples and demo versions of some of those third-party products
on the accompanying CD-ROM. However, because we wanted the book to be as accessible
as possible to all our readers, we use only those controls or add-in products that
are included in the Visual Basic 5 Professional Edition.
Whats on the CD-ROM?
In the back of this book, there is a CD-ROM that contains lots of Visual Basic
code, sample and demonstration programs, and handy utilities. Following is a brief
description of the contents of the CD. Refer to the installation directions on the
last page of the book for details on how to install and run these programs.
Chapter Projects
and Examples
All examples and exercises mentioned in this book are stored in the TYSDBVB
directory of the CD-ROM. You can copy these files directly to your workstation hard
disk or enter them from the listings in the book.
Visual Basic Code
Libraries, DLLs, and Custom Controls
All reusable code libraries mentioned in the text are also included on the CD.
If you want to save yourself some typing, you can simply add these libraries to your
Visual Basic projects. You can also copy these libraries to your workstation hard
drive and modify them for your own use.
Recommended Files
Besides the contents of the CD, we recommend you pick up the following product
on your own:
- MS Windows 95 Help Authoring Kit: This is Microsoft Corporation's Help authoring
kit, which includes all the tools you need to convert Microsoft Word formatted documents
into compiled help files for your Visual Basic application. You can obtain this from
Microsoft press or find a freeware version of it (called What6) from the
Microsoft site at
http://www.microsoft.com/kb/softlib/mslfiles/what6.exe
SQL-VB5 Interpreter
The \SQLVB5 directory contains the executables and the source code for
the SQL-VB5 Interpreter program. This program is covered in Appendix A.
The SQL-VB5 program reads ASCII text files containing valid SQL scripts.
The SQL-VB5 Interpreter can be used to create, modify, update, and delete
Microsoft Jet-format databases. A number of new features have been added to SQL-VB5
including the ability to handle non-Jet data formats and the new OLE Automation wrapper
to allow you to call SQL-VB5 from other VBA-compliant programs.
Shareware and Demos
The CD-ROM also contains various shareware and demo versions of third-party software.
We encourage you to test these software tools and, if you like what you find, support
the software authors by purchasing a licensed copy of the programs you find useful.
Online Resources
We encourage you to keep in touch with us electronically. You can visit our Web
site at www.amundsen.com/tysdbvb
and e-mail us at MikeAmundsen@msn.com
and Curtis_Smith@fuse.net. Additional
information on our Web site and other valuable Visual Basic online resources can
be found in the resource.htm file on the CD-ROM.
 
|