In order to obtain more direct control over the SQL database, you can call the functions of the ODBC driver directly in the relevant DLL just as the Jet Engine does normally. As for the programming language used, the only restrictions that apply are those that generally apply for DLLs. When you bypass the Jet Engine, naturally you lose all the functions implemented by the Jet Engine
Program Structure
The structure of a VB program that accesses the database by calling the ODBC API directly is not necessarily made more complex by this fact. However, there is no longer any use of data controls and bound controls, nor can a number of data sources be accessed within one SQL statement. None of the conveniences associated with the use of record-set and database objects are available in such applications. At the same time, however, their main memory requirements are lower, dialog with the server database can be controlled much more closely, and it is possible to execute SQL statements without becoming blocked.
The figure below provides a general overview of the flow of an ODBC API application:
The figure shows that, following a preparatory phase during which the connection to the database is set up, SQL statements are placed in a buffer as strings. The statement is then examined and prepared by the ODBC driver, parameters - if any - are identified and buffers are prepared for parameter passing. Once the parameter buffers have been filled with values, the server database can execute the prepared statement. Execution usually results in one or more result sets being returned that contain data rows or an error. In contrast to access via the Jet Engine, execution of the statement does not yet transfer any data rows to the application program, since this is the responsibility of the application (as is any optimization associated with it).
Column Binding, Extended Fetch
The application asks the server database for data rows from the result set either one at a time or in blocks (extended fetch). Afterwards, the columns of the data rows can be accessed. The API also provides the option of binding local variables to the individual columns before the result set is accessed so that these columns will be filled with values directly when the ODBC driver fetches the data row(s) (column binding). It is recommended that you take advantage of this option since it not only saves time but also avoids superfluous program lines.
System Tables
Naturally, this type of application can also suffer from poor performance as a result of unwise programming. It is still generally advisable to address only the columns and data rows in an SQL statement that are actually needed. If the application accesses system tables (lists of all tables, indexes, views, users, etc.), keep in mind that in a larger database, these lists can take on considerable dimensions. Therefore, once you have requested the necessary information, you should maintain it locally.
Required Declarations
If you decide to use the ODBC API, you will need several files containing the declarations of the functions that form the API. Although it is possible to implement these definitions yourself using the right documentation, it is also cumbersome. Fortunately, however, these files already exist as a component of the ODBC SDK (Software Development Kit) Version 2.0, which must be purchased separately from Microsoft Support:
odbcor_g.bi Constants used by the core functions
odbcor_m.bi Declarations of core functions
odbext_g.bi Constants required for the "extended odbc functions"
odbext_m.bi Function declarations required for the "extended odbc functions"
Aids to Decision-making
Use of the ODBC API does not automatically determine that you will use a specific database; instead, it leaves this decision open until you need to utilize specific characteristics of the server database. There is a number of good reasons for deciding in favor of the ODBC API when designing an application and for rejecting the more convenient alternative, Jet Engine and DAO.
· The application must make do with a small amount of main memory.
· Performance is unsatisfactory, even though the server database quickly executes the application's SQL statements.
· The application must utilize characteristics of the server database that are not accepted by the Jet Engine even with SQL_Passthrough (e.g. passing of parameters to stored procedures, multiple result sets).
· Knowledge of embedded SQL and ODBC can make it easier to decide in favor of the higher-performance interface.