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 cn = Application.CurrentProject.Connection |
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 : | Source : |
Type : | Type : |
Locking : | Locking : |
Options : | Options : |
Property: | Property: |
Methods : | Methods : |
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: | Property: |
Methods : | Methods : |
Dim Pa as Parameter | Dim Pa as New ADODB.Parameter |
Qd.Parameters(Name) = value | Set Pa = Cm.CreateParameter (Name, Type, Direction) Ou Cm.Parameters.Refresh |
Property: | Property: |
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.