Advanced Client-Server Issues

Michael Mee
Jet Program Manager, Microsoft Developer Division

Created: January 9, 1995

techart

Introduction

This article covers Microsoft® Visual Basic® (VB) options for accessing server data, general client-server strategies, and data access object (DAO) (Jet/VB)-specific information. As a companion article to the "Advanced Client-Server Issues" presentation prepared for VBITS '94 (Visual Basic Insider's Technical Summit), it provides more detail than the presentation, as well as background and supporting information. If you missed the talk, you can find most of its content in this paper. If you attended the talk, some of the points covered briefly are explained in more detail.

VB Client-Server Options

With a general-purpose tool such as Visual Basic, you can obtain client-server data in a number of ways. Some of these are:

• VBSQL (~DB-Library)
  
  
• Open database connectivity (ODBC)
  
  
• DAOs
  
  
• Third-party alternatives (usually for specific servers)

This paper discusses the first three alternatives in more detail.

VBSQL

VBSQL is a version of DB-Library that has been modified for use with Visual Basic. DB-Library is the low-level application programming interface (API) that Microsoft SQL Server provides to support client application programmers. This is normally programmed from C, but the VBSQL custom control (VBX) provides Visual Basic programmers with almost all the same functionality. DB-Library functionality that is missing from VBSQL includes the ability to specify data binding (not a great loss, because it is handled automatically) and two-phase commit.

In addition, VBSQL sometimes bundles several calls together. For example, the VBSQL SQLOpenConnection function corresponds to several DB-Library calls.

You may wonder why a VBX is provided. Surely it would be enough just to expose several entry points by way of an appropriately declared header file. The problem with dynamic-link library (DLL) entry points is that they don't provide a way for a VB user to have code execute asynchronously. The VBX provides this ability. For example, by using a VBX, you can make it possible for events to be triggered by the underlying DB-Library code. (C programmers do the equivalent in DB-Library by registering a callback.) These events are fired for messages or errors encountered by the SQL server as it executes your code. This lets your VB code cancel an executing query or run a query on the server, while continuing to execute code on the client.

Some of the unique functionality provided by VBSQL includes:

• Full stored procedure support
  
  
• Granular error control
  
  
• Full control of SQL Server options
  
  
• The ability to complete asynchronous queries (such as background query processing)
  
  
• DB-Library provided keyset-driven cursors (but you should note that this is not a panacea)
  
  
• Bulk copy operations

All this functionality comes at a price. Over 120 VBSQL function calls are available to provide these features. In addition, you are responsible for administering your own connections, error handling, data fetching, and data buffering.

For example, retrieving data from a server involves the following steps:

1. Coding a message and error handler.
   
   
2. Initializing VBSQL.
   
   
3. Opening a connection to the server.
   
   
4. Creating and sending the appropriate SQL statement.
   
   
5. Retrieving the data.
   
   
6. Closing the connection.

To better understand these steps, look at the sample code for a program that retrieves data from a server. Because all the code you write will be specific to VBSQL, it will work only with Microsoft SQL Server.

ODBC

Open database connectivity (ODBC) is a well-known but sometimes misunderstood beast. According to marketing materials, ODBC:

• Lets you access data in more than one data storage location (for example, more than one server) from within a single application.
  
  
• Lets you access data in more than one type of database management system (DBMS), such as DB2®, ORACLE®, Microsoft SQL Server, DEC® Rdb, Apple® DAL, and dBASE®, from within a single application.
  
  
• Greatly simplifies application development, making it easier for developers to provide access to data in multiple, concurrent DBMSs.
  
  
• Is a portable API, which allows the same interface and access technology to serve as a cross-platform tool.
  
  
• Insulates applications from changes to underlying network and DBMS versions. This means that modifications to networking transports, servers, and DBMSs will not affect current ODBC applications.
  
  
• Promotes the use of SQL—the standard language for DBMSs—as defined in the ANSI 1989 standard. It is an open, vendor-neutral specification based on the SQL Access Group (SAG) Call Level Interface (CLI).
  
  
• Lets corporations protect their investments in existing DBMSs and developers' acquired DBMS skills. In this way, ODBC lets corporations use existing diverse DBMSs, while continuing to "rightsize" applications.

