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.
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.
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
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.
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.
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.
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.
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.
Here is a brief rundown of what you accomplish each week.
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 concentrates on topics that are of value to developers in the standalone and workgroup environments. We cover a wide variety of topics, including:
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.
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.
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.
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.
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.
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.
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.
Besides the contents of the CD, we recommend you pick up the following product on your own:
http://www.microsoft.com/kb/softlib/mslfiles/what6.exe
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.
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.
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.
