From DAO to ADO

When you migrate Access data to SQL Server, you have two choices with Access 2000 and only one with other versions. With previous versions of Access, you will link SQL Server tables, using ODBC, to your Access database. With Access 2000, you can either link the table using ODBC or decide to take profit of the new Access Project file (.adp) using OLE-DB. In the link option, your Access-VBA code remains in DAO, but need to be adapted to the newly ODBC-linked tables. In the project option, you DAO code needs to be modified in ADO.


Adapting DAO Access code to use SQL Server linked tables


There is generally not so many modification to implement with linked tables, except those due to performance problems (I will deal with these in a forthcoming article).


The table below gives you the few Access syntax that will not work with SQL Server linked tables and that you have to modify.
















Access (syntax to modify)


SQL Server


dbOpenTable


dbOpenDynaset, dbOpenDynamic ou dbOpenSnapshot


Seek, method


Use a SELECT query with a WHERE condition in the OpenRecordset method


Updatable, property


May have adifferent value with a SQL Server and an Access table. Check that your SQL Server have a Primary Key or a Unique index to be able to update data.

Adapting DAO Access code to DAO


The DAO and ADO Object models are totally different and, of course, incompatible.



Figure 1 DAO Object model


 



Figure 2 ADO Object model


The following table gives you the DAO and ADO objects and collections equivalent, when it exists.

















































DAO (for Jet)


ADO


DBEngine


N/A


Errors


Errors


Workspaces


N/A


Databases


Connections


Users


N/A


Groups


N/A


Containers


N/A


QueryDefs


Commands


Recordsets


Recordsets


Relation


N/A


TableDefs


N/A


Fields


Fields


Parameters


Parameters


Documents


N/A

Database and connection


The following table gives you the modification to your database objects references, to transform them to ADO connections.













DAO (for Jet)


ADO


Dim Db As Database


Dim Cn as New ADODB.Connection


Set Db = DBEngine(0)(0)

Set Db = OpenDatabase(Name, Options, ReadOnly, Connection)


Set cn = Application.CurrentProject.Connection

Cn.Open ConnectionString, IDUser, Password, Options

Recordset


The recordsets are the basis of reading data from a table, a view, a query or a stored procedure. Even if the objects are named the same way in ADO and DAO, they greatly differ. The following table gives you all the code differences. You must pay attention to the fact that the default values for opening a recordset are different between DAO and ADO. You will probably be surprised if you do not remember it…































DAO (for Jet)


ADO


Dim Rs as Recordset


Dim Rs as New ADODB.Recordset


Set Rs = Db.OpenRecordset(Source, Type, Option, Locking)


Rs.Open Source, Connection, Type, Locking, Option


Source :

Table Name

Query Name

SQL query

Proc. Name and dbSQLPassThrough option


Source :

Table Name and adCmdTable option

View Name and adCmdTable option

SQL Query

Stored Procedure Name


Type :

dbOpenTable (default)

dbOpenDynaset

dbOpenSnapshot

dbOpenForwardOnly

N/A


Type :

adCmdTable option

adOpenKeySet

adOpenStatic

adOpenForwardOnly (default)

adOpenDynamic


Locking :

dbReadOnly

dbPessimistic (default)

dbOptimistic

N/A


Locking :

adLockReadOnly (default)

adLockPessimistic

adLockOptimistic

adLockBatchOptimistic


Options :

dbAppendOnly

dbSQLPassThrough

dbSeeChanges

dbDenyWrite

dbDenyRead

dbForwardOnly

dbReadOnly

dbInconsistent

dbConsistent

Type different than dbOpenTable

dbOpenTable Type

dbOpenTable Type

dbSQLPassThrough with Stored Proc.

N/A

N/A

N/A

N/A

N/A


Options :

N/A

by default with SQL Pass Through

N/A

Connection object mode to adModeShareDenyWrite

Connection object mode to adModeShareDenyRead

adForwardOnly Type

Connection object mode to adModeRead

N/A

N/A

adCmdText

adCmdTable

adCmdTableDirect

adCmdStoredProc

adCmdUnknown

adCmdFile

adAsyncExecute

adAsyncFetch

adAsyncFetchNonBlocking


Property:

AbsolutePosition

BOF, EOF

Bookmark

Bookmarkable

DateCreated, DateUpdated

Filter

Index

LastModified

LockEdits

Name

NoMatch

PercentPosition

RecordCount

Restartable

Sort

Transactions

Type

Updatable

ValidationRule

ValidationText


Property:

AbsolutePosition

BOF, EOF

Bookmark

ADO Recordset are always Bookmarkable

N/A

Filter

Index

N/A

LockType

N/A

EOF

To calculate (AbsolutePosition/RecordCount)

RecordCount

ADO Recordset are always Restartable

Sort

Transactions Property of Connection Object

CursorType

Updatable Property of Connection Object

N/A

N/A


Methods :

AddNew

Cancel

CancelUpdate

Clone

Close

CopyQueryDef

Delete

Edit

FindFirst, FindLast, FindNext, FindPreviou

GetRows

Move

MoveFirst, MoveLast, FindNext, FindPrevious

NextRecordset

OpenRecordset

Requery

Seek

Update


Methods :

AddNew

Cancel

CancelUpdate

Clone

Set Rs = Nothing

N/A

Delete

A Recordset is updatable if the connection allows ir

Seek with option (adSeekFirstEQ, adSeekLastEQ…)

N/A

Move

MoveFirst, MoveLast, FindNext, FindPrevious

NextRecordset

Open

Requery, Resync

Seek

Update

Command and QueryDef


Using QueryDefs in Access permits the reexecution of queries and the use of parameters. The ADO equivalent is the Command. Here’s how to move from QueryDefs to Commands.




























DAO (for Jet)


ADO


Dim Qd As QueryDef


Dim Cm as New ADODB.Command


Set Qd = Db.QueryDefs(“MyQuery”)


Cm.CommandText = “SQL Query or Stored Procedure”


Property:

Connect

DateCreated, DateUpdated

LogMessages

Name

RecordsAffected

Replicable

ReturnsRecord

SQL

Type

Updatable


Property:

ActiveConnection

N/A

N/A

Name

N/A

N/A

Some values of CommandType

CommandText

CommandType

Updatable Property of Connection Object


Methods :

Cancel

Close

CreateProperty

Execute

OpenRecordset


Methods :

Cancel

Set Cm = Nothing

N/A

Execute

Open Method of a Recordset Object


Dim Pa as Parameter


Dim Pa as New ADODB.Parameter


Qd.Parameters(Name) = value


Set Pa = Cm.CreateParameter (Name, Type, Direction)

Pa.Value = value


Ou


Cm.Parameters.Refresh

Cm.Parameters(1).Value = value


Property:

Direction

Name

Type

Value


Property:

Direction

Name

Type

Value

In the next article, I will give you some examples of code transformation to speed up your migration and enhance the performance of your application.

Latest Articles