Although these marketing claims could be overstating the case, there is a lot of truth in them. The proof is in the large number of tools available that support ODBC drivers, and the large number of drivers available that work with those tools. The practical upshot of the ODBC standard is that many more choices are now available to developers who want to get to server data.

Performance is occasionally cited as a concern with ODBC. Indeed, early drivers and tools that used those drivers were slower than native APIs. However, tools have since made use of the ODBC API more effectively, and ODBC drivers have significantly improved performance in many cases, forcing changes to the native APIs as a result. Today, the speed differences between VBSQL and the ODBC SQL Server driver are negligible.

You might wonder why anyone would use VBSQL, if the ODBC API is so good. A main feature of the ODBC API is that it works across all databases. As a result, it must inevitably find a core set of functionality that all databases can support. This means that functionality that is unique to a given server may not be exposed elegantly by way of ODBC. In extreme cases, it may not even be exposed at all.

It is worth noting that anything that can be expressed in a server's SQL language is almost always available. Indeed, it is hard to point to any feature of Microsoft SQL Server that is not available with ODBC. Stored procedure support is available, as are triggers. Error handling is perhaps highlighted the most, probably because VBSQL allows asynchronous error handling, while ODBC requires a polled approach.

So, in a nutshell, the pros and cons of ODBC are:

Pros

• One programming API for all servers
  
  
• Tight resource control possible

Cons

• Not all back-end specific support provided
  
  
• Can be complex to program
  
  
• Uses more memory than pure VBSQL

For comparison purposes, here are the steps required to retrieve data from a server:

1. Initialize ODBC.
   
   
2. Open a connection to the server.
   
   
3. Create and send the appropriate SQL statement.
   
   
4. Retrieve the data.
   
   
5. Close the connection.

Data Access Objects (Jet)

Data Access Objects (DAO) refer to the group of programmable objects that are part of Visual Basic (and Microsoft Access). For example, the Database, Tabledef, and Querydef objects are all part of DAO. DAO uses the Microsoft Jet Database Engine, Jet. This engine is used by both Microsoft Access and Visual Basic, and essentially the same database programming language is exposed in both products. Jet itself is composed of many parts, as shown in the following figure.

It's hard to do a simple comparison of Jet directly against VBSQL or ODBC, because Jet provides many more features. For example, using Jet to access server data allows for:

• Fully scrollable result sets.
  
  
• Updatable queries (including joins).
  
  
• Queries, such as heterogeneous joins, that act across multiple databases.
  
  
• ODBC support (Jet can access most ODBC databases).

In addition, programming for Jet is far simpler than programming for VBSQL or ODBC, and typically requires many fewer lines of code.

If you need some of these features, it's hard to look past what Jet provides. However, Jet might require certain adjustments. Some of the costs associated with Jet include:

• Tuning, in some cases, for optimal performance.
  
  
• Harder-to-access server-specific features (complex stored procedures, for example).
  
  
• More memory requirements than pure ODBC (or VBSQL).

Summary of Options

This table won't explicitly tell you which method to use for a specific application. In making your decision, you'll also need to consider the overlap between techniques and outside variables (such as the background of the programmers writing the application). To help you further, the following lists suggest applications that highlight the strengths of each technique.

VBSQL: Highly efficient, asynchronous background task using specific Microsoft SQL Server features. This application makes use of:

• Small memory footprint.
  
  
• Event-driven error and message handling.
  
  
• Known back-end server.
  
  
• Skilled developers (typically C programmers).

ODBC: Small, fast data-entry application, with advanced error handling and large installed base. This application makes use of:

