From DAO to ADO

July 5, 2000

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.









The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers