Use ODBCDirect with Microsoft Access

What Is ODBCDirect?

ODBCDirect is a technology that enables you to work with ODBC database servers without loading the Microsoft Jet database engine. ODBCDirect relies on the Microsoft DAO 3.6 object model, so that you can easily modify your existing DAO code to take advantage of ODBCDirect. Microsoft DAO 3.6 includes new objects, methods, and properties to support ODBCDirect.

Advantages of Accessing ODBC Data with ODBCDirect

ODBCDirect offers the following advantages for ODBC operations:

ODBCDirect can make your code faster and more efficient by providing direct access to ODBC data sources. Since it doesn't require loading the Microsoft Jet database engine, ODBCDirect consumes fewer resources on the client side. The ODBC server is responsible for all query processing.
ODBCDirect gives you improved access to server-specific features that aren't available by using ODBC through Microsoft Jet. For example, for servers that support cursor specification, ODBCDirect allows you to specify where cursors are located, whether locally or on the server. In addition, to interact with stored procedures at the server level, you can specify input values and check return values, which you can't do when using Microsoft Jet.
ODBCDirect also supports asynchronous queries. When you execute a query, you don't have to wait for the query to finish running before you begin another operation. You can track the query's execution by checking the StillExecuting property.
ODBCDirect supports batch updating, enabling you to cache Recordset object changes locally and then submit these changes to the server in a single batch.
With ODBCDirect, you can create simple cursorless result sets, or more complex cursors. You can also run queries that return any number of result sets. You can limit the number of rows returned and monitor all the messages and errors generated by the remote data source without affecting the performance of the executing query.

Creating an ODBCDirect Workspace

You can create an ODBCDirect workspace by specifying the constant dbUseODBC for the type argument of the CreateWorkspace method. You can also specify that an ODBCDirect workspace be created by default by setting the DefaultType property of the DBEngine object.

Once you've created an ODBCDirect workspace, you can use specific DAO objects, properties, and methods to work with data on the ODBC database server.

Advantages of Accessing ODBC Data with Microsoft Jet

Microsoft Jet and ODBCDirect workspaces provide different but complementary functionality. You should use a Microsoft Jet workspace to access .mdb files and ISAM data formats, such as text and spreadsheets. Microsoft Jet provides unique capabilities that aren't available through ODBCDirect, including the following:

Updatable joins. You must use a Microsoft Jet workspace to update data in Recordset objects based on multiple-table joins.
Heterogeneous joins. You must use a Microsoft Jet workspace to perform joins of tables in different data sources.
Data Definition Language (DDL) operations. You must use a Microsoft Jet workspace to perform DDL operations using DAO. ODBCDirect doesn't provide a TableDef object, so you can't create or modify tables by using DAO. You can, however, perform DDL operations by using ODBCDirect to execute SQL DDL statements.
Form and control binding. If your application requires that forms or controls be bound to data in an ODBC data source, you must use Microsoft Jet. Data accessed within an ODBCDirect workspace can't be bound to forms or controls.

If you don't need these capabilities, you can use an ODBCDirect workspace.

Note You can define both Microsoft Jet and ODBCDirect workspaces in your applications and mix them in any fashion. For example, in the same function, you can define a Microsoft Jet workspace to perform DDL operations using DAO and you can also define an ODBCDirect workspace to perform asynchronous queries.