Converting DAO Code to ADO Code

If you want to convert your old DAO code to new ADO code — perhaps for consistency with ADO code working with other types of data, or out of concern that DAO will no longer be supported in future versions of Access — you can use Table 5.7 as a guideline. Bear in mind that some types of DAO code can't be converted to ADO, because they have no equivalent in the ADO object model, so you will still need to use DAO for Access form recordsets, or creating tables and their fields pro-grammatically.

You can't exchange data between ADO and DAO recordsets, even when working in databases with references set to both object models.

TABLE 5.7

ADO Equivalents of DAO Objects

DAO Object

ADO Object

Notes

DBEngine

No equivalent

Not needed

Workspace

No equivalent

Not needed

Database

Connection

Recordset

Recordset

Dynaset type

Keyset cursor

Snapshot type

Static cursor

Table type

Keyset cursor with acCmdTableDirect option

Field

Field

Recordset fields only

QueryDef

No direct equivalent, but can use the Command object to get the same functionality

TableDef

No equivalent

When using the DAO object model to work with Access data, the following code segment opens a recordset based on a query in an external database:

Dim dbs as DAO.Database Dim strDBName As String Dim rst As DAO.Recordset strDBName = "E:\Documents\Northwind.mdb" Set dbs = OpenDatabase(Name:=strDBName)

Set rst = dbs.OpenRecordset(Name:="qryCurrentOrders", _ Type:=dbOpenDynaset)

This ADO code opens an equivalent recordset:

Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim strDBName As String Dim strConnectString As String Dim strQuery As String

Create a connection to an external database.

strDBName = "D:\Documents\Northwind.mdb" Set cnn = New ADODB.Connection Set rst = New ADODB.Recordset strQuery = "qryCategorySalesFor19 97"

Need to specify the Jet 4.0 provider for connecting to Access databases.

With cnn

.Provider = "Microsoft.Jet.OLEDB.4.0" .Open strDBName strConnectString = .ConnectionString End With

Open a recordset based on a saved query.

rst.Open Source:=strQuery, _ ActiveConnection:=cnn, _ CursorType:=adOpenStatic, _ LockType:=adLockReadOnly

Once the recordset has been created, you can work with it much like a DAO recordset, though there are some differences — see the sections on ADO recordset cursor and lock types for details on the differences.

For further information on converting DAO code to ADO code see Alyssa Henry's article "Porting DAO Code to ADO with the Microsoft Jet Provider," which is available MSDN Library by searching its title or at http://msdn.microsoft.com/library/ default.asp?url=/library/en-us/dndao/html/daotoado.asp.

online in the online in the

0 0

Post a comment