William R. Vaughn
Microsoft Corporation
February 1996
Note Portions of this paper are excerpted from William Vaughn’s new book, Hitchhiker's Guide to Visual Basic and SQL Server, 4th Edition (Microsoft Press, 1996).
We have just experienced a revolution. Unless you were on an extended visit to your rich cousin in the Fiji islands, you too have witnessed this change. Not only have the Republicans taken over Congress, but Microsoft® Visual Basic® has taken over the development shops. It is clear that more systems designers, developers, and development managers have accepted Visual Basic as the language of choice for applications ranging from games to complex data capture and retrieval systems. As a result, Visual Basic is outselling its closest rival over four-to-one.
Since over 80 percent of Microsoft Windows®-based applications access databases, more Visual Basic applications are serving as front ends to database management systems of all sizes and complexity. While many applications are not concerned with the complexity of data sharing, the trend is toward more sophisticated multi-user/multitasking systems that place significant demands on the application, workstation, network, server, and developer. MIS shops have discovered Visual Basic as a viable platform to develop these applications, while leveraging their development talent and producing customized solutions more quickly. Visual Basic's ease of development and execution speed have made it far more cost-effective than other compile-link-test languages.
As the role of Visual Basic applications has expanded and matured, the mechanisms to connect Visual Basic to databases has also multiplied and matured. Developers are applying significant pressure on Microsoft, the third-party control vendors and their own development teams to improve Visual Basic's performance—especially when accessing data. A number of companies are using the performance of Visual Basic version 3.0 (or lack thereof) as a springboard to launch competitive database interfaces or are simply performance-tuning applications that promise to wring more thrust out of Visual Basic or the Microsoft Jet database engine.
Microsoft Jet has proven to be one of the most important new features of Visual Basic version 3.0. However, because of rumors about Jet's limited performance against remote data sources, and lack of support for key Microsoft SQL Server features, VBSQL has become a very popular interface for developers that need high-speed access to SQL Server—especially for larger companies that need to connect dozens to hundreds of users. Granted, some Microsoft Jet performance problems are self-inflicted. Many developers attracted to Visual Basic and Microsoft Jet come from the Flat-World Society, where dBASE and flat-file ISAM databases put bread on the table. By incorrectly porting age-old techniques and designs over to relational schemas, many of these systems were doomed to pitifully poor performance from the start.
Microsoft has been anxious to corral these wild herds of Xbase flat-file developers. At least six Microsoft development platforms are being promoted by Microsoft: C++® (and Quick C++), Microsoft Excel, Microsoft SQL Server, FoxPro®, Access, and Visual Basic. Developers, architects, and managers are all confused—which platform will yield the best performance at the least cost? To add to the confusion, there are at least a half-dozen ways to access SQL Server data just from Visual Basic—and these are just the Microsoft-provided means:
Figure 1. Basic components used to access SQL Server from Visual Basic
Since there are so many choices, choosing the right interface is not a simple undertaking. For the flat-file developers, Microsoft Jet seems to hold the greatest promise. However, for accessing ODBC-enabled databases, RDO seems to be a better choice for many applications.
Over the last three years (1993 to 1995), Microsoft has completely rewritten Visual Basic. Visual Basic version 4.0 is no longer the mostly-assembly language monolithic application that Visual Basic version 3.0 was. Visual Basic version 4.0 is, in most respects, an entirely new product. It is now heavily dependent on Object Linking and Embedding (OLE), Visual Basic for Applications (VBA), and other loadable "components" including Microsoft Jet. As a result of this redesign, Visual Basic version 4.0 now has the ability to create DLLs, create applications that act as OLE servers and clients, and support development add-ins like SourceSafe.
Visual Basic is provided in both 16-bit and 32-bit versions for use on 16-bit Windows and 32-bit Windows 95 and Windows NT systems. Upon closer inspection, these two versions are very different in many respects. For example, the 32-bit version of Visual Basic version 4.0 is designed to work with 16-bit Unicode strings. This single aspect can significantly impact conversion of 16-bit Visual Basic version 3.0 applications that pass strings to DLLs (like existing VBSQL applications). In addition, since the Windows operating system itself has drastically changed, calls made from Visual Basic applications to Windows APIs must be re-coded, and in some cases, completely redesigned. Because of these differences, application conversion to the Windows 95 or Windows NT platforms can be a challenge—especially for larger applications. Conversion can be made easier by studying the documentation and the Knowledge Base articles on conversion included with Visual Basic version 4.0. In most cases, existing Visual Basic version 3.0 applications should port to Visual Basic version 4.0 (16-bit) with no serious roadblocks—assuming you did not use any nasty tricks based on inside knowledge of the architecture. We wade into the compatibility swamp a little later in the book.
Although VBX custom controls are supported in the 16-bit versions of Visual Basic version 4.0, you must use OCX equivalents of all custom controls for 32-bit programs. Guess what? Not all of the control vendors are ready with 32-bit versions. The slowest to convert will probably be the vendors that have gone out of business. Can you say "re-write"? Granted that most of the applications written for Visual Basic version 3.0 can be imported into Visual Basic version 4.0 and recompiled, some of them will require significant re-coding, re-thinking, and adjustments before they will work as before.
Although Visual Basic itself has changed radically, the methods and techniques used to develop VBSQL applications are generally unchanged. Because Visual Basic version 4.0 still does not have built-in call-back support, a custom control to implement the error and message callback routines is still needed for both 16-bit and 32-bit operating systems. A new VBSQL.OCX has been developed to support 32-bit operating systems by the Microsoft SQL Server group. It is designed to be a virtual clone of the 16-bit version.
Visual Basic version 4.0 also utilizes the latest Microsoft Jet Database Engine(s) (versions 2.5 and 3.0), which were first implemented in Microsoft Access versions 2.0 and 7.0. The Jet 3.0 engine is used in 32-bit operating systems like Windows 95, and Jet 2.5 is used in 16-bit systems. Each of the Jet engine interfaces is implemented with a "type library" that matches their target operating system and code base. Before starting to code a new application, or once you import an existing Visual Basic application, you need to choose one of the three type libraries. The dialog box used to make this choice is under the Visual Basic Tools/References dialog box (see Figure 2).
Figure 2. Visual Basic Tools/References dialog box
The real purpose of the different type libraries is to help you remove dependencies on obsolete DAO code. When you use the 2.5 libraries, obsolete DAO objects are still available; so to make it easy to see which objects are affected, you can easily switch to the DAO 3.0 library for your 32-bit systems. When you recompile, all of the remaining obsolete objects are shown. The dual 2.5/3.0 typelib lets you continue to use Visual Basic 3.0 source code in a 32-bit system without significant change while also using the new Jet 3.0 Resultset object, its properties and methods.
Each of the new versions of Jet supports a litany of new features, which are highlighted in the session—many of which have been available in Microsoft Access 2.0 for some time now. Unfortunately, there is simply not enough time in an hour-long session to discuss the details of what took over a year and a half to document. This paper attempts to add some substance to the slides, but the best source of information is the Visual Basic version 4.0 documentation.
In general, Visual Basic version 4.0 implemented a new object model that makes many of the standard recordset objects obsolete. Several of the Visual Basic 2.0 methods carried over to Visual Basic 3.0 have also been sent to the scrap heap. Your code should continue to work, however, because if you use the compatibility version (Microsoft DAO 2.5/3.0 Compatibility Library), both the old and new objects are recognized. This transition is not without its bumps, however. You will find some areas where the code simply does not do the same thing as before. For example, when connecting to ODBC data sources, Jet now fetches the first (block of) row(s) and returns. In Visual Basic 3.0, Jet did not return until the last row was fetched. Although this new behavior results in much faster execution and is generally preferable, you may need to tweak your code to take this into account. In Visual Basic version 3.0, the RecordCount property immediately reflected the number of rows in the resultset. In Visual Basic version 4.0, that number is not valid until you move to the last record on your own. This also means that you won't be able to execute another operation on the same Database object until the first resultset is fully populated—which happens when you do a MoveLast. Simply performing a MoveLast after opening the Recordset reproduces the Visual Basic version 3.0 behavior, but means that your application must wait for this process to complete.
All three types of recordset objects made obsolete with Visual Basic version 4.0 are now supported in one, expanded, and improved Recordset object. The new Recordset object supports a new set of methods and properties that make working with ISAM data even easier. There are several new ways to position the current record pointer using row numbers—either relative or absolute. While this is heresy to relational types, this is good news to the flat-filers.
One of the most useful improvements is to the Find method. In the Visual Basic 3.0, the Find method basically fetched each individual row back from the original source, tested it, and if it qualified, repositioned the current row. This proved to be fine for small resultsets, but fatal for larger cursors where data had to be fetched over the network. The new Find logic performs a new query against the engine (local or external) and returns those few rows in the current resultset that qualify. Rows with matching bookmarks are retrieved from the original resultset that match this subset. This dramatically improves Find performance on larger resultsets. Visual Basic version 4.0 also gives you much more control over the size and handling of the buffers used to improve performance and reduce network load.
The Data control has also been rewritten to solve some of its most troubling problems including handling empty resultsets. Now you can set the EOFAction property to automatically create a new record if the Data control reaches EOF. The Data control now supports all three types of Recordset objects and can be passed an instantiated Recordset object created with DAO.
Database replication is the process of copying a database so that two or more copies stay synchronized. Each copy of the database is called a replica, and each contains a common set of tables, queries, forms, reports, macros, and modules. Replicas that belong to the same replica set can exchange updates of data or replicable objects. This exchange is called synchronization. Visual Basic version 4.0 includes support for the replication features provided by Jet version 3.0.
Microsoft provides programmers and users with four ways to use database replication. In order of complexity, these are:
The first three replication tools provide a easy to use visual interface, while the last enables developers to build replication directly into their applications. The Microsoft Jet Replication Engine is not a separately packaged product. Replication can be used on the following networks: Windows 95 peer-to-peer networks, Windows NT 3.51, and Novell Netware 3.x and 4.x network servers.
Several of the functions of Briefcase replication are available to developers and MIS support staff through data access object (DAO) programming.
Jet replication has provided extensions to the Data Access Objects (DAO) programming interface to Jet. (available through Microsoft Access for Windows 95 or Microsoft Visual Basic 4.0). Developers are provided with routines to:
These properties include the replica, the description of the replica set, the ID of the Design Master of a replica set, the default replica to be used in an exchange, and the local/global property for each object in the database.
Microsoft Excel for Windows 95, Microsoft Visual C++, and Microsoft Visual Basic for Windows 95 all use version 3.0 of the Microsoft Jet database engine directly or through DAO, and therefore, they can interface with Jet database replication. Microsoft Project uses Jet version 2.5 as its standard database format, but may also use ODBC to store its data in the Jet 3 format, and thereby benefit from Jet 3 replication.
Microsoft Excel for Windows 95 is not equipped to make a database replicable. However, if Microsoft Excel updates a database that has been made replicable by another Microsoft product, Excel will function ‘correctly’ with this replicable database, and any changes made to the database will correctly synchronize with other replicas.
To provide better performance against ODBC back-end servers, a number of changes were made in the Jet engine to deal with external databases and their special needs. Is the new version faster? Yes, to an extent. In some cases, Jet is much faster (when compared to Jet version 1.1) as it:
Unfortunately, even the newest versions of Jet do not utilize all of the new Microsoft SQL Server 6.0 features, or some of the more sophisticated aspects of TSQL and stored-procedure programming. Some of the unsupported features include:
Jet does support the new SQL Server identity column—but you have to ensure that the dbSeeChanges option is set on the Data control (set Options to 512) or on the OpenRecordset method.
The Data control has been greatly improved over the Visual Basic version 3.0 version. Its new features include:
The DBList, DBCombo, and DBGrid bound controls are all new for Visual Basic version 4.0. They are all capable of managing sets of records when bound to a Data control. All of these controls permit several records to be displayed or manipulated at once. The DBGrid control also supports an unbound mode, so it can be used with API-based or custom database interfaces.
The CheckBox, TextBox, Label, Picture, Image, ListBox, and ComboBox controls are also data-aware and can be bound to a single field of a Recordset managed by the Data control. Additional data-aware controls like the MaskedEdit and 3DCheckBox controls are available in the Professional and Enterprise Editions, and from third-party vendors.
When Visual Basic uses the Jet database engine to create a Recordset, no other Visual Basic operations or events can occur until the operation is complete. However, other Windows-based applications are permitted to continue executing while the Recordset is being created. If the user presses CTRL+BREAK while the Jet engine is building a Recordset, the operation is terminated, a trappable error results, and the Recordset property of the Data control is set to Nothing. In design time, a second CTRL+BREAK causes Visual Basic to display the Debug window.
When you use a Data control to create a Recordset object or when you create a Recordset object in code and assign it to the Data control, the Microsoft Jet database engine automatically populates the Recordset object. As a result, bookmarks (and for snapshot-type Recordset objects, recordset data) are saved in local memory; the user doesn't need to manipulate the Data control, and you don't need to invoke the MoveLast method in code. Page locks used to create the Recordset are released more quickly, making it possible for other Recordset objects to access the same data. Recordset objects created in code but not assigned to the Data control aren't automatically populated by the Jet engine. Populate these objects through code.
Important In Visual Basic version 4.0, the Data control no longer creates or accesses Dynaset objects. For this reason, some of the methods of the Dynaset object are not supported when used with the Recordset object created with the Data control. For example, the ListFields, ListIndex, CreateDynaset, and CreateSnapshot methods are not supported. All of the remaining methods and properties of the Dynaset object are supported by the dynaset-type Recordset object. Note that in some cases, properties that are valid when used with a Dynaset object are not valid with all types of Recordset objects that could be created by the Data control. For example, the Sort property is valid on a Dynaset and on the dynaset-type Recordset, but not valid on a table-type Recordset.
Another important option when using the Data control is the ability to execute stored queries. If you create a QueryDef object beforehand, the Data control can execute it and create a Recordset using the QueryDef object's stored SQL, Connect, and other properties. To execute a QueryDef, set the Data control's RecordSource property to the QueryDef name and use the Refresh method.
If the stored QueryDef contains parameters, you need to create the Recordset and pass it to the Data control.
One of the most powerful features of Visual Basic version 4.0 is its ability to accept alternative object models for specific specialized functions. The new RemoteData Control (RDC) and its Remote Data Objects (RDO) are examples of an object model extension. This custom control and associated object model addresses the specific needs of remote ODBC database engines—especially SQL Server. All of the features shown above as unsupported by the Jet/DAO interface are supported by the RDC/RDO.
The RDC is used as a replacement for the standard Data control and totally eliminates the need for the Jet database engine when accessing SQL Server, Oracle, and other ODBC remote server-class databases. No, the RDC/RDO do not support heterogeneous joins or updatable joins as it does not include its own cursor or query engine. Instead, RDO uses either the ODBC cursor library or SQL Server server-side cursors and the remote query engine—all query logic is performed on the remote server—not on the workstation. Using an RDC, you can create applications that use bound controls; just as you would with the Data control. However, the Remote Data Objects (RDO) implemented by the RDC are somewhat different than the DAO implemented by the Jet engine. However, I think you will find that the RDO has all the functionality you need, since much of the missing methods and properties were there to support's Jet's ISAM heritage.
The RDC/RDO also exposes ODBC handles needed to use direct ODBC calls against a variety of data sources. This way, if the interface does not support some required feature, you can attempt to use the ODBC API to implement it. The Remote Data Object model is designed around the ODBC API—it supports an object hierarchy identical to that used by the API. This approach gives you a higher degree of control over the user-interface and back-end interface and a lot more flexibility.
Many of you are trying to decide whether or not to use the Data Access Objects (DAO) included (in part) in Microsoft Visual Basic version 2.0, fully implemented in version 3.0, further enhanced in version 4.0 and replicated for ODBC back-end databases with the RDC/RDO. There are many considerations that make this decision tough. Hopefully, after having attended this session you will have a pretty good idea what to do—or you may decide to go into decorative landscaping instead.