• Highly efficient hand-tuned code.
  
  
• Reasonable memory requirements.
  
  
• Portability across several back ends.
  
  
• Developers who are comfortable with database API-level programming (such as former embedded SQL programmers).

DAO: Interactive browsing tool requiring rapid development, accessing multiple vendor databases (including non-server databases). DAO makes use of:

• Full scrollable cursors with minimal programming.
  
  
• Quick and simple query generation, regardless of data source.
  
  
• The ability to join data across several databases.
  
  
• The ability to update resulting queries.
  
  
• Developers who are comfortable with Visual Basic. They do not necessarily have to have much database experience

General Client-Server Strategies

To best apply client-server technology, the following strategies are recommended:

• Request only the data you need.
  
  
• Store data locally.
  
  
• Use only the functionality required.
  
  
• Batch updates and use transactions.
  
  
• Move your data to a server.

Request Only Needed Data

The more data you request from the server, the more processing time you use and the more network traffic you create. To request less data in your forms:

• Request as few records as necessary. For example, rather than opening a large result set, you should use a restriction to limit the size of the result set (being careful to use restrictions that the server can process).
  
  
• Base your forms on queries that select as few remote fields as necessary, rather than directly on the tables. That way, only fields used on the form are actually retrieved from the server. If you need to add fields to the form later, add them to the query's SELECT list.
  
  
• Use fewer bound controls, such as list boxes and combo boxes. Each form control that is bound to a remote field requires a separate query to be sent to the server when the form is opened. In particular, avoid list boxes and combo boxes with large row sources for the list.

In some cases, you may want to display all the fields in a record on a form. In other cases, you might not need to make all the fields available to the user. For example, because Memo and OLE Object fields can take a long time to retrieve from a server, you should display them on a form only when requested.

To speed up processing of your forms, use the following steps to avoid retrieving unnecessary data:

1. Display the most important fields on a main form and provide a More Information button that opens another form containing other fields.
   
   
2. Base the second form on a query that is parameterized by the primary key field on the main form.

Store Some Data Locally

Often, an application contains several forms that use the same remote table—for example, as the source for a list box or combo box. If the data in the table doesn't change frequently, you can speed up form loading and reduce server load by using one of the following techniques:

• If the table contains a small amount of data that never changes (such as the names and two-letter abbreviations of the 50 states), you should store it in the local application database. If the table is joined in queries with remote tables, you should also keep a copy of it on the server to avoid heterogeneous joins.
  
  
• If the data in the table changes rarely (such as a list of buildings in a company), it should reside both on the server and in the local application database. In addition, your application should provide a way for the user to download the table when the data does change.
  
  
• If the data in the table changes occasionally (that is, less than daily, such as a list of employees in a small company or department), you should store it both on the server and in the local application database, and your application should automatically refresh the local version each time it starts. This method uses extra time when the application starts, but speeds up queries when the application is running.

Use Only the Functionality You Need

• Don't use scrolling cursors if you don't need to scroll. Many applications only move forward through result sets.
  
  
• Don't use updatable cursors if you're only reading.
  
  
• Connections are expensive; don't keep them open if you're not using them. Reuse them when possible to avoid startup overhead.

Batch Operations When Possible

• Cache multiple inserts locally and update the server in one operation.
  
  
• Place transactions around multiple updates to help data integrity and ease programming complexity.
  
  
• Use parameterized queries to reduce query compilation and execution time.

Moving Your Data to a Server

The best approach to creating a client-server application in Visual Basic is to create the tables that will contain the data on the server itself. However, because the Microsoft Jet database engine works nearly the same with tables from any source, you can also create the tables in your local Microsoft Jet database, export them to a server (by using a SELECT INTO query), and, as a final step, attach the remote tables in your local database. Although this second technique may be more convenient—especially if your database design hasn't solidified—there are complications in moving the data to the server. If you decide to start with Visual Basic tables and move them to a server later, you should keep the following issues in mind.

Indexes

