|  | Click Here!

Search the site:
EXPERT SEARCH

| Programming Languages
| Databases
| Security
| Web Services
| Network Services
| Middleware
| Components
| Operating Systems
| User Interfaces
| Groupware & Collaboration
| Content Management
| Productivity Applications
| Hardware
| Fun & Games

EarthWeb sites
Crossnodes
Datamation
Developer.com
DICE
EarthWeb.com
EarthWeb Direct
ERP Hub
Gamelan
GoCertify.com
HTMLGoodies
Intranet Journal
IT Knowledge
IT Library
JavaGoodies
JARS
JavaScripts.com
open source IT
RoadCoders
Y2K Info

#

- Teach Yourself
  - DATABASE PROGRAMMING WITH VISUAL BASIC® 5
  - in 21 Days, Second Edition
- Acknowledgments
  - About the Authors
    - Tell Us What You Think!
  - Introduction
    - Welcome to Database Programming in Visual Basic 5
    - Database Design Skills
    - ActiveX DLLs and Custom Controls
  - Who Should Read This Book
    - What You Need to Use This Book
    - Quick Course Summary
      - Week 1: Data Controls and Microsoft Jet Databases
      - Week 2: Programming with the Microsoft Jet Database Engine
      - Week 3: Advanced Database Programming with SQL and ODBC
      - The Appendixes
    - Whats Not Covered in This Book
    - Whats on the CD-ROM?
      - Chapter Projects and Examples
      - Visual Basic Code Libraries, DLLs, and Custom Controls
    - Recommended Files
      - SQL-VB5 Interpreter
      - Shareware and Demos
      - Online Resources

# 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.

| Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details.