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.
