Implementing DAO and Microsoft Excel

DAO and ODBC

Using DAO you can connect to an ODBC data source such as SQL Server by opening them directly, attaching to linked ODBC data sources, or by using ODBCDirect. Remember that RDO and ADO are better alternatives to DAO when creating client/server applications.

Opening Directly

Although not an optimal approach, DAO can be used to open ODBC data sources directly. A connect string must be created that contains information such as the ODBC driver name, server name, database name , login id, and password. This string is provided as a parameter while connections are made to the database and result sets are created.

Example

This example uses Visual Basic to pass a connect string to DAO for the pubs database on a SQL Server.

 Db.Connect = _ "ODBC;Database=pubs;UID=UserName;PWD=Password;DSN=PubsData" 

Performance is limited with this approach because:

• Jet does not always make the most efficient use of connections into an ODBC data source.

  Connections are usually created and dropped as result sets are created. With no persistent connections, Jet spends a significant amount of time negotiating and making connections.

• Jet does not cache information coming from the ODBC data source.

  To access any catalog information about data source objects, a connection must be made and the data source queried directly.

Attaching ODBC Data Sources

The native data format for Jet is the .mdb format used by Microsoft Access. Jet can attach remote data sources directly to an .mdb file and use standard DAO code to retrieve and manipulate data. These data sources appear as linked tables in Microsoft Access. This approach is usually faster than opening an ODBC data source directly using DAO, because the .mdb file provides a location to cache catalog information about the remote data source. This eliminates the requirement to retrieve this information directly from the data source.

Using ODBCDirect

ODBCDirect is a feature of DAO that allows for more direct access to ODBC by providing DAO access to Remote Data Objects (RDO). RDO will be discussed further in the next section. When opening ODBC data sources through ODBCDirect, the DAO object model separates from the Jet engine. This approach is useful when a combination of ISAM and ODBC data sources are referenced in the same applications. ODBCDirect allows you to open a DAO workspace that routes calls to remote data sources without using the Jet engine. As its name implies, ODBCDirect accesses the remote data source directly through ODBC rather than going through Jet.

Example

In this example, two database objects are created in Visual Basic, one connecting through Jet to a Microsoft Access database and the other using ODBCDirect to bypass Jet and connecting to a SQL Server:

 Dim wrkJet As Workspace Dim wrkODBC As Workspace Dim db As Database Dim cn As Connection Dim rs1 As Recordset' To be used by Jet Dim rs2 As Recordset' To be used by ODBCDirect ' Open Microsoft Jet and ODBCDirect workspaces Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet) Set wrkODBC = CreateWorkspace("", "admin", "", dbUseODBC) ' Connect using the Jet OpenDatabase method Set db = wrkJet.OpenDatabase("Northwind.mdb") ' Connect using the ODBCDirect OpenConnection method Set cn = wrkODBC.OpenConnection("", , , _ "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers") ' Open a Jet Recordset Set rs1 = db.OpenRecordset _ ("SELECT * FROM Employees", dbOpenDynaset, dbReadOnly) ' Open ODBCDirect Recordset Set rs2 = cn.OpenRecordset _ ("SELECT * FROM stores", dbOpenDynamic) ' Add code here to process the records . . . ' After processing records, close all the connections rs1.Close rs2.Close db.Close cn.Close wrkJet.Close wrkODBC.Close