Server Connectivity

One of the most confusing choices when designing a Visual Basic database application is choosing the data access API. The options for Visual Basic to SQL Server can be summarized as follows:

1. Bound data control.

2. Data access objects (DAO) with updatable dynasets.

3. DAO with non-updatable dynasets and SQL_PASSTHROUGH.

4. DB Library or an abstraction thereof, such as VBSQL.VBX or SQL Sombrero by Sylvan Faust.

5. Call the ODBC API directly.

I usually rule out the first three because of the lack of low-level control over the DBMS and poor performance they provide. They may, however, be suitable for a small number of users or no future need to support server-specific functionality.

A more interesting choice is between ODBC and the native DBMS API, DB-LIB. VBSQL and SQL Sombrero are probably easier to develop with than direct ODBC calls and do not sacrifice any server-based functionality or performance.

However, a carefully constructed Visual Basic abstraction of the ODBC API can be developed once and shared by all developers. This is what the VBODBC library included with this presentation does.

ODBC provides the following advantages over the native API:

• Companies often have more than one DBMS in house. An ODBC standard means that developers only have to learn one API.

• A DSS application can be written to a server-based DBMS and operate on a different single-user DBMS as well. This is often a requirement when remote users want a subset of the data warehouse "to go" on laptops, and IS doesn't want to maintain two different front-ends.

• Client machines only have to maintain one type of database driver (many ODBC drivers, however, will also install the underlying native API).

• Migrating back-ends is a more straightforward process, although the "seamlessness" of it is over-hyped.

This choice depends on the existence of a good ODBC driver, of which until recently there were few. ODBC is enjoying widespread industry support, however, and will only mature—it's an investment that will last.

You can expect, however, products that abstract multiple DBMSs as an object model (as opposed to an API) to eventually mature. Right now we have products that operate well against only one DBMS (like SQL Sombrero for SQL Server, and Oracle Objects for Oracle), or products that operate poorly against many DMBSs (like Jet), but we do not yet have the best of both worlds.