Here's a list of the most important new features of Visual Basic 4.0 that affect all Visual Basic developers, regardless of whether they use Visual Basic's Data Access Object (DAO):
Roger Jennings
201 West 103rd Street, Indianapolis, Indiana 46290
This book is dedicated to Maxwell Keith, attorney extraordinaire, and Terry Keith—firm friends through bad times and good.
SECOND 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-30652-2
Library of Congress Catalog Card Number: 95-67650
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.
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. Visual Basic is a registered trademark of Microsoft Corporation.
Publisher and President:
Richard K. Swadley
Acquisitions Manager:
Greg Wiegand
Development Manager:
Dean Miller
Managing Editor:
Cindy Morrow
Marketing Manager:
John Pierce
Assistant Marketing Manager:
Matthew Harris, author of Sams Publishing's Learn Visual Basic for Applications in 21 Days, updated chapters 3, 6, 7, 8, 10 through 12, 15, 25 and 27 of this edition. Matthew, who is a Oakland, CA–based database consultant, also is
the author of Sams Publishing's Teach Yourself Visual Basic for Applications in 21 days, a book on fixed disk data compression, and has contributed many chapters to books on programming Paradox and other database platforms. You can reach
Matthew on CompuServe at 74017,766.
The author is indebted to Neil Black and Stephen Hecht of Microsoft's Jet Program Management and Jet Development groups, respectively, for their "Jet Database Engine ODBC Connectivity White Paper." The "White Paper" made a
substantial contribution to the writing of Chapter 19, "Using the Open Database Connectivity API." Special thanks go to participants in the Visual Basic beta forum, as well as in the MSBASIC and MSACCESS forums on CompuServe, who provided
valuable tips, guidance, and suggestions in the course of writing this edition.
Thanks also are due to Robert Bogue, the technical editor for this book. Finally, a special note of appreciation to Greg Wiegand, Sams Publishing's acquisitions manager, for handling the administrative chores associated with producing the second
edition of this book.
Roger Jennings is a consultant specializing in Windows database and multimedia applications. He was a member of the Microsoft beta-test team for Visual Basic 2.0, 3.0, and 4.0; the Professional Extensions for Visual Basic 1.0 and 2.0; Visual
Basic for DOS; Microsoft Access 1.0, 1.1, 2.0, and 95; Word for Windows 2.0, the 32-bit version of Word 6.0, and Word 7.0; Excel 4.0, 5.0 (16- and 32-bit versions), and 7.0; Microsoft Project 4.0 and 4.1; Windows 3.1; Windows for Workgroups 3.1 and 3.11;
Windows 95; Windows NT 3.5 and 4.0 Workgroup and Server; the Microsoft ODBC 2.0 and 2.5 drivers; Microsoft SQL Server 6.0 and 6.5; Exchange Server 4.0; Microsoft Delta; Video for Windows 1.1; and Multimedia Viewer 2.0. Roger is the author of Sams
Publishing's Access 2 Developer's Guide and two other books on Microsoft Access, plus books devoted to Windows 95, Windows NT, and Windows multimedia techniques. He is the co-author with Peter Hipson of Sams' Database Developer's Guide
with Visual C++ 4.0. Roger is a contributing editor for Visual Basic Programmer's Journal and co-authored with Microsoft's Greg Nelson "A Client/server Application: From Concept to Reality," which appeared on the
Microsoft Developer's Network CD-ROM.
Roger has more than 25 years of computer-related experience, beginning with his work on the Wang 700 desktop calculator/computer, and he has presented technical papers on computer hardware and software to the Academy of Sciences of the former USSR, the
Society of Automotive Engineers, the American Chemical Society, and a wide range of other scientific and technical organizations. He is a principal of OakLeaf Systems, a Northern California software consulting firm; you may contact him via CompuServe (ID
70233,2161) or the Internet (70233.2161@compuserve.com).
The release of Visual Basic 4.0 marks a sea change in Microsoft's application programming languages: Visual Basic, Applications Edition, better known as Visual Basic for Applications (or VBA), is now the true lingua franca for developing 16- and
32-bit solutions with Microsoft's mainstream Windows productivity applications, including Excel, Access, and Project. VBA also is the programming language for the development tools that accompany members of Microsoft's new BackOffice server suite,
described in the "Visual Basic 4.0 and Microsoft BackOffice" section later in this introduction. Although Microsoft Word had not joined the VBA camp when this second edition of Database Developer's Guide with Visual Basic was
written, you're likely to see a 32-bit version of Word with VBA by late 1996. Therefore, use of the term Object Basic, which encompasses VBA, Access Basic, and Word Basic, in the first edition of this book has been replaced by VBA in this
second edition.
Microsoft's OLE 2.1 and the Component Object Model (COM) are now firmly entrenched as the new compound document standard for Windows, and OLE Automation replaces DDE as the primary means of interapplication communication. Big-time system resource
consumption by 16-bit OLE 2.0 mega-servers, typified by Excel 5.0 and Word 6.0, limited adoption of OLE Automation in commercial database front-ends. Windows 95 and Windows NT 3.51+ overcome most resource limitations when running 32-bit OLE 2.1
applications. Thus, 32-bit Visual Basic programs are likely to be the glue that binds industrial-strength solutions orchestrating members of 32-bit Microsoft Office with OLE Automation. Out-of-process OLE Automation servers, especially the big ones, are
not renown for their speed. Fortunately, there's a trend toward a Pentium on every power-user's desktop, so faster hardware comes to the rescue again. OLE (Custom) Controls (OCXs), which are in-process OLE Automation servers, don't suffer from the
performance hit associated with the Lightweight Remote Procedure Calls (LRPCs) required by out-of process servers. Thus, OCXs are almost as quick as VBXs, which OCXs are destined to replace.
Microsoft's addition of the Access database engine to Visual Basic 3.0 made this versatile Windows programming language a strong competitor to the desktop database market's principal players: Microsoft Access and FoxPro for Windows, Lotus Approach, and
Borland International's dBASE and Paradox for Windows. The Open Database Connectivity (ODBC) application programming interface (API), introduced in Visual Basic 2.0, made Visual Basic 3.0 a major factor in client/server front-end development throughout the
world. According to Microsoft, more than 60 percent of all Visual Basic 3.0 application development activity was devoted to manipulating databases. This percentage is likely to increase as developers upgrade to Visual Basic 4.0. The three major sections of
this introduction that follow describe the new database connectivity features of Visual Basic 4.0 and how Visual Basic 4.0 fits into the database front-end and back-end market.
New features of Visual Basic 4.0 fall into two basic categories:
The following two sections describe each of these feature categories in detail.
Version control is the primary issue associated with having two database development platforms, Visual Basic and Access, sharing the same database engine. Visual Basic 3.0 was designed around the Jet 1.1 database engine of Access 1.1, a set of
conventional dynamic link libraries (DLLs), which provided a basic set of database manipulation functions called by Visual Basic 3.0 keywords. Access 2.0 included an OLE 2.0 type library (TypeLib) layer, identified as "cdao," which implemented
the Data Access Object (DAO) supplied by Jet 2.0. Microsoft provided the Access 2.0/Visual Basic 3.0 Compatibility Layer to provide Visual Basic 3.0 connectivity to Access 2.0 .MDB files, bypassing the cdao TypeLib and calling the Jet 2.0 DLLs' database
functions directly. The Compatibility Layer supported self-contained features of Access 2.0 .MDBs, such as enforcement of domain and referential integrity rules created within Access 2.0. The Compatibility Layer did not, however, provide access to new Jet
2.0 DAO features, such as SQL Data Definition Language (DDL) and programmatic security for .MDBs.
Visual Basic 4.0 delivers all of the features of Access 2.0's DAO, and one-ups Access 2.0 by being first to provide the 32-bit version of enhanced Jet 2.5 and Jet 3.0 database engines. (Access 2.0 users need the Microsoft Access 2.0 Service Pack,
discussed in the "What You Need to Use This Book Effectively" section later in this chapter, to gain most of the Jet 2.5 enhancements.) The following list summarizes the Jet 2.5/3.0 features of Visual Basic 4.0, Professional Edition, that are new
to Visual Basic 3.0 programmers:
The most apparent and the most controversial change in Visual Basic 4.0 is the adoption of VBA's code editor, which displays all Visual Basic procedures for a container object within a single window. Changing the code editing metaphor to the VBA
standard caused a flurry of protest messages in the Visual Basic 4.0 beta forum on CompuServe. But the die was cast before the alpha version was released: All Microsoft implementations of VBA will share a common programming look and feel; resistance is
futile.
The following list briefly describes the most important differences between Visual Basic 3.0 and 4.0:
The last two new features of Visual Basic 4.0 are included only in the Professional Edition, which also includes the capability to "remote" your OLE 2.1 mini-servers to move from conventional two-tier to the new three-tier client/server
database architecture.
Making the choice between Microsoft Access, FoxPro for Windows, Borland Paradox for Windows and dBASE for Windows, or Microsoft Visual Basic 4.0 as your primary database development platform is not a simple matter, especially for seasoned developers of
character-based applications. FoxPro for Windows offers xBase language compatibility and, without question, remains the fastest of all the Windows desktop databases. Access 2.0 and 95 have an unique user interface that lets you develop
simple-to-moderately-complex database applications with a minimum of effort. The Paradox for Windows desktop RDBMS has a wider variety of field data types than any RDBMS, Windows or DOS, including the majority of today's client/server database systems.
Each of these development platforms has its own feature set, together with limitations that may not become apparent until you are embroiled in your first large-scale, client/server database front-end application. Chapter 1, "Positioning Visual Basic
in the Database Front-End Market," compares Visual Basic 4.0 with your alternatives among the major combatants in the Windows database wars.
If you have invested several years in developing xBase or PAL programming skills and are reluctant to abandon xBase RDBMSs or Paradox for a new object-oriented, event-driven Windows programming language, welcome to the crowd. COBOL programmers have the
same problem when their employers downsize from mainframe "legacy" databases to client/server systems running on PCs. Fortunately, Visual Basic resembles xBase and PAL more closely than COBOL resembles C or any other programming language common
to PCs. Unfortunately for PAL programmers, the ObjectPAL programming language of Paradox for Windows bears little relationship to the character-based PAL of Paradox 3.5 and its predecessors. xBase, PAL and Visual Basic, however, all have their roots in the
original Dartmouth BASIC. Thus, you are likely to find the structure of Visual Basic database applications quite similar to those xBase or PAL programs you are now writing or have written in the past.
Simply choosing FoxPro for Windows, dBASE for Windows, or Paradox for Windows because you're accustomed to writing xBase or PAL code is not likely to be a viable long-term solution to your Windows database development platform dilemma (notwithstanding
John Maynard Keynes's observation that "[i]n the long term, we are all dead"). If you create Windows database front-ends for a living, either as an in-house or independent developer, you are expected to provide your firm or client with
applications that incorporate today's new technologies. You need to prepare now for OLE 2.1, with its in-place activation and OLE Automation (OA), and Visual Basic for Applications (VBA). Windows is where the action is, and more than 100 million copies of
Windows 3+ and Windows 95 give Microsoft the marketing clout to make OLE 2.1, ODBC 2.5, and VBA the "standards" on the desktop computers of the world (whether the "industry" agrees or not). Microsoft announced in early 1996 that Visual
Basic Script (VBS), a subset of VBA whose formal name is Visual Basic, Scripting Edition, will extend VBA programmability to interactive World Wide Web pages. The alternative vaporware standards proposed by groups of software vendors
organized to combat the Microsoft behemoth are very unlikely to replace OLE, ODBC, and VBA in the foreseeable future.
Windows desktop database front-ends present a challenge to developers accustomed to writing a thousand or more lines of code to create character-based RDBMS applications in xBase, PAL, C, or other programming languages. You can create a very simple but
usable Visual Basic database front-end with the data control object and very little Visual Basic code. Microsoft Access offers code-free capabilities, if you don't consider Access macros to be code. You'll need to write substantial amounts of code to
create a usable database application with dBASE, FoxPro's xBase, or Paradox's ObjectPAL. However, the reality is that you have to write a substantial amount of code to create a commercial-quality production database application with any of these products.
The issue isn't how much code you have to write, but the language in which you write the code. Some of the language issues that will affect your career opportunities or the size of the numbers on your 1099s are these:
This book doesn't purport to answer these questions directly, but you're likely to reach your own conclusions before you finish reading Database Developer's Guide with Visual Basic 4.0.
At the time this book was written, Visual Basic 4.0 and Access 95 were the only database development platforms to fully support OLE 2.1+, OLE Automation, and OLE Controls. Visual Basic 4.0 is the only development platform, other than C++, that lets you
create your own OLE Automation mini-servers. Once you learn Visual Basic 4.0's implementation of VBA, you're grandfathered into proficiency in Access, Excel, Project, and Word VBA (when VBA-enabled Word appears). Chapter 14, "Integrating Database
Applications with OLE 2.1," describes how OLE 2.1, OLE Automation, and VBA fit into your decision support database front-ends.
Access 2.0 was released before the OLE Custom Control specification was finalized and many months prior to the retail release of Microsoft Visual C++ 2.0's Control Development Kit (CDK), which developers need to implement OCXs. Access 2.0's OC1016.DLL is not compatible with the final version of commercial OCXs designed for use with Visual Basic 4.0, which use OC25.DLL. The Access 2.0 Service Pack, described in the "What You Need to Use This Book Effectively," updates Access 2.0 to accommodate 16-bit OCXs based on Visual C++ 2.0's OC25.DLL. Access 95 fully supports 32-bit OLE Controls.
Whatever language you ultimately choose, you must adapt to the event-driven approach to application design, inherited from the Windows graphical user interface (GUI). You also need to face the fact that Windows applications will not perform with the
blazing speed of your Clipper or FoxPro applications running directly under DOS. Few, if any, Windows applications can match their DOS counterparts in a speed contest, but this situation is likely to change when you run your 32-bit database front-end under
Windows NT on a high-powered RISC workstation. Fortunately, most Windows users have grown accustomed to the sometimes sluggish response of Windows 3.1+. It's possible, however, to design Visual Basic 4.0 client/server front-ends that rival the performance
of their character-based counterparts. Chapter 13, "Designing Online Transaction Processing Systems," and Chapter 20, "Creating Front-Ends for Client/Server Databases," provide examples of "plain vanilla" front-ends that
deliver excellent performance.
When this edition was written, Microsoft Office 4.2+ had garnered more than 85 percent of the Windows productivity application suite (front-end) market. Microsoft Office 95 includes Excel 7.0, Word 7.0, PowerPoint 7.0, and a Microsoft Mail 3.2 client
license. The Professional version also includes Microsoft Access 7.0 (95). Stimulated by the success of Office, Microsoft introduced its server (back-end) suite, BackOffice, in Fall, 1994. Microsoft BackOffice comprises a bundle of the following server
products:
Like Microsoft Office, you get a substantial (about 40 percent) discount from the individual server license prices when you purchase the BackOffice bundle for $2,199 (estimated retail price, ERP). Unlike earlier versions of Windows NT Server and SQL
Server, which were available in "Enterprise" versions with unlimited client licenses, BackOffice does not include client licenses. Instead, using the BackOffice suite requires payment of a $309 (ERP) "per-seat" license fee for each
client workstation. Microsoft's executive vice-president for sales, Steve Ballmer, touted 1995 as the "year of BackOffice," and the primary focus of Microsoft's Tech*Ed 95 conference (held in New Orleans in late March 1995) was on the BackOffice
product line. It's much more likely that 1996 will be the "year of BackOffice," because of the major upgrades to BackOffice components during 1996.
Microsoft SQL Server 6+ and Exchange Server use OLE 2 and OLE Automation pervasively. The Microsoft Exchange 4.0 client uses OLE/Messaging 1.0 objects, and Schedule+ 7.0 has its own object library. As mentioned earlier in this introduction, the
development tools for the Microsoft SQL Server 6+ RDBMS and Exchange Server e-mail system use VBA. (Exchange Server uses a database structure similar to that of Jet 3.0, but optimized for messaging services.) Ultimately, all of the members of the
BackOffice suite, including the Internet Information Server, are likely to offer VBA extensions for customization. Microsoft has positioned Visual Basic 4.0 as the primary development platform for "building solutions" based on BackOffice servers.
According to the story "Steve Ballmer Never Had to Move So Fast" by Steve Hamm in the October 10, 1994 issue of PC Week, Ballmer's goal is to increase Microsoft's income from client/server products from 10 percent of revenue in 1994 to 25
percent by the end of the 1990s. Even if Ballmer doesn't meet his goal, Visual Basic developers will gain a huge new revenue base writing management applications for Microsoft Exchange and SQL Server 6+.
This book is intended primarily, but not exclusively, for the following categories of readers:
Database Developer's Guide with Visual Basic 4 assumes that you have experience with Visual Basic, Access Basic, or one of the traditional PC programming languages for Windows, such as Microsoft or Borland C or C++, Borland Delphi, or the
Windows version of SmallTalk. This book does not contain an introduction to Visual Basic programming techniques; many excellent tutorial and reference books are available to fill this need. (The bibliography that appears later in this introduction lists
some of the better books and other sources of information for beginning-to-intermediate-level Visual Basic Programmers.) Instead, Database Developer's Guide with Visual Basic 4 begins with an overview of how Visual Basic fits in the desktop and
client/server database market and proceeds directly to dealing with Data Access Object variables in Visual Basic. The entire content of this book is devoted to creating useful Visual Basic 4.0 database front-ends, and every example of the Visual Basic code
in this book, except one OLE Automation server, involves one or more connections to database(s).
All the code examples in this book, except for minor code fragments, are included on the accompanying CD-ROM. Sample databases in each of the formats supported by the 32-bit Jet 3.0 database engine are provided. None of the sample databases include
tables that relate to any form of Widget. Some of the sample databases are quite large so you can use their tables for performance comparisons. Tips and notes based on the experience of database developers with Visual Basic 4.0 and Access 95 appear with
regularity.
You need the Professional Edition of Visual Basic 4.0 to re-create or modify the sample applications that appear in Chapter 8, "Designing a Decision-Support Front-End," and those chapters that follow it. Although you can use the Data control
and the bound control objects of the Standard Edition of Visual Basic 4.0 to create simple database front-ends, the Professional Edition is a necessity for serious database development. The examples of Section VI, "Taking Advantage of Enterprise
Edition Features," require the Enterprise Edition of Visual Basic 4.0.
Windows NT 3.51+ Workstation or Windows 95 is required to compile and test 32-bit versions of your Visual Basic 4.0 database front-ends. All but one of the figures in this book illustrate the appearance of 32-bit sample applications running under
Windows 95.
Purchasing a copy of Access 95 is strongly recommended for those of you who intend to use Access .mdb database files in commercial front-ends. Creating new databases and adding tables is easier and faster when you use Access 95 instead of Visual Basic
4.0's Database Manager add-in. You can use Access 95's query design window to create a query graphically, test the result, and then copy the Access SQL statement underlying the query to your Visual Basic code. Using Access 95's Relationships window to
establish rules for enforcing referential integrity and creating business rules that maintain domain integrity is much simpler than Visual Basic 4.0's code-centric approach. An additional benefit of acquiring Access 95 is the availability of database
design and documentation tools for Access that are not yet available to Visual Basic database developers. Some of the new design and documentation tools for Access databases are described in Chapter 4, "Optimizing the Design of Relational
Databases," and in Chapter 25, "Documenting your Database Front-Ends." If you develop Visual Basic 4.0 database applications for a living, you'll save many times your investment in a copy of Access 95.
If you elect to use Access 2.0 with Visual Basic 4.0, make sure you obtain a copy of the Microsoft Access 2.0 Service Pack for Access 2.0. The primary value of the Service Pack for Visual Basic 4.0 users is the update that allows Access 2.0 to use
commercial OCXs created to the current version of the OLE Custom Control Specification 1.0. The Service Pack also takes care of system resource problems that occur when you print reports that include Microsoft Graph 5.0 objects.
The Service Pack updates the Jet 2.0 database engine to provide an enhanced repair facility and takes care of what Microsoft calls "isolated stability issues." Updated versions of the installable ISAM drivers for Btrieve, Paradox, and xBase tables also are included. Visual Basic 4.0 also performs these updates during its setup process. If you are using Access 2.0 on a computer other than that on which you installed Visual Basic 4.0, these additional updates are important to maintain consistency between the operation of Access 2.0 and Visual Basic 4.0.
If you use Access 95 .mdb files for multiuser database front-ends and want to scale up to a client/server database, the Microsoft Access Upsizing Toolkit is the answer. The Upsizing Toolkit, a free add-in for Access 95, available from http://www.microsoft.com, automatically exports tables from your .mdb files to a Microsoft SQL Server 4.2+ database object. The Upsizing Wizard included in the Upsizing Toolkit creates Transact-SQL (SQL Server's dialect
of SQL) DDL statements that create Server tables, update the tables with data from your .mdb file, and then add indexes and default values for table fields. The Wizard also writes the Transact-SQL CREATE TRIGGER code necessary to maintain domain and
referential integrity of SQL Server 4.21 databases. If you're running SQL Server 6+, which offers declarative referential integrity (DRI), the Upsizing Wizard writes ANSI SQL-92 SQL statements to enforce referential integrity at the database level.
Developers of commercial database front-ends with Visual Basic 4.0 are likely to want the additional features offered by third-party, data-aware OLE Controls. Data-aware OLE Controls enable you to attach the control to a field of a database table using
Visual Basic 4.0's data control as an intermediary. Although Microsoft has co-opted the data-aware grid, combo box, and list box VBX market by providing 16- and 32-bit OCX versions of these controls with Visual Basic 4.0, many third-party publishers offer
quite useful enhancements to Microsoft's set. Sources of these OCX controls are provided in Appendix A, "Resources for Visual Basic Database Front-Ends."
If you plan to localize Visual Basic front-ends for international distribution by using Windows resource files, you need Microsoft Visual C++ 2.0, Borland C++ 4.x, or another modern Windows C/C++ compiler in order to create both 16- and 32-bit
.RES files from .RC source files.
Microsoft SQL Server 6+ running under Windows NT Server 3.51+ is the most economical choice for developers who want to gain experience with an enterprise-quality client/server RDBMS. SQL Server 6.0 is used for all of the client/server examples in this
book. Now that Microsoft has altered the licensing policy for both Windows NT 3.51 Server and SQL Server to provide per-seat licenses for clients, the cost of acquiring the server components has decreased dramatically. (Per-sever licensing remains
available as an option.) One of the advantages of using Microsoft SQL Server is that you can run your client applications on the same computer that runs the RDBMS server. This is not possible with the majority of client/server RDBMSs. Microsoft SQL Server
6+ emulates Sybase SQL Server 4.x and Sybase System 10+ quite effectively; however, the SQL Server driver supplied with Visual Basic 4.0 does not let you take advantage of many of the new features in Sybase System 10+.
Most 16-bit Visual Basic 4.0 database front-ends for Windows 3.1+ will perform satisfactorily on 80386DX-33 or faster computers with 4MB or more of RAM. Windows 95 requires an 80486DX33 or faster and 8MB of RAM to provide reasonable performance. If you
plan to use Access 95, you should have a minimum of 16MB of RAM and an 80486DX2-66 or faster processor. If you plan to take full advantage of OLE 2.1 and OLE Automation, 16MB to 32MB of RAM is recommended. All of the 32-bit versions of the sample
applications of this book run satisfactorily under Windows NT 3.51 Workstation with 16MB of RAM.
The chapters in Section V, "Multiuser And Client/Server Database Front-Ends," use a variety of server and workstation configurations to demonstrate workgroup and client/server database networking techniques. All computers are networked with
10Mb/s Intel EtherExpress 16 network interface cards using thin Ethernet cabling. Following are the identifiers for each of the computers used to write this book, together with a brief description of the computer's configuration:
Database Developer's Guide with Visual Basic 4 is divided into seven parts containing a total of 27 chapters. Each part deals with related database-application-design subjects. The parts are ordered in a way that parallels a typical database
front-end development program. The content of each part and chapter of this book is described in the sections that follow.
Part I gives you an introduction to Visual Basic 4.0's capabilities as a Windows database front-end development environment. Chapter 1, "Positioning Visual Basic in the Database Front-End Market," analyzes the features that Visual Basic 4.0
offers database developers and how the language fits into Microsoft Corp.'s strategy to dominate the desktop and client/server database development markets. Chapter 2, "Understanding the Jet 3.0 Data Access Object," provides a detailed
description of how you create and manipulate Visual Basic database objects and collections, using Access .mdb databases in the examples. In Chapter 3, "Using Visual Basic's Data Control," you create a simple database front-end using the Data
control and other data-aware controls that are bound to the current record of database tables.
Part II deals with relational database design and using SQL to create SELECT and action (UPDATE, INSERT, and DELETE) queries that employ the Jet 3.0 database engine. Chapter 4, "Optimizing the Design of Relational Databases," shows you how to
normalize data in order to eliminate data redundancy in your front-end. Chapter 5, "Learning Structured Query Language," discusses ANSI SQL-89 and SQL-92, and how Access SQL differs from the "standard" SQL used by client/server and
mainframe databases. Chapter 6, "Connecting to Other Desktop Database Tables," provides insight on the use of the Jet 3.0 ISAM drivers with xBase, Paradox, and Btrieve tables. Chapter 7, "Running Crosstab and Action Queries," advances
beyond simple SQL SELECT queries and shows you how to write queries that include TRANSFORM, PIVOT, INTO, and other less commonly used SQL reserved words that modify the data in your tables.
Part III is devoted to creating commercial-quality, decision-support front-ends for databases. Chapter 8, "Designing a Decision-Support Front-End," describes the principals of converting raw data into easily comprehensible information that
can be displayed on Visual Basic forms. Chapter 9, "Using Advanced Data-Aware OLE Controls," shows you how to combine Visual Basic 4.0's 32-bit data-aware (bound) controls and VBA code to customize control operation. Chapter 10, "Graphing
Summary Data Created with Crosstab Queries," gives examples of using Visual Basic's Graph control to create the broad-brush summaries favored by management. Chapter 11, "Printing Reports with Code and Crystal Reports," shows you how to
design printed reports and how to seamlessly integrate report generation with your database front-ends.
Part IV takes you deeper into the realm of commercial database front-end development. Chapter 12, "Extending Your Use of the Data Access Object," shows you how to use the Graph control and Pinnacle Publishing's ChartBuilder OLE control to
create drill-down applications based on choices made by clicking hot spots on graphs and charts. Chapter 13, "Designing Online Transaction-Processing Systems," describes how to design forms for heads-down, high-speed data entry and how to use the
transaction-processing reserved words of Visual Basic to speed bulk updates to tables. Chapter 14, "Integrating Database Front-Ends with OLE 2.1," explains how you use in-place activation with OLE 2.1–compliant source applications. Excel 7.0
and Word 7.0 are used as OLE 2.1 source applications in this chapter. Chapter 15, "Using OLE Automation with Productivity Applications," describes the principles of OLE Automation and provides examples of Visual Basic 4.0 code that manipulate an
Excel 7.0 worksheet object. Chapter 15 also demonstrates the close relationship of Visual Basic's implementation of VBA and other VBA-enabled applications. Chapter 16, "Creating Local OLE Automation Servers and DLLs" describes how to create
database-oriented OLE Automation mini-servers (LOBJects) that you can use with VBA-enabled OLE 2 clients. Part IV concludes with Chapter 17, "Translating Access Basic and VBA Code to Visual Basic 4.0," for Access developers who are porting Access
2.0 and Access 95 applications to Visual Basic 4.0.
Up until Section V, Database Developer's Guide with Visual Basic 4 is devoted to self-contained applications designed for a single user. Section V provides the background and examples you need to add networking and client/server database
capabilities to your Visual Basic 4.0 database front-ends. Examples employ Windows for Workgroups 3.11, Windows 95, Windows NT 3.51+ Server, and SQL Server 6+. Chapter 18, "Running Visual Basic Database Front-Ends on Networks," describes how to
use peer-to-peer and network servers to share databases among members of a workgroup or throughout an entire organization. Chapter 19, "Using the Open Database Connectivity API," describes both the theoretical and the practical sides of ODBC and
how to set up ODBC data sources with the ODBC Administrator application in a client/server environment. Chapter 20, "Creating Front-Ends for Client/Server Databases," describes how to use the ODBC API to set up and connect to client/server and
mainframe data sources with your Visual Basic front-ends. Decision-support and online transaction-processing examples that connect Microsoft SQL Server are included. Chapter 21, "Integrating Databases with Intranets and the Internet," describes
how to use the Internet Data Connector of Microsoft's Internet Information Server 1.0 to run queries against SQL Server and Jet 3.0 databases. Chapter 21 also gives you a brief introduction to Visual Basic Script (VBS) and ActiveX Controls for creating
active World Wide Web pages.
The Enterprise Edition of Visual Basic 4.0 is designed primarily for developers of advanced 32-bit client/server database front-ends and complex applications that involve a group programming environment. Chapter 22, "Using the Remote Data
Object," describes how to take advantage of the new Remote Data Object (RDO) and its associated Remote Data control (RDC) to speed queries against client/server databases. The RDO also lets you take advantage of precompiled queries (prepared
statements) and server stored procedures. Chapter 23, "Creating and Managing Remote Automation Objects," introduces the use of out-of-process OLE Automation servers to create three-tier client/server database applications. Chapter 23 also shows
you how to use the Automation Manager and a pool manager with OLE Automation .exe files run on a server. Chapter 24, "Managing Large Projects with Visual SourceSafe 4.0," describes how to use Visual SourceSafe 4.0 for version management of
complex database applications.
No production database front-end is complete without full documentation and an online help system for users. Chapter 25, "Documenting Your Database Front-Ends," shows you how to use Visual Basic's database object collections to create a data
dictionary in the form of a text file that you can import into other applications, such as Word 7.0 or Excel 7.0. Chapter 26, "Creating Help Files for Database Front-Ends" describes how to use Word 7.0 and commercial 32-bit WinHelp assistants,
such as RoboHELP 95, to speed the addition of context-sensitive help to your Visual Basic 4.0 front-ends. The last chapter in the book, "Making Distribution Diskettes with the Visual Basic Setup Kit," shows you how to create a professional
installation program that rivals the capabilities and appearance of Microsoft's Setup application for mainstream Windows 95 and Windows NT applications.
The appendixes provide useful reference data for the explanatory material in the body of the book. Appendix A, "Resources for Visual Basic Database Front-Ends," lists the OLE Controls and other add-in products that add new features to your
database front-ends, as well as publishers of periodicals devoted to Visual Basic and databases in general. Suppliers and publishers are categorized by subject, and entries include addresses, telephone, and FAX numbers, as well as brief descriptions of the
products listed. Appendix B, "The Leszynski Naming Conventions for Microsoft Visual Basic," describes the prefixed tags used in this book to identify the object or data type of variables. The naming conventions are based on the system originally
developed by Stan Leszynski and Greg Reddick for naming Access objects and variables.
A glossary of terms that might be unfamiliar to those new to Windows programming, relational database front-ends, PC networking, and/or Windows NT appears after the two appendixes of this book. Emphasis is placed on defining the special terms,
abbreviations, and acronyms used in this book, especially new terms relating to Windows 95, Windows NT 3.51+, and OLE 2.1.
This book uses a variety of typesetting styles to distinguish between explanatory material, the entries you make in Windows dialogs, components of initialization (.INI) files, and the code you enter in Visual Basic's code editing window. The sections
that follow describe the typographic conventions used in this book.
The documentation and help files for Windows 95 and Windows NT use the term font to refer to what printers and graphic artists call a typeface. In the graphic arts industries, typefaces come in families. A type or typeface family consists of
typefaces with related designs in different styles (Roman for regular type, italic for cursive styles), weights (heavy, bold, demi-bold, book, and light), widths (expanded or condensed), and attributes (underlined, double-underlined, strikethrough). A font
is a specific size of a typeface with a single style, weight, and attribute, such as 12-point Futura bold italic condensed. This book uses the graphic-arts terminology in all explanatory material relating to type, both on your computer's video display unit
(VDU) and in printed output.
Accelerator (Alt+key) and shortcut (Ctrl+key) combinations that you use to substitute for mouse operations are designated by joining the keys with a plus sign (+). Ctrl+C, for example, is the shortcut key for copying a selection to the
Windows Clipboard. Alt+H is a common accelerator key combination that takes the place of clicking the Help button in dialogs. Some applications, such as Microsoft Word 6.0, use multiple-key shortcuts, such as Ctrl+Shift+key, to activate macros.
Accelerator keys for menu choices are indicated by underlining the accelerator key, as in File. Sequences of menu choices often are shown in the order needed to accomplish the action, such as File | Open. Ellipses after menu
choices that lead to dialogs do not appear in this book.
Menu and tabbed dialog choices use the vertical bar (pipe) separator, as in File | Open or, in an extended example from Word 7.0, Tools | Options | View | Show.DraftFont. Tools | Options is the menu choice,
View is the property page (tab) of the Options property sheet, Show is the category (property), and DraftFont is the (property) value. This style corresponds to menu hierarchy descriptions employed by many Microsoft Corp. developers, and corresponds, more
or less, to object hierarchy conventions.
Entries that you make in dialogs or at the Windows caret (the name used in this book for the insertion point in documents) are set in boldface type. Where menu choices require an entry such as a filename in a dialog, the filename is set in lowercase
letters in accordance with the standards set by Windows 3+—for example, File | Open vb3demo.mak. Completion of the entry by pressing the Tab key to move to another dialog control or by pressing the Enter key is assumed.
In the unlikely event that you need to press a succession of keystrokes, rather than a combination of keys, to accomplish an action, the necessary keys are separated by commas without intervening spaces, as in Alt,X,Y.
32-bit filenames use uppercase and lowercase characters, as in PerSel32.exe. To comply with Windows 95 and Windows NT 4.0 filename display conventions, file extensions appear in all lowercase. 16-bit filenames appear with all alphabetic characters
capitalized.
Examples of VBA code, SQL statements, and source code fragments in other programming languages are set in monospace type. Reserved words in VBA are set in bold monospace type. Keywords, such as the names of objects, object data types, and
collections, are set in regular-weight monospace type. Reserved words and keywords in ANSI SQL and xBase programming languages are set in UPPERCASE MONOSPACE type. Here's an example of the formatting of SQL statement examples:
SELECT Name, Address, City, Zip_Code FROM Customers WHERE _ Zip_Code >= 90000
The equivalent to the preceding statement in xBase is formatted as this:
USE customers LIST name, address, city zip_code WHERE zip_code >= 90000
For consistency, the Visual Basic line continuation character pair ( _) is used when the length of an SQL statement exceeds the printing width of the page, despite the fact that this separator pair is not valid when embedded in a SQL statement String
variable. Visual Basic functions that are allowed to be used in Access SQL are not set in bold monospace type in SQL statement examples.
Special implementations of SQL that do not conform to ANSI SQL-92 standards, such as the {ts DateVariable} syntax that Microsoft Query uses to indicate the timestamp data type, appear as in the SQL dialog of the application. The PIVOT and
TRANSFORM statements of Access SQL that (unfortunately) were not included in SQL-92, however, retain uppercase status.
Entries in Windows 3.1+, Visual Basic, and Microsoft Access 2.0 initialization (.INI) files appear in monospace type. Sections of .INI files are identified by square brackets surrounding the section identifier, as in
[Options] SystemDB=c:\vbapps\system.mda
Entries that you make in Windows 3.1+'s registration database, REG.DAT, or the Windows 95 and Windows NT Registry using the registration database editor application, REGEDIT.EXE, mode also appear in monospace type to preserve indentation that indicates
the level of the entry in the tree (file-directory-like) structure of Registry entries. The full path to HKEY. . . and other entries in the Registry of Windows 95 and Windows NT is provided unless otherwise indicated in the accompanying text.
As mentioned earlier, all examples of VBA code, as well as code examples in other Basic dialects, such as Access (2.0) Basic and Word Basic, are set in a monospace typeface. Monospace type also is used for code fragments that are mixed with
proportionally spaced explanatory text. Styles and weights are applied to code examples and fragments according to the following rules:
The code examples in this book use two- or three-letter prefixed tags to identify the data type of variables and symbolic constants of the fundamental data types of Visual Basic and other Object Basic dialects, as well as object variables. The prefix
tags used in this book are based on the Leszynski Naming Conventions for Microsoft Visual Basic (LNC). LNC is derived from a proposed standard for naming Access Basic objects and variables created by Stan Leszynski and Greg Reddick, which was originally
published in Pinnacle Publishing Inc.'s Smart Access newsletter.
All the code examples in this book and on the accompanying CD-ROM use Option Explicit to specify that variables must be declared with Public, Private, Const, and/or Dim statements prior to assigning the variable a
value in procedures. This book uses explicit data type assignment; variables of the Variant data type are declared As Variant, despite Visual Basic's assignment of Variant as the default data type. You won't find data type symbols,
such as %, @, and ! used in this book, except for the $ symbol following the names of functions that explicitly return values of the String data type. The bang (!) symbol is reserved as a separator in statements
that specify objects within container objects. Prefixed tags, related to those of Hungarian notation for C code, maintain consistency in data type identification for both conventional and object variables.
Examples of tagged variable names of the fundamental data types are strStringVar, intIntegerVar, lngLongVar, datDateTimeVar, curCurrencyVar, and varVariantVar. It is arguable whether the Variant data
type is appropriately classified fundamental data type. Microsoft Corp. and this book use the term fundamental data type to distinguish conventional variables, including variables of the Variant data type, whose names are VBA reserved words,
from variables of object data types, which may have names that are either reserved or keywords.
Prefix tags also are used to identify the type of object when you declare variables of the various object data types supported by Visual Basic. The most common object prefix tags in this book are wsWorkSpace, dbDatabase,
rstTableRecordset, rsdDynasetRecordset (updatable), rssSnapshotRecordset (not updatable), and qdfQueryDef. Prefix tags for variables of the Object data type use a two-letter code for the application with an optional third
letter, as in xlaApplication, xlwWorkBook and xlsWorkSheet (Excel 5+), mpObject (Microsoft Project 4+), wwObject (Word [for Windows] 6+), and cdObject (CorelDRAW! 5+).
Prefix tags also are used to identify user-defined data types declared with Type. . .End Type statements. Appendix B provides detailed information on the derivation and use of type identifier prefix tags.
As mentioned earlier in this introduction, Database Developer's Guide with Visual Basic 4 is intended for readers who are familiar with writing Visual Basic 3.0, Visual Basic for Applications, and/or Access Basic code. If your first
Visual Basic application is a full-fledged database front-end, you may want to acquire one or more tutorial or reference books on introductory or intermediate-level VBA programming. Access Basic programmers who are porting Access 2.0 applications to Visual
Basic 4.0 will benefit from developer-level Visual Basic guides. You also may want more details on the 1992 version of ANSI SQL, SQL-92, and the background of the ODBC API. The following sections provide recommendations of up-to-date books that fulfill
these needs.
The following books are designed to introduce database programmers to Visual Basic 4.0's event-driven graphical programming environment:
Teach Yourself Visual Basic for Applications in 21 Days by Matthew Harris (Indianapolis, IN, 1994, Sams Publishing, ISBN 0-672-30447-3)
Teach Yourself Visual Basic 4 in 21 Days, Third Edition by Nathan & Ori Gurewich (Indianapolis, IN, 1995, Sams Publishing, ISBN 0-672-30620-4)
Essential Visual Basic 4 by Mark Heyman (Indianapolis, IN, 1995, Sams Publishing, ISBN 0-672-30771-5)
The following books cover intermediate-to-advanced Visual Basic programming topics:
Real-World Programming with Visual Basic 4, Second Edition by Anthony T. Mann (Indianapolis, IN, 1995, Sams Publishing, ISBN 0-672-30779-0)
Visual Basic Performance Tuning and Optimization by Keith Brophy and Tim Koets (Indianapolis, IN, 1995, Sams Publishing, ISBN 0-672-30796-0)
If you want to fully understand the history and implementation of the American National Standards Institute's X3.135.1-1992 standard for SQL-92, you need a copy of Jim Melton's and Alan R. Simpson's Understanding the New SQL: A Complete Guide,
ISBN 1-55860-245-3 (San Mateo, CA, Morgan Kaufmann Publishers, 1993.) Jim Melton of Digital Equipment Corp. was the editor of the ANSI SQL-92 standard, which comprises more than 500 pages of fine print.
The syntax of SQL is the subject of a standard that is published by the American National Standards Institute (ANSI). As of this writing, the current standard, X3.135.1-1992 or SQL-92, was available from
American National Standards Institute
11 West 42nd Street
New York, NY 10036
(212) 642-4900 (Sales Department)
The SQL Access Group (SAG) consists of users and vendors of SQL database management systems. SAG publishes standards that supplement ANSI X3.135.1-1989, such as the Call-Level Interface (CLI) standard used by Microsoft's ODBC API. You can obtain SAG
documents from
SQL Access Group
1010 El Camino Real, Suite 380
Menlo Park, CA 94025
(415) 323-7992 x221
A variety of sources of up-to-date information are available to Visual Basic developers in print and electronic formats. Both print periodicals and online sources address management and development issues that are applicable to database development as
a whole. Several forums on CompuServe Information Services offer product support services for Access and Windows. The sections that follow describe some of the sources you can use to expand your Visual Basic horizons.
The following are a few of the magazines and newsletters that cover Access exclusively or in which articles on Microsoft Access appear on a regular basis:
The majority of these magazines are available from newsstands and bookstores. Names and addresses of the publishers are listed in Appendix A.
Microsoft and other firms sponsor several product support forums on CompuServe Information Service's online information utility. These forums are a vital source of information for Visual Basic database developers:
If you don't have a CompuServe account, call (800) 848-8199 for information on how to obtain your CompuServe ID number.
The Internet rapidly is overtaking commercial online services as a source of information and, to a lesser extent, peer-to-peer support for Visual Basic database developers. The Microsoft Network (MSN) also provides bulletin boards for Visual Basic and
Access. When this edition was written, Microsoft was in the process of integrating MSN with the Internet as a value-added service.
The MS Visual Basic Support Desktop (Go Word msvb_sd) provides a members' BBS, Visual Basic FAQs (Frequently Asked Questions), links to a variety of Visual Basic–related Microsoft Web pages, and a link to the Microsoft Knowledge Base via the
Internet.
The Microsoft Access 95 Forum (Go Word msaccess) provides a number of BBSs devoted to 32-bit Access and the Jet database engine, including support services for Access 95.
The Microsoft Developer Product Information Pages (http://www.microsoft.com/DevOnly/prodinfo/) provides links to news about Microsoft's Visual Basic and Access product lines.
The Macmillan Information SuperLibrary (http://www.mcp.com) is an award-winning Web site that provides a complete online catalog of all computer-related books published by Macmillan Computer Publishing. The SuperLibrary
also includes an online newsletter, press releases, and online bookstore. A wide variety of source code from MCP books, shareware, freeware, and demo software is available for downloading.
The VBPJ Development Exchange (http://www.windx.com) includes the following sections: Developer's Resources, Visual Basic Tools, Visual C++ Tools, Access Tools, Visual FoxPro Tools, BackOffice Tools, and OLE Tools.
The VBPJ Development Exchange Web site is a service of Fawcette Technical Publications, publishers of Visual Basic Programmer's Journal and Microsoft Interactive Developer magazine.
Fawcette Technical Publications calls its Avatar Web site (http://www.avatarmag.com) "The Interactive Developer's Online Magazine." Avatar is designed to complement Microsoft Interactive Developer
Magazine, published by Fawcette. The Avatar Magazine site was in the alpha testing stage when this edition was written and is expected to be in full operation by mid-1996.
The comp.lang.basic.visual.databases and comp.databases.ms-access newsgroups are two of the most active Usenet areas devoted to Microsoft Visual Basic database and Access programming issues.