Exporting a table to a server creates no remote indexes. Before attaching the exported table, you must create a unique index on it by using a pass-through query or a separate server-based tool if you need to update the table's data. You'll probably want to define other indexes as well, to improve query performance.

Counter fields

Because most servers don't provide the automatic-numbering functionality of a counter field, Microsoft Jet exports counter fields as long integers. To mimic a counter field, you have to either create an insert trigger on the server that provides the next key value, or write a form-level event procedure in your Visual Basic application to provide this functionality.

Default values

Default field values aren't exported with a table, but because most servers support default values, you can create them directly on the exported table. Note that Visual Basic won't automatically display the values in a new record in a form or grid; they are displayed only after you save the record without entering a value in the field.

Validation rules

Validation rules aren't exported with a table. You can create triggers or rules on the remote table that enforce your rules, or you can enforce rules by writing code in a data control's Validate event. Using form-level validation is easier, but not as reliable, because invalid entries are rejected only by the form, not by the server table itself. If you're using a server-based trigger, be aware that many Visual Basic functions (such as DatePart or Format) have no equivalent on your server.

Relationships

If your server implements enforced or cascading relationships, you can define them, but Visual Basic won't be aware of them. Otherwise, you can mimic these types of relationships by using server-based triggers or local form-level event procedures. You can mimic enforced relationships by prohibiting operations that violate the relationship, and you can mimic cascading relationships by manually synchronizing keys when you update related tables.

Security

Any security you establish in your Microsoft Jet database has no effect on security on the server. Visual Basic isn't aware of server security, although it can't violate that security. For example, if you're editing a remote table for which you don't have INSERT permission, you can type a new record. However, when you try to save it, the server returns an error message, and the record isn't inserted. You can minimize these discrepancies by synchronizing local and remote user permissions and passwords. This means you have to log on only once, because Microsoft Jet automatically attempts to log on to the server by using your local user permission and password. It prompts you only if this logon fails.

Table and field names

Microsoft Jet lets you use almost any character in a table or field name, but many servers have more restrictive naming conventions. Therefore, when you export a table, each non-alphanumeric character in every name is converted to an underscore (_). As a result, queries, forms, and other Visual Basic objects in your application may no longer work. To solve this problem, you can create a name-mapping query. For example, suppose you have a local table called Order Details, with three fields: Product ID, Quantity, and Price/Unit. After exporting and attaching the table, you can map the names by following these steps:

1. Name the attached table Order_Details. Microsoft Jet renames the fields Product_ID, Quantity, and Price_Unit.
   
   
2. Rename or delete the local table.
   
   
3. Create a query based on this SQL statement:

   SELECT Product_ID AS [Product ID], Quantity, Price_Unit AS [Price/Unit] FROM Order_Details
   

Name this query "Order Details." This query makes it possible for all existing queries, forms, and other objects that use the local table, to continue to work with the remote table, without imposing any extra overhead or limiting functionality.

DAO Strategies and Hints

The following sections describe several strategies and hints for using DAO.

Update VB to Use Jet 2.0 with the VB Compatibility Layer

With the Visual Basic Compatibility Layer, you can use the Jet 2.0 engine from VB 3.0. In addition to letting you use Microsoft Access 2.0 .MDB files, it also has many enhancements for client-server programming.

Attach Server Tables

The most common and efficient way to access data on a server is to attach tables from the server. With few exceptions, you can use attached tables in your application, just as you would Microsoft Jet tables. Microsoft Jet automatically connects to the server when you use an attached table.

Microsoft Jet stores field and index information for the tables you attach, which improves performance when you open the tables. Note, however, that you must reattach remote tables if you make changes to fields or indexes on the server.

If your server supports the creation of SQL views, you can attach them to your local database. Microsoft Jet treats an attached view exactly like an attached table with no indexes. The processing defined in a view is always performed by the server, no matter what is executed locally.

