Between November, 1992 and May, 1993, Microsoft Corporation introduced three new Windows relational database products: Access 1.0, FoxPro 2.5 for Windows, and Visual Basic 3.0. Microsoft heralded Access as the "database that anyone can use"
and sold 750,000 bargain-priced copies in 90 days. FoxPro for Windows targeted existing FoxPro developers and prospective users of Borland International's then long-promised dBASE for Windows. Both Access and FoxPro were aimed at the market for Borland's
Paradox for Windows, which emerged shortly after the retail release of Access 1.0. When Database Developer's Guide with Visual Basic 4, the second edition of this book, was written, Microsoft had sold more than four million copies of Access versions
1.0, 1.1, and 2.0 and claimed more than two million Visual Basic 1.0, 2.0, and 3.0 users. Between mid-August and mid-November, 1995, Microsoft released Windows 95 and 32-bit, "designed for Windows 95" versions of Access (7.0), Visual FoxPro
(3.0), and Visual Basic (4.0), together with 32-bit Microsoft Office 95. Behind the scenes of the Windows 95 marketing blitz were hundreds of developers in Redmond making sure that early adopters of Windows 95 would have 32-bit applications to run when
Windows 95 hit the retail shelves.
Microsoft Access, Visual FoxPro, plus Borland International's dBASE and Paradox for Windows are categorized as desktop databases. Desktop databases are applications that employ a proprietary (native) database file or index structure, or both, and can
be run on a PC of moderate performance (for example, an 80486DX33-based computer with 8M of RAM). (Access for Windows 95 requires 12M of RAM and runs far better with 16M or more. You can launch Access 95 from Windows 95 in 8M of RAM, but that's
about all you can do.) Desktop databases also include an embedded application programming language that is designed specifically for use with the native database structure. Visual Basic for Applications (VBA) is Microsoft's application programming language
of choice for all of its productivity software; only Visual FoxPro remains firmly entrenched in the xBase camp. Microsoft promises that the next version of 32-bit Word will replace or supplement Word Basic with VBA.
Visual Basic for Windows has been Microsoft Corporation's most successful programming language product, as witnessed by world-wide sales of more than two million copies. Microsoft's original objective for Visual Basic was to expand the market for
Windows 3.x by providing a means for fledgling programmers to create their own Windows applications without having to learn the C programming language. Microsoft achieved this goal with Visual Basic 1.0. Many experienced programmers abandoned C,
C++, and Pascal in favor of Visual Basic, because they could develop Windows applications much faster than with traditional programming languages. Microsoft enriched Visual Basic 2.0 with object variables, MDI forms, and other enhancements and added a
limited set of database connectivity features. Independent firms created a wide variety of VBX (Visual Basic eXtension) custom controls and add-in products for Visual Basic 1.0 and 2.0, the majority of which addressed database applications.
By early 1993, a Microsoft market study showed that more than 70 percent of all Visual Basic applications involved databases. Even before the introduction of Visual Basic 3.0 with its Microsoft Jet Data Access Object (DAO), which greatly enhanced
database functionality, Visual Basic was a major but unrecognized player in the Windows database market. The failure of market research firms to place Visual Basic 1.0 and 2.0 in the Windows database category caused signification distortion of the desktop
database market statistics for 1992 and 1993. By mid-1995, Visual Basic 3.0 had become by far the most popular Windows programming tool for writing front-ends for client/server databases, despite the publicity garnered by competing development tools, such
as Sybase's PowerBuilder and Borland International's Delphi.
One of Microsoft's primary objectives for Visual Basic 4.0 is to provide a 32-bit application development environment to complement the Microsoft BackOffice server suite running on Windows NT. Visual Basic 4.0 and its future successors are Microsoft's
"FrontOffice" for the firm's assault on the enterprise-wide networking and information systems business. BackOffice includes distributed database management with SQL Server 6.0, messaging with Microsoft Mail 3.5, groupware via Exchange Server,
mainframe connectivity using SNA Server, and software/hardware management with SMS Server. Microsoft's aggressive pricing for BackOffice server and client licenses, combined with Windows NT's capabilities as an application server has made Microsoft a major
player in the enterprise computing market.
This chapter describes Visual Basic 4.0's role in Windows database application development and shows how Visual Basic 4.0, 32-bit OLE (Object Linking and Embedding) Automation, and Visual Basic for Applications (VBA) fit into Microsoft Corporation's
strategy to maintain its domination of the Windows application marketplace. The chapter also discusses the advantages and drawbacks of using Visual Basic 4.0 for database applications and gives you a preview of many of the subjects that are covered in
detail in the remaining chapters of this book. It's becoming a 32-bit, "designed for Windows 95" world out there, so this book concentrates on 32-bit application development with Visual Basic 4.0.
Visual Basic 4.0's advanced database connectivity and data-handling features qualify the language as a full-fledged database application development environment. The new data access features that Microsoft added to Visual Basic 3.0 positioned the
product as a direct competitor to Access, FoxPro, and Paradox for Windows in the desktop database market. Visual Basic 4.0 Enterprise Edition's Visual Remote Data Object brings high-performance processing of client/server queries, and integrated Visual
SourceSafe makes practical team development of large database applications. Visual Basic 4.0's primary advantages over its database competitors are performance, simplicity, flexibility, and extensibility:
The most important benefit of selecting Visual Basic 4.0 as your primary database development platform, however, is not evident in the preceding feature list. Visual Basic 4.0 has adopted Visual Basic Applications Edition version 2.0; Microsoft's
generic application programming (macro) language, which first appeared in Excel 5.0. VBA has its roots in the original Word Basic macro language and Embedded Basic (EB), which first appeared in early beta versions of Access 1.0. (You still see an
occasional reference to "EB" in current releases.) The syntax of VBA is almost identical to that of Visual Basic 3.0 code, so acclimating to Visual Basic 4.0's version of VBA and the new code editor is a quick and easy process. Although each
application that embeds VBA has its own object hierarchy, the syntax of VBA is virtually identical in Access 95, Excel 5+, Project 4+, and Visual Basic 4.0. Learn one "dialect" of VBA and you've learned them all. A common VBA promises to greatly
increase the efficiency of creating integrated productivity applications with Visual Basic 4.0 and the members of the Professional Edition of Microsoft Office.
Another reason for choosing Visual Basic 4.0 for database application development is its OLE 2.1 client capability. Visual Basic 3.0 was the first database development environment to support OLE Automation. OLE Automation is likely to be one of Visual
Basic 4.0's most-used features for the majority of database developers. OLE Automation enables you to manipulate objects exposed by other OLE Automation server applications from within your Visual Basic 4.0 database application. Applications need not
include VBA to act as OLE Automation servers (source applications); Microsoft Word 6.0 and Word 95 support OLE Automation using the conventional Word Basic macro language command syntax. Chapter 14, "Integrating Database Front-Ends with OLE 2.1,"
describes how to use OLE Automation with a variety of commercial 16-bit and 32-bit Automation servers.
The new feature of Visual Basic 4.0 that's causing the most excitement among database developers is the capability to create your own OLE Automation servers. The capability to encapsulate custom Visual Basic 4.0 components as OLE Automation servers
makes code reusability practical, especially in team development environments. You can create high-performance in-process servers (called OLE DLLs) or conventional out-of-process servers (.exe files) that expose objects, methods, and properties to your
Visual Basic 4.0 applications, as well as to other OLE Automation clients such as Access, Excel, and Project. Chapter 16, "Creating Local OLE Automation Servers and DLLs," shows you how to create your own custom objects. Visual Basic 4.0 also
lets you communicate with networked OLE Automation servers, called Remote Automation Objects (RAOs) to implement three-tier client/server database architectures. Chapter 23, "Creating and Managing Remote Automation Objects," gives you the details
on writing 32-bit remote LOBjects (Line-of-Business objects) with the Enterprise Edition of Visual Basic 4.0.
The Windows database war was not over when this book was written, but Microsoft's three-pronged attack with Access, Visual FoxPro, and Visual Basic 4.0 has forced competing publishers of desktop database managers and client/server front-end development
tools into their defensive trenches. As a group, Microsoft's database applications for Windows, together with ancillary products such as ODBC (Open Database Connectivity), MAPI (Messaging API), and TAPI (Telephony API) have a breadth and depth that no
other software publisher presently can match.
All of Microsoft's 32-bit mainstream Windows productivity applications and programming languages presently support or soon will accommodate the 32-bit Microsoft Jet 3.0 database engine. Like VBA, the Jet database engine is a software component that
adheres to Microsoft's Component Object Model (COM) architecture. COM is Microsoft's infrastructure for creating code modules (called objects) that are independent of programming languages and computer platforms. COM defines a set of
interfaces that applications must support; OLE 2+ is a high-level implementation of COM designed for sharing objects between Windows applications and for application programming. OLE 2+ defines its own set of interfaces to permit use of one
program's objects by another program. As an example, applications that support VBA, such as Visual Basic 4.0, Access 95, Excel 95, and Project 95, communicate with the Jet 3.0 database engine (Msjt3032.dll) through the Microsoft Jet 3.0 Data Access Object
(DAO)—Dao3032.dll—an in-process OLE Automation server. (Access 95 also directly calls functions in Msjt3032.dll). Therefore, only a single copy of the Jet 3.0 DLLs is required in your \Windows\System folder; each application creates its own
instance of Jet as needed. A similar approach is used to share other components, such as DAO, spell-checking, graphing, and VBA 2.0; the component files are located in subfolders of your \Windows\Program Files\Common Files\Microsoft Shared folder if you're
running Windows 95.
The Jet database engine component included with Visual Basic 4.0 enables you to use dBASE III, IV, and 5; FoxPro 2+ and 3.0; Paradox, Btrieve 5.1x and 6.0; or Access 1.x, 2.0, and 95 databases with equal facility. You also can access
Excel 3+ and Lotus 1-2-3 worksheets, plus fixed-width or delimited text files, as if these files were desktop database files. Any of the preceding file types can be linked to a Jet database. (When this edition was written, Btrieve tables could be linked
only to Jet version 2.5 and earlier databases.) Only Microsoft Access rivals Visual Basic 4.0's universal database connectivity. Microsoft's 32-bit ODBC Administrator application and the 32-bit ODBC drivers created by Microsoft for Office 95 and by
third-party developers offer similar capabilities. (Intersolv, for instance, has a 32-bit CA-Clipper ODBC driver that handles Clipper's .NTX indexes.) With the exception of providing access to desktop databases not supported by Jet 3.0, the primary
application for ODBC in Visual Basic applications is connecting to client/server databases. Details of the two methods of adding desktop database functionality to Visual Basic 4.0 applications are given in Chapter 6, "Connecting to Other Desktop
Database Tables."
To use Btrieve databases with Visual Basic 4.0, you need a Windows dynamic link library that is included with the Btrieve for Windows application and other Btrieve products. Appendix A of this book, "Resources for Visual Basic Database Front-Ends," provides information on how to obtain the required Btrieve DLLs.
Visual Basic 4.0 includes an improved add-in application, Data Manager, which enables you to create new Jet databases, as well as to add, delete, and modify tables in new or existing Jet, dBASE, FoxPro, Paradox, and Btrieve databases. Figure 1.1 shows
Data Manager's Table Editor dialog for the Titles table of Biblio.mdb, the sample Jet 2.0 database supplied with Visual Basic 4.0. The Table Editor dialog displays the structure of the existing fields of a table and enables you to add new fields to a
table, or edit or remove existing fields. The Indexes dialog lets you add, delete, or modify table indexes. Data Manager is an example of a Visual Basic 4.0 database application that uses multiple document interface (MDI) forms. MDI enables you to create
database applications with several windows (called MDI child forms or windows) that are contained within a conventional window (called the parent window or form).
Figure 1.1. Visual Basic 4.0's Data Manager application.
For new Visual Basic 4.0 database applications, Jet (Access) .mdb is the preferred database format for versatility and performance. The Data Manager is a very helpful add-in, but it's not an effective substitute for Microsoft Access 2.0 or Access 95 if you intend to make extensive use Jet 2+ or Jet 3.0 .mdb files, respectively, for your Visual Basic 4.0 database applications. Although Data Manager aids you in creating new Jet databases, Access's table design mode and Relationships window greatly speed the design of multi-table .mdb databases.
Visual Basic 4.0's broad spectrum of database connectivity makes Visual Basic an ideal candidate for developing database front-end applications. The term database front-end is used to describe a computer application that can select items of data
contained in a database and display the chosen data items as information that is meaningful to a user. Some front-end applications only display data; others also let you update the database by editing, adding, or deleting records. The database system
itself is called the back-end. The back-end database is, at the minimum, a collection of related tables. Traditional desktop database managers store these related tables as individual files in a single directory, together with index files that are
used to speed the data-gathering process. Jet and client/server RDBMSs store all related tables and indexes, plus stored (precompiled) queries, in a single database file. Jet calls precompiled queries QueryDef objects, which correspond to stored procedures
(sps) in client/server RDBMSs.
Microsoft has achieved dramatic success in making the Windows graphical user interface (GUI) a world-wide standard for use on corporate PCs. At the time this edition was written, Microsoft claimed to have sold more than 10 million copies of Windows 95,
and sales of Windows 95 were reported to be running at over one million copies per month. Therefore, it is no surprise that virtually all of today's database front-ends are being created to run under Windows 95 and its high-end relative, Windows NT 3.51+.
With Visual Basic 4.0 and Access 95, Microsoft also has the upper hand in the capability to create 32-bit Windows database applications that employ a variety of database structures. Wide-ranging database connectivity is one of the major elements of
Microsoft Corporation's strategy to obtain a major share of the enterprise-wide computing market.
This book uses the term front-end generator to describe a Windows application with which you can quickly create a database front-end application for a wide variety of desktop and client/server RDBMSs. Theoretically, any programming language that
can create executable files for Windows can qualify as a front-end generator. You can write a Windows front end using C, C++, or Pascal compilers; many large-scale MIS applications are written in C or C++. Writing even a simple Windows database front-end
in C++, for example, requires a major programming effort that fails the "quickly" test. Therefore, this book restricts the classification of front-end generators to the following two types of products:
There were several hundred commercial Windows front-end generators available at the time this book was written, about evenly divided between the two preceding categories. Most of these products also include a report generator to print formatted data.
The retail version of Microsoft Access uniquely qualifies in both categories of front-end generators, because Access's user interface (UI) is simple enough that nonprogrammers can create their own database applications. Presently, Access is the most viable
competitor to Visual Basic in the front-end development tool market, although the 12M minimum RAM requirement for Access 95 may limit acceptance until 16M becomes the standard complement of RAM for Windows 95 workstations.
A critical requirement of any front-end generator is the capability to transfer data to other Windows applications easily. Copying database information to the Windows Clipboard and pasting the Clipboard data into a compatible application, such as
Excel, provides basic inter-application or inter-process communication (IPC) capability. Windows DDE (dynamic data exchange) is the most universal method of automatically transferring data to and from database front-ends, but DDE implementations, other
than pasted dynamic links, seldom meet the "easily" part of the requirement. Fewer front-end development tools offer OLE 2's capability to link or embed objects and to activate OLE objects in place. Only Visual Basic 4.0 currently offers a
combination of wide-ranging database connectivity and full 32-bit OLE 2.1 compatibility, including OLE Automation client capabilities.
If you aren't proficient in SQL, you need to learn yet another programming language to create database front-ends with Visual Basic. To select the data you want from a database attached to Visual Basic, write the necessary SQL statement, and then send
the statement as a string variable to the Access database engine or an ODBC driver. SQL (properly pronounced ess-que-ell, not the more common sequel or seekel) is the lingua franca of relational database systems. SQL has its roots in a
language called SEQUEL (Structured English Query Language) that IBM developed at its San Jose Research Laboratory in the mid 1970s. SEQUEL later became SEQUEL/2 and ultimately was renamed SQL. The first two relational databases to use SQL were Oracle,
developed by Relational Software, Inc. (now Oracle Corporation), and IBM's SQL/DS.
The purpose of SEQUEL and its successors was to provide a relatively simple, nonprocedural programming language to manipulate relational database systems. Visual Basic's VBA is a procedural language; you write a series of statements, such as If.
. .Then. . .Else. . .End If, to instruct the VBA interpreter to execute a series of instructions in a sequence you define. You control how the program executes to achieve the result you want. A nonprocedural language, on the other
hand, expects you to write a series of statements that describe what you want to happen (for example, SELECT * FROM TableName). The application that processes the statement determines how the statement is executed and simply returns the
result—in this case all the records contained in TableName.
One of the advantages of the use of SQL to manipulate relational databases is that the language has been standardized by a committee (X3.135) of the American National Standards Institute (ANSI). The standardization effort began in the mid-1980s; ANSI
X3.135-86 (SQL-86) specified the first official version of SQL. The 1986 standard was updated in 1989 (SQL-89) and again in 1992 (SQL-92). Developers of RDBMSs that use SQL are free to extend the language in any way they see fit; however, SQL reserved
words that are included in the ANSI standard must return the result specified by the standard. Extended SQL languages, such as Transact-SQL, which is used by the Microsoft and Sybase SQL Server RDBMSs, offer useful additions to SQL, especially for writing
stored procedures. Some implementations of SQL, such as IBM's version for DB2, do not comply with the latest ANSI standards; for instance, you cannot use the AS keyword to assign a derived column name to a DB2 column that contains a value expression (for
example, SUM(Expr)).
Users of xBase RDBMSs, such as dBASE and FoxPro, will find the structure of SQL statements to be quite similar to the interactive xBase statements that you enter at the dot prompt. In this book, xBase refers to any desktop relational database
management system that uses the dBASE file structure and supports, at the minimum, all the commands and functions of the dBASE III+ programming language. Here are the two xBase statements executed at the dot prompt:
USE customer LIST name, address, city, state, zip_code FOR zip_code >= 90000
And here's the single SQL statement contained in a Visual Basic string variable:
SELECT name, address, city, state, zip_code FROM customer WHERE zip_code >= 90000
Both return the same result—a list of the names, addresses, cities, states, and zip codes for all customers whose zip codes are equal to or greater than 90000.
Most of the recent implementations of desktop RDBMSs include SQL implementations that have varying degrees of conformance to the ANSI SQL-89 specification. Microsoft Access's dialect of SQL, Jet SQL, conforms quite closely to ANSI-89 syntax, and now
includes the Data Definition Language (DDL) elements of SQL that you need to create tables, indexes, and relationships with conventional SQL statements. Jet SQL, however, omits the Data Control Language (DCL) that enables you to GRANT or REVOKE privileges
for users to gain access to the database or the tables it contains. You establish permissions in Visual Basic 4.0 by manipulating members of the Jet 3.0 DAO's Users and Groups collections. Jet SQL compensates for the lack of SQL-based security features, at
least in part, by providing the TRANSFORM and PIVOT keywords that let you create the very useful crosstab queries—described forthwith. Chapter 5, "Learning Structured Query Language," describes the structure of SQL statements and how to
implement SQL in your Visual Basic code.
Access 2.0 or Access 95 is an excellent tool to aid in learning SQL. Both versions of Access include a graphical query design window to make creating and testing queries easy. Once you've designed and tested your query, use Access's SQL window to display the underlying Jet SQL statement to execute the query. You can copy the Jet SQL statement to the Clipboard and paste it as the RecordSource property value for a Visual Basic Data control. Alternatively, you can paste the Jet SQL statement into your VBA code for assignment to a string variable.
Database front-end applications that you create with front-end generators fall into two broad categories:
The following sections describe the basic characteristics of these two categories of database front-ends.
Decision-support applications represent the most common type of database front-end application. Single-purpose, decision-support front-ends typically display sales information for selected customers or products. At the other end of the decision-support
spectrum, complex management information systems (MIS) provide summarized information concerning virtually all of the quantifiable aspects of large organizations' operations from distributed database systems. Decision-support applications usually involve
read-only access to the data in the underlying database. Chapter 8, "Designing a Decision-Support Front-End," is devoted to writing Visual Basic code to display information gleaned from relational databases.
Most decision-support front-end development tools include the capability to create graphs and charts based on summary data. Grouping and totaling data to create summary information often is called rolling up the data. The Access database engine
lets Visual Basic decision-support applications perform crosstab rollups. Crosstab methods let you display summary data in worksheet format, usually as a time series. Using a crosstab query, you can display sales of products (in rows) by month or by
quarter (in columns) directly from tables that contain only raw invoicing data. Crosstab queries is one of the subjects of Chapter 7, "Running Crosstab and Action Queries." Drill-down methods enable you to show the detailed data that underlies
your summary information. (See Chapter 12, "Extending Your Use of Data Access Objects," for examples of drill-down methods.)
In-house and independent database-application developers use Visual Basic to create a wide variety of single-purpose and MIS decision-support front-ends. The principal advantages of Visual Basic over competing front-end development tools for creating
decision-support applications are as follows:
Many of the advantages in the preceding list apply equally to decision-support and transaction-processing front-ends. The list is by no means comprehensive. Many other advantages that derive from choosing Visual Basic as your database front-end
development tool will become apparent as you progress through this book.
Front-ends for transaction processing let users update the tables of databases. Transaction processing involves editing or deleting existing records in the database tables or adding new records to the tables. Therefore, users of transaction-processing
applications need read-write access to the tables that they want to modify. Transaction-processing applications require that either the database itself or your Visual Basic code preserves the integrity (related to accuracy) of the data. Enforcing domain
(data value) integrity and referential (record) integrity in databases that users can update is covered in Chapter 4, "Optimizing the Design of Relational Databases."
Transaction processing implies the capability of using the SQL reserved words COMMIT and ROLLBACK to execute or cancel pending changes to the tables, respectively. All modern client/server databases support COMMIT and ROLLBACK transaction processing,
but only a few desktop databases incorporate native transaction-processing capabilities. Access databases, for example, support transaction processing internally, whereas dBASE databases do not. Visual Basic 4.0 supports transaction processing with the
keywords BeginTrans, CommitTrans, and Rollback. Chapter 13, "Designing Online Transaction-Processing Systems," shows you how to use Visual Basic's transaction-processing keywords to speed updates to RDBMS tables, while preserving database
integrity.
ODBC drivers can provide transaction-processing capability for databases that do not ordinarily support COMMIT/ROLLBACK transaction processing. Intersolv's dBASE ODBC driver, for example, enables you to use COMMIT or ROLLBACK in your SQL statements that operate on dBASE tables.
In a multiuser environment, transaction-processing front ends must maintain database consistency and concurrency. A simplified description of the use of these two terms follows:
Both consistency and concurrency issues are controlled by the locking methods employed in multiuser environments. Visual Basic 3.0 supports the following locking methods:
When you use a client/server RDBMS, the server back-end usually handles the page-level locking process for you. The majority of client/server RDBMSs let you specify the level of locking and the page-level locking method to be employed through SQL
keywords, such as SQL Server's HOLDLOCK instruction. You need to use the SQL passthrough option when you want to use SQL reserved words that are not included in Jet SQL. SQL passthrough is discussed in the section, "Client/Server RDBMSs," later
in this chapter.
The Jet database engine is capable of creating and maintaining indexes for each of the database types that the engine supports. You need a primary key index in order to update data contained in Paradox and most client/server database tables. (Visual
Basic 4.0 does not use or maintain Paradox secondary or query speed-up indexes that are created on more than one column or that are designated as unique.) It is a good database-programming practice to create indexes on the primary key field(s) of all of
the tables in your database. (Visual Basic 4.0, however, does not recognize indexes on primary key fields of dBASE or Btrieve tables as PrimaryKey indexes.) Adding indexes on the foreign key fields of related tables speeds queries that involve more than
one table.
Visual Basic 4.0 can neither read nor maintain the .NTX index files created for .DBF files by CA-Clipper applications. Intersolv offers 16-bit and 32-bit ODBC drivers that can read and update CA-Clipper .NTX indexes. If you want to use Visual Basic 4.0 front-ends concurrently with CA-Clipper DOS applications, you need to use the Intersolv ODBC driver, or you must convert all the database indexes to dBASE III+ .NDX or dBASE IV .MDX file formats. The latter approach may require that you rewrite a substantial amount of Clipper code.
As you add more indexes to your tables, the speed of transaction processing operations decreases when you update the data values contained in the indexed fields. Therefore, the number of indexes you create for a table depends on whether the table is
used primarily for decision-support or transaction-processing applications. Choosing the right index structure is discussed in Chapter 4.
Multiple indexes drastically slow the importation of data from unsupported file types, such as delimited text files, to your existing tables.
When you import data, you may find it much faster to create a new table to hold the imported data, then index the new table and append the data from the new table to the existing table.
You can write Visual Basic front-ends for a variety of types of database management systems. In fact, if you use the ODBC drivers and write SQL statements that use reserved words included in the ODBC Core-level SQL grammar, it's likely that you can
create a single application that will perform satisfactorily with virtually any of the more commonly used relational database management systems. The ODBC Core-level SQL grammar is a subset of ANSI SQL-89 and is specified in Microsoft's Programmer's
Reference for the Microsoft Open Database Connectivity Software Development Kit (ODBC SDK). The Microsoft ODBC drivers included with Microsoft Office 95 supports Core-level SQL grammar, as do the SQL Server driver supplied with Visual
Basic 4.0 and the ODBC drivers supplied by Intersolv.
The Microsoft 32-bit ODBC driver for Oracle 6 and Oracle 7 RDBMSs, which is required for use with 32-bit Jet 3.0, was not available when this edition was written. The 32-bit ODBC driver for Microsoft SQL Server also can be used with Sybase SQL Server and Sybase System 10, but the driver is not supported by Microsoft for use with Sybase RDBMSs. When used with Sybase products, some features of Sybase System 10 are not available when using the Microsoft driver.
The sections that follow describe the four basic categories of database management systems that you can use with your Visual Basic 4.0 database applications.
Traditional desktop RDBMSs, typified by dBASE and Paradox, use separate files for each table and index, or collection of indexes, for a single table. FoxPro, dBASE, and Paradox tables use fixed-width (also called fixed-length) records. You
specify the maximum size of each field of the Character data type; data values shorter than the maximum size automatically are padded with blanks (spaces) to the maximum size of the field. Btrieve tables provide for variable-length character fields;
variable-length character fields can save a substantial amount of disk space if the length of data values in character fields varies greatly.
The Visual Basic documentation defines a database comprising traditional desktop RDBMS table and index files as an external database. This book does not use the term external database, because there is no complementary internal database defined
in Visual Basic 4.0. The dBASE, FoxPro, Paradox, or Btrieve database is specified as the well-formed path to the folder that contains the table and index files that you need for your application. A well-formed path, also called a fully-qualified
path, consists of the drive designator and the path to the folder that contains the table and index files, such as C:\Vbdbs\Dbase. If your tables are stored on a file server that uses the Uniform Naming Convention (UNC), you substitute the
server name for the drive identifier, as in \\SERVER\VBDBS\DBASE. Applications must support both long file names and UNC to qualify to carry Microsoft's "Designed for Windows 95" logo.
For xBase files, you specify the indexes to be used with each of the tables in individual .inf files located in the same folder as the table. The filename of the .inf file is the same as the table file. Thus, the information file for Customer.dbf is
named Customer.inf. If you use dBASE IV multiple index files, only one entry is required, NDX1=Customer.mdx. For dBASE III+ indexes, the index files are identified by sequentially numbered entries, such as NDX1=Custname.ndx, NDX2=Custzip.ndx, . . ., with
each entry on a separate line. You need .inf files for dBASE III+, dBASE IV, and FoxPro files, but not for Paradox or Btrieve files. When you create a dBASE or FoxPro table and specify an index, Jet 3.0 automatically creates the .inf files for you. To use
existing .mdx or .ndx index files with your .dbf file, you must use Windows Notepad or another text editor to create the .inf file.
Btrieve's data definition file, Files.ddf, serves the same purpose as the .inf file. Jet cannot create the Files.ddf file for Btrieve databases. You need Xtrieve or a third-party Btrieve utility program to create the necessary Btrieve data definition
file. Other requirements for the creation of Btrieve files are discussed in Chapter 6.
The Jet database engine does not have the capability to remove deleted records from dBASE and FoxPro table files. You need an application that supports the xBase PACK statement to eliminate deleted records and recover the fixed disk space the deleted records consume.
FoxPro and dBASE memo files that are associated with database tables must be stored in the same folder as the table that contains a Memo field data type. If the associated memo file is missing or corrupted, you receive an error message from Jet when
you attempt to open the table file.
It is a good database-programming practice to place all the table, memo, and index files you need for your application in a single database folder. Some xBase applications, such as accounting products, require that groups of files be stored in different folders. Visual Basic 4.0 enables you to open more than one database at a time; thus, you can deal with table, memo, and index files that are located in more than one folder. This recommendation does not apply to Jet .mdb databases, because all of the tables for the database application are likely to be contained in a single .mdb file.
The manipulation of data in the table files and the maintenance of the indexes of traditional desktop databases are the responsibility of the database application. The application translates high-level statements, such as SQL's SELECT or dBASE's LIST
expressions, into low-level instructions that deal directly with records in the table files. If you run queries from a workstation against large table files that are located on a network file server, a very large number of low-level instructions are sent
across the network to the file server. When a large number of users attempt to run queries simultaneously, to the same or other tables on the server, performance can suffer dramatically because of network congestion.
There is no equivalent in Jet 3.0 of the record number associated with traditional RDBMS tables. Microsoft makes the valid point that record numbers are meaningless in SQL databases. (However, Microsoft Access assigns record numbers to tables and query results that Access displays in datasheet mode.) Visual Basic 4.0 uses the Bookmark and AbsolutePositon properties of a table or query result as substitutes for the RECNO() function of xBase. A Bookmark value uniquely identifies a record, but a Bookmark returns a string, not a numeric, value. The new AbsolutePosition property of Jet 3.0 is similar to RECNO(), but AbsolutePosition is zero-based and points to the position in the result set, not to RECNO()'s position, which is based on the sequence of original table data entries.
The term front-end originally appeared in conjunction with client/server RDBMS applications. Front-end refers to the client application that runs on a workstation connected to the server (back-end) on a local area network (LAN) or wide area
network (WAN). The rapid growth of the client/server database market in the 1990s is due to the desire of users of mainframe and minicomputer database management systems to downsize their information systems. Downsizing means substituting relatively
low-cost file and application servers, most often based on Intel-architecture PCs, for expensive mainframe and minicomputer hardware and database software products that are costly to maintain. Today's trend is toward distributed client/server
systems. In distributed database systems, tables that contain the data to satisfy a query might be located within databases spread across several different servers in widely varying locations that are connected by a WAN.
The operating system for the server portion of the client/server RDBMS need not be (and often is not) the same as the operating system used by the client workstations. For example, Microsoft SQL Server 6.0 runs under Windows NT 3.5+, and Sybase SQL
Server or System 10+ runs under UNIX on minicomputers or as a NetWare Loadable Module (NLM) on Novell PC file servers. Although the majority of both Microsoft and Sybase SQL server clients now run under DOS/Windows 3.1+, it's likely that by the end of
1996, most client applications will be running under 32-bit Windows 96 or Windows NT 3.51+. This projection explains, at least in part, the emphasis on 32-bit Visual Basic 4.0 front-ends in this book.
Client/server RDBMSs differ greatly from desktop database management systems. The primary distinction is that all SQL statements issued by the front-end application are executed by the server. When a workstation sends a conventional SELECT query to the
server, only the rows that meet the query's specifications are returned to the workstation. The server is responsible for executing all SQL statements sent to the server by workstations. The server also handles all concurrency and consistency issues, such
as locking. If a query issued by a workstation cannot be completed by the server, the server returns an error message to the workstation. Combining high-level and low-level instruction processing at the server solves most network congestion issues.
The majority of client/server RDBMSs store all databases in a single, very large file. Where necessary, the file can be divided between server computers, but the divided file is treated as a single file by the server's operating system. Client/server
RDBMSs include other sophisticated features, such as the maintenance of transaction logs that enable databases to be re-created in the event of corruption by a major hardware or software failure. Most client/server products now can use fixed disk arrays
and mirrored fixed disks that reduce the likelihood that a failure of part or all of single fixed disk drive will bring client services to a halt.
The easiest method of connecting your Visual Basic 4.0 database application to a client/server database is by using the appropriate 16-bit or 32-bit ODBC driver. (Using the Jet 3.0 DAO with either Visual Basic 4.0 or Access 95 requires 32-bit ODBC
drivers.) This book refers to a client/server database connected through an ODBC driver as a data source. To open a connection to a data source, you need to have previously defined the data source with the ODBC Administrator application that
is supplied with Visual Basic 4.0 or another Microsoft application, such as Microsoft Query, that uses ODBC. (Microsoft Query is included with the Office 95 software suite.) You need the data source name (DSN), a valid user login identifier (UID), and a
valid password (PWD) to open a client/server data source as a Visual Basic 4.0 Database object or to attach tables from the data source to an open Jet database.
Although you can use the Jet database engine to process queries against client/server databases that you open as a Jet Database object or that you attach to a Jet database, using the SQL passthrough option takes better advantage of the client/server
environment. When you specify the use of SQL passthrough, the server processes the query and returns a non-updatable Recordset object of the Snapshot type that contains the rows, if any, returned by the query. You also can use SQL passthrough to execute
action queries that append, update, or delete records, but do not return a query result set. SQL passthrough enables you to execute stored procedures, if your client/server database supports stored procedures. (The Microsoft and Sybase versions of SQL
Server, Sybase System 10, and Oracle 6+ are examples of RDBMSs that support stored procedures.) A stored procedure is a compiled version of a standard SQL query that your application uses repeatedly. Stored procedures usually execute much faster than
conventional SQL queries, especially when the query is complex.
The Enterprise Edition of Visual Basic 4.0 includes the Remote Data Object (RDO) and the Remote Data Control (RDC). The RDO is designed specifically for executing stored procedures on client/server RDBMSs.
Client/server RDBMSs vary widely in purchase price. As a rule, the price of the server software depends upon the number of simultaneous workstation connections that the server supports, as well as the operating system under which the RDBMS runs. As
with runtime versions of traditional RDBMSs, you purchase copies of the workstation software that are necessary to connect to the server. Microsoft SQL Server 6.0, a component of Microsoft BackOffice, is currently the lowest cost enterprise-level
client/server RDBMS available from a major software publisher. You run Microsoft SQL Server 4.21a or 6.0 as a service under Windows NT 3.51+. (A process starts automatically when you boot Windows NT; it's not necessary for the administrator to log
on and start a process manually.) SQL Server 4.21a and 6.0 offer the option of per-server or per-seat licensing, but client licenses for the complete BackOffice suite are per-seat only. Chapter 20, "Creating Front-Ends for Client/Server
Databases," describes how to use Microsoft SQL Server 4.21a and 6.0 with Visual Basic 4.0 front-ends.
Microsoft Access deserves its own category, because Access databases bear little resemblance to traditional desktop database structures. The Microsoft documentation for Visual Basic 3.0 referred to both Access databases and Visual
Basic databases. It is likely that Microsoft intended these two terms to mean "databases created with Access 1.x" (which required a SYSTEM.MDA file) and "databases created with Visual Basic 3.0" (which did not require a
SYSTEM.MDA file), respectively. The documentation for Visual Basic 4.0 now uses Jet to refer to Access databases. For consistency with Visual Basic 4.0 documentation, this book uses the term Jet database no matter what application is used to
create the .mdb file.
Access 95 replaces Access 1.x and 2.0 SYSTEM.MDA files with System.mdw, called a workgroup file, which fulfills similar security functions. The .mda file extension is now reserved for Access library files. Visual Basic 4.0 does not require a System.mdw file or SYSTEM.MDA, but a workgroup file is needed if you want to take advantage of the Groups and Users collections to manipulate permissions for secure multiuser .mdb files.
As mentioned at the beginning of this chapter, Jet is the default database type for Visual Basic 4.0. Microsoft's choice for the default database type is understandable, because Jet .mdb files have a structure that is proprietary to Microsoft
Corporation. Therefore, you need to purchase Microsoft products to use Jet database files; all the Microsoft applications that can handle Jet database files are Windows applications. It is highly unlikely that Microsoft will publish the intimate details of
the Jet .mdb file structure as an "open standard," at least in the foreseeable future. Despite the proprietary nature of Jet database files, you are likely to find that Jet is the database type to select when the choice is yours to make.
Jet database files include many of the features of the client/server databases described in the preceding section. Much of the architecture of Jet .mdb files is based on the structure of Microsoft SQL Server database files. Here are some of the
similarities between Jet and client/server databases:
Access 2.0 and Access 95 offer a variety of events applicable to data-bound forms and controls that are not available in Visual Basic 4.0. As an example, the Access BeforeUpdate and AfterUpdate events of data-bound controls, and the OnCurrent, BeforeInsert, and AfterInsert events of Access forms (equivalent to the Data control on a Visual Basic form) are not available as Visual Basic events. Access 95 provides improved granularity of events involving updating tables in comparison with Visual Basic 4.0.
Other advantages of using Jet databases include the capability to attach tables of other supported database types. The Microsoft documentation for Visual Basic 4.0 contains ambiguous references to external tables and attached or linked
tables. As mentioned earlier in the chapter, this book does not use the term external tables. The official name, as of Access 95, is linked tables. You can gain a significant speed advantage if you link tables from client/server databases
to a Jet database, rather than opening the client/server data source as a Visual Basic Database object. However, you cannot open an attached client/server table as a Recordset object of the Table type; only Dynaset and Snapshot types are supported. The Jet
2.5 and 3.0 DAO create a local pseudo-index on the client/server table if the table is not indexed. These methods of creating Visual Basic 4.0 Recordset objects are covered in detail in Chapter 2, "Understanding the Jet 3.0 Data Access Object."
You usually gain an even greater speed advantage when you use the SQL passthrough option to cause your SQL query statements to be executed by the database server rather than by the Jet database engine.
If you have the appropriate software and hardware (called a gateway or MiddleWare), you can connect to many popular mainframe and minicomputer RDBMSs, such as IBM's DB2 or Digital Equipment Corporation's Rdb. (MiddleWare recently became a
registered trademark of TechGnosis, Inc., a supplier of gateway products.) Suppliers of gateways to DB2 databases that are compatible with ODBC include Micro Decisionware (now a part of Sybase), Information Builders, Inc., TechGnosis, Inc., and IBM
Corporation. More information on these gateways is included in Appendix A, "Resources for Visual Basic Database Front-Ends." In addition to the gateway, you need the appropriate ODBC driver for the mainframe or minicomputer database to which you
want to connect. One of the primary commercial uses of Visual Basic 3.0 is the creation of front-ends for IBM DB2 mainframe databases.
IBM offers DB2/2 for use under OS/2 in both a single-user and a multiuser version. IBM also has released another DB2 variant for use under Windows NT 3.5+. DB2/2 is the replacement for the OS/2 Database Manager (DBM) for OS/2 v. 1.3. Having a local desktop or networked version of DB2 can save many hours of negotiation with your DB2 database administrator when you need to restructure or reload your test database.
You can even use SQL statements to query nonrelational databases, such as CODASYL network databases or hierarchical databases typified by IBM's IMS. Products such as Information Builder's EDA/Link for Windows and the IBI EDA/SQL database engine make
network and hierarchical databases behave like client/server applications.
The introduction to this book states that Database Developer's Guide with Visual Basic 4 is intended for readers who are familiar with Visual Basic programming techniques. Some readers have a tendency to skip over introductions and
proceed to the first or one of the later chapters in a book of this type. The sections that follow are designed for database developers who have not yet mastered Visual Basic programming or for whom creating a database application will be their first
experience with Visual Basic programming. Developers evaluating Visual Basic 4.0 as an alternative to Microsoft Access for Windows 95 also might benefit from the short description of Visual Basic programming that follows. Visual Basic pros undoubtedly will
want to skip these following two sections.
Creating database applications for the character-based environment of DOS traditionally has involved top-down programming techniques. Using xBase as an example, you start "at the top" with a main program, such as APPNAME.PRG, in which you
declare your PUBLIC (Global) variables and constants, and then add the code you need to create the DO WHILE .T.. . .ENDDO main menu loop for your application. Next you add the procedures that include more menu loops for the next level of submenus. Then you
write the procedures that contain the @. . .SAY and @. . .GET statements to create the screens that constitute the core of your DOS application. Finally, you add the accouterments, such as data validation procedures and report printing operations. As an
experienced database developer, you write modular source code. You've written your own libraries of standard procedures and user-defined functions that you reuse in a variety of applications. You also might employ add-in libraries created by other
developers. If you use CA-Clipper, you spend a substantial amount of time recompiling and linking your application during the development cycle.
To use Visual Basic, you'll need to abandon most of the programming techniques to which you've grown accustomed and adopt Windows object-oriented, event-driven, method-centric programming style. The first major difference you'll discover when you
switch to Visual Basic as your database development platform is that you do not create a "main" program. The "main" program is Microsoft Windows. There is a hidden WinMain program in every Visual Basic .exe file, but Windows itself has
the last say on how your application executes. Your application can't execute any code until an event occurs, because all Visual Basic procedures begin as event-handlers. Event-handlers are methods that your application executes in response to
events. You can't generate or respond to an event without creating a form for the following reasons:
That's why a default Form1 appears each time you launch Visual Basic. Opening a Visual Basic form generates an event, Load, that calls an event-handling routine Form_Load(). Visual Basic automatically assigns names, consisting of the form name, an
underscore, and the event name, to event-handling code. If you name the opening form of your application StartUp, Visual Basic creates a stub, Sub StartUp_Load(). . .End Sub, which you populate with the code you need to initialize your
application. (Sub declares a subprocedure of your application's WinMain program.) You specify the name of the opening form of your application in the Startup Form drop-down list in the Project page of the Options dialog that appears when you choose
Options from Visual Basic's Tools menu. Forms, together with the control objects on the form and the event-handling code for the form and its control objects, are stored in files with the extension .frm.
It is a good practice to declare the Public variables and constants that your application needs in a Visual Basic module. Module files use the extension .bas. You create a new Visual Basic module by choosing Insert | Module.
(Visual Basic 4.0 introduces a new Class module type—.cls—for creating your own object classes and resource files, .res, which aid in internationalizing your applications.) A project file, with the extension .vbp (formerly .MAK), maintains a list
of all of the form, module, resource and OLE Control files that constitute your application. (The 16-bit version of Visual Basic 4.0 also supports .VBX custom controls.) The project file serves the same purpose as the .MAK file that is used to control the
compiling and linking of Clipper or C applications. When you "compile" a Visual Basic application, you create a Windows .exe file that contains your forms and code. Windows dynamic link libraries, Vb40016.dll and Vb40032.dll (16-bit and 32-bit
replacements for Visual Basic 3.0's VBRUN300.DLL), contain the runtime version of the Visual Basic 4.0 interpreter that executes your code. The distribution version of your database application includes the application's AppName.exe file, an
optional initialization (AppName.ini) file, Vb400??.dll, OLE Control (.ocx) and/or custom control (.VBX) files for the custom controls you employed, and the components of the Jet database engine and Data Access Object (also DLLs) for the
type(s) of database(s) your application opens.
The structure of Visual Basic applications differs considerably from Access applications that include Access VBA code. Visual Basic has no direct counterpart to Access's macro actions, which you execute with DoCmd statements (methods of the DoCmd object in Access 95). However, the syntax of Access VBA and the VBA dialect of Visual Basic 4.0 is almost identical. Therefore, it is practical to import Access VBA code into Visual Basic applications, and vice-versa. Chapter 17, "Translating Access VBA Code to Visual Basic 4.0," details the differences between Access Basic 1.x and 2.0, Access VBA, and Visual Basic 3.0.
Visual Basic 4.0 makes extensive use of object-oriented programming terminology to describe the components of applications. Visual Basic 4.0 classifies forms, controls on forms, databases, and tables as objects. An object possesses both
characteristics and behavior; the characteristics of an object are the object's properties (data), and the behavior of the object is determined by its methods (incorporated in event-handling code). An object is a container for
data and code. Objects can contain other objects; forms, as an example, contain control objects. Each Visual Basic object has its own predetermined set of properties to which Visual Basic assigns default values. You execute the methods that are applicable
to a programming object in event-handlers. The set of methods that are applicable to Form objects, for example, differ greatly from the set of methods that are used with Recordset objects.
Visual Basic enables you to create object variables that refer to objects with the Dim objVariable As ObjectType and Set objVariable = ObjectName statements. After executing these two statements,
objVariable is a reference (pointer) to the original object. You can assign as many different variables to the same object as you want. If you add the reserved word, New, to the Set statement, as in Set objVariable
= New ObjectName, you can create a new instance of the original object. An instance of an object is a copy of the object that you can manipulate independently of the object you copied. Object variables are an essential element of
database application programming with Visual Basic 4.0.
Variables, declared with the xBase reserved words PUBLIC and PRIVATE, default to the Logical data type and are assigned a new data type when they are initialized with a value other than .T. or .F.. Because standard xBase only has the four fundamental
field data types used in dBASE III+ .DBF files (Character, Numeric, Date, and Logical), it is a simple matter for an xBase interpreter to determine the data type from the assigned value and to treat the variable according to its content. xBase is said to
have weak data typing. In contrast, compiled languages, such as Pascal and C, have strong data typing—you must expressly declare the data type when you name the variable.
Early versions of the BASIC language took a middle road to data typing: All variables were of the floating-point numeric type unless you explicitly declared the variable to be of the String data type by appending a dollar sign ($) to the
variable name. As the sophistication of BASIC dialects increased, BASIC interpreters and compilers began to add new data types, such as Integer, Long (integer), Single (precision), Double (precision), and Currency, using
the symbols %, &, !, #, and @, respectively. At this point, the authors of various flavors of BASIC ran out of usable type declaration symbols. The Option Explicit and Dim anyVariable As DataType statements
give you the option of using strong data typing with BASIC.
There are three problems with strong data typing when dealing with objects and databases:
Visual Basic 2.0 introduced the Variant data type, which solves all the preceding problems. An added benefit of the Variant data type is that you can create multidimensional arrays that contain different data types in each dimension.
Variables that you declare without explicitly defining their data type default to the Variant data type in Visual Basic 2.0 through 4.0. All data contained in Visual Basic Recordset objects is of the Variant data type.
The basic specification for the Variant data type is included in the Microsoft Programmer's Reference: Creating Programmable Applications for OLE 2.0. The Variant data type is used exclusively for passing data between
applications that employ OLE Automation methods. Visual Basic 4.0 defines 15 interpretations of the Variant data type, seven of which correspond to the now-traditional BASIC data types described previously. This book substitutes the term
"Variant data subtype" for "interpretation." Some of the new data subtypes are Empty (uninitialized—the variable has been declared, but has not been assigned a value), Null (no data), Date (date and time in Microsoft
fixed-point format), Object (OLE Automation object), Error, and Array. Notice that of these new data subtypes, only Null is a Visual Basic reserved word. The specification for the Variant data type includes an additional subtype, Boolean,
which is now supported directly by Visual Basic 4.0. You use the intrinsic Integer constants, True (-1, &HFFFF) and False (0, &H0000), to set Boolean values.
Database objects were introduced to Visual Basic in version 2.0, but these database objects were applicable only to databases connected through the ODBC driver for Microsoft SQL Server, the sole ODBC driver that Microsoft supplied with Visual Basic
2.0. You needed to use a third-party .VBX custom control or an add-in library to connect to other types of databases. Visual Basic 3.0's Access database engine (AKA Jet 1.1), combined with newly added database functions and methods incorporated in the Jet
1.1 Data Access Object, enabled you to create database objects using tables native to any of the more common desktop and client/server RDBMSs. In addition, Visual Basic 3.0 enabled you to define and create new databases for the majority of the supported
database types.
The Jet 2.0 database engine used by Access 2.0 and available to Visual Basic 3.0 through the Microsoft Access 2.0/Visual Basic 3.0 Compatibility Layer introduced a number of new objects of the Data Access class. Jet 3.0 adds the new Error object to Jet
2.0/2.5. Following are the primary high-level objects that are contained in the Data Access Object hierarchy of Jet 3.0, all of which derive from the fundamental DBEngine object class:
Jet 1.x's Table, Dynaset, and Snapshot objects are supported by Visual Basic 4.0's Jet 2.5/3.0 Compatibility layer for backward compatibility with earlier Visual Basic code. All applications you create with Visual Basic 4.0 should use the
Recordset object; there is no guarantee that Table, Dynaset, and Snapshot objects will be supported in future versions of the Jet database engine or the Jet Data Access Object.
Visual Basic 4.0's data control uses the Recordset property to define a Recordset object that is specific to the data control. Microsoft calls Recordset a property of a data control, but this book considers Recordset to be an object in all cases, because Recordset objects have properties; only objects can have properties.
A full description of the syntax of the functions and methods for creating members of the DBEngine object class is provided in the next chapter.
A collection is a set of references to related objects, similar to but not identical to an array. The specification for creating and naming collections is included in the Microsoft OLE 2.0 publication, Creating Programmable
Applications. The references (pointers) to objects in a collection are called members of the collection. Each member of a collection has an index value, and persistent objects have unique names. Unlike arrays, however, the index number of a
member may change, and index numbers need not be contiguous. It is possible for a collection to contain no members at all. Most collections have a property, Count, that returns the number of members of the collection. The index to a collection need not be
(but usually is) an integer; some objects use string indexes. The safest approach is to always specify the unique name of the member of a collection you want to use, if the object has a Name property.
The name of a collection is the English plural of the class of the object in the collection. In Visual Basic 4.0, collections include Forms (all forms that have been loaded by the application), Controls (each control on a loaded form), the data access
object collections in the following list, and collections of objects exposed by OLE 2+ applications that support OLE Automation. Following are examples of the collections that are most often used in VBA code:
Names of collections and members of collections are not reserved words in Visual Basic. Therefore, names of collections and members of collections are not set in bold type in the previous list. As an example, you can declare and use a variable or constant with the name, TableDefs or TableDef, and not incur an error message. It is a good programming practice not to use names of objects or collections for VBA variables or constants.
Using collections of data-related objects lets you add new members or delete existing members of the collection with the Append and Delete methods. Data access collections are the only Visual Basic collections that are not read-only. Therefore, you can
modify the structure of an existing database or add new tables to a database you create by applying the Append and Delete methods to a TableDef object. When you delete a table that is linked (attached) to a Jet database, you don't actually delete the
table; only the link to the attached table is deleted. You can add Field objects to tables, but you can't delete a Field object from a collection. You need to re-create the entire table if you want to remove a field. You can add or delete indexes on one or
more fields of any TableDef object in the TableDefs collection.
Visual Basic 4.0 provides a Data control that enables you to add bound control objects to a Visual Basic form. Bound control objects display data and update data in the current record of a specified Recordset object. Visual Basic 4.0 includes a new
Data Form Designer add-in, which almost instantaneously creates a simple data display and editing form for a table in any of the database formats supported by Jet 3.0. The Data Form Designer is related to the Form Wizard of Access. Figure 1.2 shows the
opening dialog of the Data Form Designer in which you select the database to open (ConnectString list), the RecordSource property (a TableDef or QueryDef object, or an SQL Statement), and the columns (fields) you want to appear on the form. When you click
the Build the Form button, the Data Form Designer quickly creates a standard form, like the one shown in Figure 1.3, that contains a Data control, text boxes for each column you add, and a set of standard buttons for manipulating the record pointer of the
Data control's Recordset object.
Visual Basic 4.0's Data Form Designer add-in serves two purposes: to demonstrate the capabilities of the Data control and to provide an example of Visual Basic 4.0's OLE Automation add-in features. Add-ins extend the power of Access wizards to Visual Basic 4.0. Creating add-in extensions to the Visual Basic development environment is beyond the scope of this book, but you can expect to see many third-party extensions designed for creating Visual Basic 4.0 database applications.
The advantage of using the Data control is that you can create a form to browse the records in a Recordset object without writing any Visual Basic code at all. The drawback of the Data control is it's VCR-style buttons, similar to the record selector
buttons of Access 1.x's datasheet view. Many database developers prefer to use command buttons with Alt+key combinations for record manipulation, such as First, Next, Last, and Previous (Record), in addition to the
five buttons shown in Figure 1.3. The majority of the sample applications in this book use Visual Basic code, rather than the Data control, to navigate and edit data in Recordset objects.
The Data control and data-bound controls, such as data-bound text boxes, are called, collectively, data-aware controls, data access controls or, simply data controls. Data controls have the following two properties that are not
available with the conventional (unbound) versions of Visual Basic control objects:
The Professional and Enterprise Editions of Visual Basic 4.0 include a variety of data controls that were not available in Visual Basic 3.0, but were offered as .VBXs by third-party custom control vendors. Chapter 3, "Using Visual Basic's Data
Controls," provides examples of simple decision-support and transaction-processing applications that you can create with the data control and Visual Basic 4.0's bound control objects.
This chapter has given you an overview of Visual Basic 4.0's capabilities as a database development platform and how Microsoft plans to use Visual Basic 4.0, OLE Automation, and Visual Basic for Applications to cement the firm's leadership position in
the Windows desktop database market. No matter your opinions relating to Microsoft's predominance in the Windows applications market and the methods Microsoft has used to achieve their present market share, the Microsoft desktop database juggernaut is a
fact. The initial success of Windows 95 on the desktop and the inroads being made by Windows NT 3.51+ in the enterprise computing market indicates that 32-bit database front-end development will predominate in 1996 and beyond. Visual Basic is more than
likely to continue as the most popular Windows desktop and client/server database application development tool.
The remaining two chapters in the first section of this book give you the basic details you need to use Visual Basic 4.0's data access object, the Data control, and bound control objects to create simple Visual Basic database applications that display
and edit data contained in Jet databases. Even accomplished Access developers should scan the next two chapters, because Visual Basic 4.0's Data Access Object differs significantly from Access's CurrentDB() approach, and Visual Basic's direct access to the
current Database object and its underlying Recordset objects requires that you adopt a new understanding of how your code interacts with data control objects.
