Michael Amundsen and Curtis Smith
201 West 103rd Street, Indianapolis, Indiana 46290
To our families. Their support, patience, and[md]above all[md]their love helped make this book possible.
FIRST EDITION
All rights reserved. No part of this book shall be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Neither is any liability assumed for damages resulting from the use of the information contained herein. For information, address Sams Publishing, 201 W. 103rd St., Indianapolis, IN 46290.
International Standard Book Number: 0-672-30832-0
Library of Congress Catalog Card Number: 95-70088
99 98 97 96 4 3 2 1
Interpretation of the printing code: the rightmost double-digit number is the year of the book's printing; the rightmost single-digit, the number of the book's printing. For example, a printing code of 96-1 shows that the first printing of the book occurred in 1996.
Composed in AGaramond and MCPdigital by Macmillan Computer Publishing
Printed in the United States of America
All terms mentioned in this book that are known to be trademarks or service marks have been appropriately capitalized. Sams Publishing cannot attest to the accuracy of this information. Use of a term in this book should not be regarded as affecting the validity of any trademark or service mark.
Publisher and President:
Richard K. Swadley
Acquisitions Manager:
Greg Wiegand
Development Manager:
Dean Miller
Managing Editor:
Cindy Morrow
Marketing Manager:
Gregg Bushyeager
Acquisitions Editor
Bradley L. Jones<
Development Editor
Ricardo Birmele
Software Development Specialist
Steve Flatt
Production Editor
Ryan Rader
Copy Editors
Marla L. Reece
Bart Reed
Technical Reviewer
Ricardo Birmele
Editorial Coordinator
Bill Whitmer
Technical Edit Coordinator
Lynette Quinn
Formatter
Frank Sinclair
Editorial Assistants
Sharon Cox
Andi Richter
Rhonda Tinch-Mize
Cover Designer
Tim Amrhein
Book Designer
Alyssa Yesh
Production Team Supervisor
Brad Chinn
Production
May An Abramson, Carol Bowers, Mona Brown, Charlotte Clapp, Terri Edwards,
Micheal Henry, Ayanna Lacey, Casey Price, Brian-Kent Proditt, SA Springer, Susan Van Ness
Introduction
Week at a Glance 1
Day 1: Your First Database Program in Visual Basic
Day 2: Creating Databases
Day 3: Using the Data Manager
Day 4: Visual Basic Database Objects
Day 5: Creating Data Entry Forms with Bound Controls
Day 6: Input Validation
Day 7: Creating Reports with Crystal Reports Writer
Week in Review 1
Week at a Glance 2
Day 8: Using the Visdata Program
Day 9: Selecting Data with SQL
Day 10: Visual Basic and the Microsoft JET Engine
Day 11: Creating Database Programs with Visual Basic Code
Day 12: Displaying Your Data with Graphs
Day 13: Data-Bound List Boxes, Grids and Subforms
Day 14: Error Trapping
Week in Review 2
Week at a Glance 3
Day 15: Advanced SQL I
Day 16: Advanced SQL II
Day 17: Database Normalization
Day 18: Multiuser Considerations
Day 19: ODBC Data Access Via the ODBC API Interface
Day 20: Securing Your Database Applications
Day 21: Preparing Online Help for Your Database Application
Week in Review 3
Appendix A: The SQL-VB Project
Appendix B: Answers to Quizzes and Exercises
We want to thank all the people at Sams for their assistance. Although many people have worked with us to bring this idea to its final form, we especially want to thank Brad, Marla, and Ryan for their help. Their persistence, insistence, and encouragement got us through the difficult spots and made the not-so-difficult spots even a bit fun. We also want to thank Ricardo Birmele for his unflagging support, even while he was pointing out our shortcomings.
The people mentioned here are just some of those who helped us in putting this book together. Where this book succeeds, it is due in large measure to the dedication and hard work of our friends and colleagues. We thank them all for their efforts and look forward to working with them on future projects.
Michael Amundsen has more than 10 years of database programming experience using dBASE, Clipper, FoxPro, and Visual Basic. He has worked in large corporate IS shops, small accounting offices, and an IS consulting firm. He recently completed his Microsoft Certification in Visual Basic and Windows 3.1 and now holds Microsoft's MCP.
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 4 in 21 Days. Curtis currently holds both an MBA and a CPA.
Welcome to Teach Yourself Database Programming with Visual Basic 4 in 21 Days. You'll cover a lot of ground in the next 21 lessons[md]from developing fully functional input screens with less than 10 lines of Visual Basic code, and writing Visual Basic code libraries to handle 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 will focus on a different aspect of database programming with Visual Basic. In week one, you learn about issues related to building simple database applications using the extensive collection of data controls available with Visual Basic. In week two, you concentrate on techniques for creating database applications using Visual Basic code. In week three, 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'll also learn techniques for creating ODBC-enabled Visual Basic applications.
This book will help 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 3 covers how to use the Visual Basic Data Manager program to create Microsoft JET format databases. Day 8 covers the use of the Visdata program to create and manage databases, and Day 9 teaches you to use SQL SELECT statements to organize existing data into usable data sets. On Days 15 and 16, you'll learn advanced SQL data definition and manipulation techniques, and on Day 17 you'll learn the five rules of data normalization.
Throughout the book, we show you how to develop code libraries that you can reuse in all your future Visual Basic programs. These code libraries include routines for input validation, error trapping, report printing, graphing data, creating input forms, user log in/log out, program security features, audit trails, and ODBC API interface. All of these libraries can be added to existing and future Visual Basic programs with very little, if any, modification. After you build these libraries, you'll be able to modify them to fit your specific needs, and even add new libraries of your own.
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.
All code examples in this book were built using Microsoft Visual Basic 4.0, Professional Edition. Most of the examples will work using Visual Basic 3.0, Professional Edition with the Microsoft JET/Access Compatibility Layer installed, but some will not. Version 4.0 of Visual Basic has several new features not available with 3.0. If you are using Visual Basic 3.0, you can still get a great deal out of this book, but we strongly encourage you to upgrade to Visual Basic 4.0. There are so many new features in Visual Basic 4.0 that you'll be glad you upgraded.
If you have Visual Basic 4.0 Enterprise Edition, you can take advantage of some new features not available in the Professional Edition, but this is not required. It will also help if you have Microsoft Word 6.0, which is used in the lesson on building help files.
Visual Basic 4.0 is available in both 16-bit and 32-bit versions. The 16-bit version of Visual Basic 4.0 will run under Windows 3.1, Windows for Workgroups, Windows 95, and Window NT. The 32-bit version of Visual Basic 4.0 will only run under Windows 95 or Windows NT. We run Visual Basic 4.0 under Windows 95, but almost all of the examples in this book will run under any version of Windows and Visual Basic 4.0. In the rare cases in which a feature only exists in the 32-bit version of the software, we let you know ahead of time and show how you can accomplish the same thing in the 16-bit environment.
Here is a brief rundown of what you'll accomplish each week.
In the first week, you will 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 Visual Basic Data Manager program to create and maintain databases. You will 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 level. Lastly, you'll learn how to use the Visual Basic Crystal Reports 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 one, 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 two will concentrate on topics that are of value to developers in the stand-alone and workgroup environments. We will cover a wide variety of topics including the following:
When you complete the chapters for week two, you will be able to build advanced database structures using the Visdata program and create complex data entry forms using Visual Basic code, including bound lists and grids, and error handling routines.
In the third and final week, we will cover several very important topics. This week's work focuses on database issues you'll encounter when you develop database applications for multiple users and/or multiple sites. You'll learn advanced SQL language for defining databases (DDL) and manipulating records within existing databases (DML). You'll 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 level. We also explain the advantages and limitations of adding cascading updates and deletes to your database relationship definitions. You will learn how to use 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 ODBC API calls to link directly with the ODBC interface to access data in registered ODBC data sources. You will also be able 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 log in and log out, program-level access rights, and audit trails to keep track of critical application operations.
Finally, you will learn how to design and build online help systems for your Visual Basic applications, including the 10-point checklist for creating quality help systems. You will also learn how to link help files directly to fields on a Visual Basic data form.
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 online help files.
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'll need for connecting your Visual Basic applications to remote databases, and not on how to operate these remote databases.
We also do not cover any third party controls or add-ins for Visual Basic 4.0. 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 ac obtain this free of charge from the Microsoft site at
http://www.microsoft.com/kb/softlib/mslfiles/what6.exe
http://www.microsoft.com/kb/softlib/mslfiles/comlyr.exe
WARNING: If you are using Visual Basic 4.0, you do not need to install the Microsoft JET/Access Compatibility Layer. In fact, instaE69E260">
Visual Basic Code Libraries
All reusable code libraries mentioned in the text are also included in a separate directory called \LIBS. This directory contains completed, fully documented versions of the code libraries used throughout the text. 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 what is included on the CD, here are some products that we recommend you pick up on your own to use.
- What6 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 free of charge from the Microsoft site at
http://www.microsoft.com/kb/softlib/mslfiles/what6.exe- Microsoft JET/Access Compatibility Layer for Visual Basic 3.0: If you are using Visual Basic 3.0 Professional with this book, you should install the Microsoft JET/Access Compatibility Layer in order to perform many of the examples in this text. You must have Microsoft Access on your workstation in order to complete the compatibility layer installation. This software can be obtained from the following Microsoft site:
http://www.microsoft.com/kb/softlib/mslfiles/comlyr.exe
WARNING: If you are using Visual Basic 4.0, you do not need to install the Microsoft JET/Access Compatibility Layer. In fact, installing the compatibility layer over Visual Basic 4.0 could result in disabling your current version of Visual Basic. If you are using Visual Basic 4.0, do not install these files on your workstation.
SQL-VB Interpreter
The \SQLVB directory contains the executables and the source code for the SQL-VB Interpreter program. This program is covered in Appendix A of this book. The SQL-VB program reads ASCII text files containing valid SQL scripts. The SQL-VB Interpreter can be used to create, modify, update, and delete Microsoft JET format databases.
Shareware and Demos
The CD 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.
In addition, the CD contains a complimentary copy of True Grid Pro[tm] version 2.1c from APEX Software.