If your server lets you update data by using views, you need to tell Microsoft Jet which field or fields uniquely specify a record returned by the view. To do this, create an index specification on the attachment. Although not actually an index, this specification lets Microsoft Jet create an updatable dynaset on the view, which can be used by forms and queries.

For example, suppose you attach an SQL view named SeptOrdersView that returns a subset of records in the remote Orders table, and you name the attachment SeptemberOrders. The OrderID field is still unique within the view, so you would run a data-definition query (not a pass-through query) by executing the following SQL statement:

CREATE UNIQUE INDEX index1 ON SeptemberOrders (OrderID)

This doesn't create an index on the server, nor does it take any extra space in your local database. It simply tells Microsoft Jet that the OrderID field is the primary key of the attached view.

Use Pass-Through Queries

In many applications, you'll use both ordinary Microsoft Jet queries (that is, queries optimized by the Microsoft Jet database engine) based on attached remote tables and pass-through queries. With an ordinary query, Microsoft Jet compiles and runs the query, combining the power of the server with the capabilities of the Jet database engine. With a pass-through query, you enter an SQL statement that Microsoft Jet sends directly to the server, without stopping to compile the query.

Pass-through queries offer the following advantages over other queries:

You don't need to consider how much of your pass-through query is being sent to the server for processing, because the query is sent as you enter it. This means that more processing occurs on the server and less data is exchanged over the network.

• Pass-through queries can use server-specific functionality—such as stored procedures and server-based intrinsic functions—that have no Visual Basic equivalent.
  
  
• Pass-through queries can log informational messages returned by the server, such as warnings and statistics.
  
  
• Pass-through queries let you use nonstandard SQL extensions supported by the server, as well as data definition and server administration and security commands.
  
  
• You must use a pass-through query if you want to join tables from different databases on a server (if the server supports multiple databases). Because each database is a separate ODBC data source, an ordinary query would consider the join to be heterogeneous and would execute at least part of the query locally.
  
  
• Update, delete, and append pass-through action queries are faster than action queries based on attached remote tables, especially when many records are affected. However, a pass-through action query cannot partially succeed as an ordinary action query can. In addition, you are limited to action queries that your server can run; if an action query uses multiple tables, you may have no choice but to use an ordinary query.

On the other hand, pass-through queries have several disadvantages:

• A pass-through query always returns a snapshot, which can't be updated. An ordinary query usually returns a dynaset, which reflects other users' changes and can be updated.
  
  
• To create a pass-through query, you must know the exact SQL syntax supported by your server. SQL syntax and conventions often vary from server to server, particularly outer join syntax, intrinsic function names, wildcard characters, and the use of single versus double quotation marks. All ordinary Microsoft Jet queries use the same syntax, regardless of where the data is stored. Microsoft Jet automatically translates intrinsic functions, wildcard characters, and other elements into the appropriate ODBC syntax.
  
  
• You can parameterize an ordinary query, which makes the query much more flexible and reusable. In contrast, pass-through queries can't be parameterized in this manner.

Use Less Functionality

To improve the performance of your client-server application, you may want to avoid using some of the more powerful features of Visual Basic. This section discusses when you can improve performance by using less functionality in your forms.

Snapshots versus dynasets

When using remote data, Visual Basic provides two kinds of recordsets to base your data controls on: dynasets, which can be updated; and snapshots, which cannot. If you don't need to update data with your form, and if the recordset contains fewer than 500 records, you can reduce the time it takes the form to open by using a snapshot. This is generally faster than using a dynaset.

However, if the recordset you're using is large or contains Memo or OLE Object fields, using a dynaset is more efficient. If you use a dynaset, Microsoft Jet retrieves only the primary key of each record; if you use a snapshot, it retrieves the entire record. A dynaset is also more efficient because Memo and OLE Object fields are retrieved only if they are displayed on screen.

Multiple-table queries versus multiple-data controls

