There are two different ways to use DAO to connect to ODBC data sources: through Microsoft Jet, or by means of a new technology called ODBCDirect. If you’re working with a database created with Microsoft Jet or in an external format supported by an installable ISAM, all DAO operations are processed through Microsoft Jet. If you’re working with an ODBC data source, you can either process DAO operations through Microsoft Jet, or you can use ODBCDirect to work directly with the data in the ODBC data source without using Microsoft Jet.
Whether you use DAO with Microsoft Jet or with ODBCDirect to work with an ODBC data source depends on what kind of operations you need to perform on the data source. You can use DAO with Microsoft Jet when you need to take advantage of the unique features for ODBC operations in Microsoft Jet, such as the ability to join data from different database formats.
You can use ODBCDirect when you need to run queries or stored procedures against a back-end server, such as Microsoft SQL Server, or when your client application needs only the specific capabilities of ODBC, such as batch updates or asynchronous queries. ODBCDirect can also make certain client/server operations significantly faster.
Because not all DAO features are available with ODBCDirect, DAO still supports ODBC through the Microsoft Jet database engine. You can use ODBC through Microsoft Jet, ODBCDirect, or both, with a single ODBC data source.
Which of these two methods you can use to access an ODBC data source is determined by what type of workspace you’re working in. A workspace, represented by a Workspace object, is an active session for a particular user account. A session marks a sequence of database operations. A session begins when a particular user logs on and ends when that user logs off. The operations that a user can perform during a session are determined by the permissions granted to that user.
The security system that controls permissions is determined by the kind of workspace you are using. In a Microsoft Jet workspace, the Microsoft Jet database engine enforces security for local objects, and the server you are connecting to, such as Microsoft SQL Server, enforces security for its objects. In an ODBCDirect workspace, the server you are connecting to enforces security. A workspace also provides mechanisms for performing multiple operations against a database as a single transaction that can be rolled back if it fails. If you don’t specifically create a workspace, then DAO creates a default workspace for you.
See Also For more information on transactions, see “Microsoft Jet Transactions” in Chapter 5, “Working with Records and Fields.”
Note Each type of workspace has its own object model. For more information on the object model for Microsoft Jet workspaces, see Chapter 2, “Introducing Data Access Objects.” For information on the object model for ODBCDirect workspaces, see “The Object Model for ODBCDirect Workspaces” later in this chapter.
When you’re working with an ODBC data source, you’ll need to decide whether you should use ODBC with Microsoft Jet, ODBCDirect, or both. This section discuss the advantages of both ODBC with Microsoft Jet and ODBCDirect. It also explains how to register an ODBC data source, whether you’re working with a Microsoft Jet workspace or with an ODBCDirect workspace.