You can display information from multiple tables in two ways. You can either base the form on a query that joins the tables, or you can use multiple-data controls (on the same form, or on different forms). The first approach usually sends a single query to the server, which performs the join. The second approach sends a separate query for each data control. This second approach requires more time for the form to load and more overhead.

Because you can update and insert data in almost every field of a multiple-table query, multiple- data controls are unnecessary. If you don't need the convenient display capabilities of multiple- data controls, you should base your form on a single query for better performance.

References

The Microsoft Developer Network Development Library has many articles of interest to database programmers. Some of these articles are also available in other forms directly from Microsoft, usually from one of their download services, such as the Internet server at FTP.MICROSOFT.COM. These articles include:

"Jet Database Engine ODBC Connectivity" by Neil Black and Stephen Hecht. (MSDN Library Archive, Backgrounders).This paper describes in great detail how Jet uses ODBC to retrieve server data. This is required reading for anyone using DAO to write significant server applications.

"ODBC: Architecture, Performance, and Tuning" by Colleen Lambert. This paper provides a good overview of how ODBC works and addresses performance issues in a realistic and useful fashion.

"A Client-Server Application: From Concept to Reality" by Greg Nelson and Roger Jennings. (MSDN Library Archive, Conference and Seminar Papers).

Some books that cover relevant topics are also starting to appear:

Visual Basic Database Programming by Karen Watterson. Published by Addison-Wesley. This is a good overview of all aspects of database programming from Visual Basic.

Hitchhiker's Guide to VBSQL by Bill Vaughn. Subtitled "The Developer's Roadmap to the Visual Basic Library for SQL Server," this is a definitive guide to using VBSQL directly. It is available from Fawcette Technical Publications or directly from the author at Beta V, 16212 NE 113th Ct, Redmond, WA 98052-2773, (206) 556-9205.

ODBC 2.0 Programmer's Reference and SDK Guide by Microsoft Press, ISBN 1-55615-658-8 (this guide is also in the Microsoft Development Library).

Support

Free support for all the products detailed in this paper is available on CompuServe®.

VBSQL support is provided in the MSSQL forum (Section 4, "MS Program Toolkits").

ODBC programming support for general Software Development Kit (SDK) questions is provided in the WINEXT forum (Section 10, "ODBC"). For more specific Microsoft SQL Server driver questions, refer to the MSSQL forum (Section 5, "ODBC Dvr for MS SQL").

DAO/Jet /Visual Basic support is available from the MSBASIC forum (Section 3, "Data Access Objects," and Section 4, "The Data Control"). If you're willing to move your frame of reference to Microsoft Access, you will find excellent support for specific database questions in the MSACCESS forum. Unlike the VB support engineers, Microsoft Access engineers live and breathe databases and hence sometimes are able to answer questions that stump the VB support folks. Don't expect Microsoft Access support to know anything about VB's data control, however.

Paid support is available direct from Microsoft. Call (800) 936-3500 for details. You may also be able to find a local Microsoft Solution Provider (SP) that can help you. Call (800) SOL-PROV for details or referrals to a local SP.

Where Do I Get it?

VBSQL is contained in the Microsoft SQL Server for Windows NT™ Programmer's Toolkit (for Windows NT and OS/2®), Part # 228-174v421.

You can receive the ODBC 2.0 SDK by joining the Microsoft Developer Network. A Level 1 subscription provides documentation and sample code. A Level 2 subscription includes the actual SDK files needed to create applications. Call (800) 759-5474 for details on how to join.

The Visual Basic Compatibility Layer (VBCL) is included in the Microsoft Access Distribution Kit and the Office Developers Kit. It can also be downloaded from the usual Microsoft download points.

Note that with the VBCL, you will also need a copy of the Jet 2.0 engine, which is currently available with Microsoft Access 2.0 or the Microsoft Access Distribution Kit. Given that you'll need Microsoft Access 2 to set up some of the new Jet 2.0 features (for example, cascading relationships or pass-through queries), buying Microsoft Access 2.0 is probably the best way to obtain Jet 2.0.