Alyssa Henry
Microsoft Corporation
July 1999
Summary: Provides a guide to changing code that uses Microsoft® Data Access Objects (DAO) into code that uses Microsoft ActiveX® Data Objects (ADO). (25 printed pages) Also discusses authoring new applications using ADO with the OLE DB Provider for Microsoft Jet.
Note This article is an abridged version of the complete technical article, "Migrating from DAO to ADO: Porting DAO Code to ADO with the Microsoft Jet Provider," available in the MSDN Online Library.
Introduction
General Differences
Getting Started
Opening a Database
Retrieving and Modifying Data
Conclusion
This article is a guide to changing code that uses Microsoft Data Access Objects (DAO) into code that uses Microsoft ActiveX Data Objects (ADO). It also guides those who are writing new code using ADO with the OLE DB Provider for Microsoft Jet (Microsoft Jet Provider). It compares the general differences between DAO and ADO, and it details the mapping of objects, properties, and methods from DAO to ADO. It also highlights areas where there are functional or semantic differences between similarly named methods or properties.
This article assumes that you have already decided to use ADO instead of DAO to access a Microsoft Jet database, or that you are investigating what it will take to port your code from DAO to ADO. It covers some of the advantages of using ADO over DAO. It describes many features of the Microsoft Jet Provider and demonstrates how to use them with ADO. It does not attempt to provide in-depth information on particular objects, properties, or methods. Refer to the online documentation provided with DAO and ADO for specific details on a particular item.
Three distinct object models in ADO together provide the functionality found in DAO. These three models are ADO, Microsoft ADO Extensions for DDL and Security (ADOX), and Microsoft Jet and Replication Objects (JRO). The functionality of DAO was divided among these three models because many applications will need just one of these subsets of functionality. By splitting the functionality out, applications do not need to incur the overhead of loading additional information into memory unnecessarily. The following sections provide an overview of these three object models.
ADO enables your client applications to access and manipulate data through any OLE DB provider. ADO contains objects for connecting to a data source and reading, adding, updating, or deleting data.
Figure 1. ADO object model
The ADO Connection object defines a session for a user for a data source. This differs from DAO, in which the Workspace object defines the session for a user and the data source is defined by the Database object.
The ADO Command object is similar to the DAO QueryDef object in that both objects can be used to execute an SQL statement against a data source. Likewise, the Recordset object in both ADO and DAO can be used to view the contents of a table or the results from executing an SQL statement.
The ADOX model contains objects for data definition (such as tables, views, and indexes) and creating and modifying users and groups. With ADOX, an administrator can control database schema and grant and revoke permissions on objects to users and groups.
Figure 2. ADOX object model
The Catalog object is the container for the data definition collections (Tables, Procedures, and Views) and the security collections (Users and Groups). This differs from DAO, in which the Database object is the container for the data definition collections and the Workspace object contains the security collections. Each Catalog object is associated with only one Connection, whereas a DAO Workspace may contain multiple Databases.
The Table, Index, and Column objects in ADO are roughly equivalent to the TableDef, IndexDef, and Field objects in DAO. Each of these objects also has a standard ADO Properties collection.
Figure 3. ADOX object model
The JRO model contains objects, properties, and methods for creating, modifying, and synchronizing replicas. It is designed specifically for use with the Microsoft Jet Provider. Unlike ADO and ADOX, JRO cannot be used with data sources other than Microsoft Jet databases.
The primary object in the JRO model is the Replica object. The Replica object is used to create new replicas, to retrieve and modify properties of an existing replica, and to synchronize changes with other replicas. This differs from DAO, in which the Database object is used for these tasks.
JRO also includes a JetEngine object for two specific Microsoft Jet database engine features: compacting the database and refreshing data from the memory cache.
Figure 4. JRO object model
To run the code examples in this document, you need references to the ADO, ADOX, and JRO type libraries in your database or project. By default, new Microsoft Access 2000 databases have a reference to ADO. However, to run these samples you'll need to add references to ADOX and JRO. If you converted an existing database to Access 2000 or are programming in Microsoft Visual Basic® or some other application, you will need to include all of the references yourself.
To add these references in Visual Basic
If you include references to both ADO and DAO in the same project, you need to explicitly specify which library to use when declaring objects because DAO and ADO include several objects with the same names. For example, both models include a Recordset object, so the following code is ambiguous:
Dim rst as Recordset
To specify which object model you want to use, include a qualifier as shown:
Dim rstADO As ADODB.Recordset
Dim rstDAO As DAO.Recordset
If the qualifier is omitted, Visual Basic for Applications (VBA) will choose the object from the model that is referenced first. So, if your list of references is ordered as follows in the References dialog box, an object declared as Recordset with no qualifier would be a DAO Recordset:
Visual Basic for Applications
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
Microsoft ADO Ext. 2.1 for DDL and Security
Microsoft Jet and Replication Objects 2.1 Library
Generally, one of the first steps in writing an application to access data is to open the data source. When using the Microsoft Jet database engine, you can open Microsoft Jet databases, other external data sources such as Microsoft Excel, Paradox, and dBASE with Microsoft Jet's ISAM components, and ODBC data sources.
The Microsoft Jet Provider can open Microsoft Jet 4.0 databases as well as databases created with previous versions of the Microsoft Jet database engine. These examples use only Microsoft Jet 4.0 databases.
The following code demonstrates how to open a Microsoft Jet database for shared, updatable access. Then the code immediately closes the database because this code is for demonstration purposes.
DAO
Sub DAOOpenJetDatabase()
Dim db As DAO.Database
Set db = DBEngine.OpenDatabase _
(".\NorthWind.mdb")
db.Close
End Sub
ADO
Sub ADOOpenJetDatabase()
Dim cnn As New ADODB.Connection
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=.\NorthWind.mdb;"
cnn.Close
End Sub
These two code listings for opening a database look somewhat different, but are not all that dissimilar. Aside from the fact that the objects have different names, the major difference is the format of the string passed to the method that opens the database.
The ADO connection string in this example has two parts: the provider tag and the data source tag. The provider tag indicates which OLE DB provider to use, and the data source tag indicates which database to open. With DAO it is assumed that you want to use Microsoft Jet, whereas with ADO you must explicitly specify that you want to use Microsoft Jet.
By default, both DAO and ADO open a database for shared updatable access when using the Microsoft Jet Provider. However, there may be times when you want to open the database exclusively or in read-only mode.
The following code listings show how to open (and then close) a shared, read-only database using DAO and ADO.
DAO
Sub DAOOpenJetDatabaseReadOnly()
Dim db As DAO.Database
' Open shared, read-only.
Set db = DBEngine.OpenDatabase _
(".\NorthWind.mdb", False, True)
db.Close
End SubADO
Sub ADOOpenJetDatabaseReadOnly()
Dim cnn As New ADODB.Connection
' Open shared, read-only
cnn.Mode = adModeRead
cnn.Open "Provider=" & _
"Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
cnn.Close
End Sub
In the DAO listing the second two parameters to the OpenDatabase method indicate exclusive and read-only access respectively. In the ADO listing the Connection object's Mode property is set to the read-only constant (adModeRead). By default, ADO connections are opened for shared, updatable access unless another mode is set (for example, adModeShareExclusive).
Microsoft Jet databases can be secured in one of two ways: with share-level security or with user-level security. With share-level security the database is secured with a password. Anyone attempting to open the database must specify the correct database password. With user-level security each user is assigned a user name and password to open the database. Microsoft Jet uses a separate workgroup information file, typically named System.mdw, to store user information and passwords.
The following listings demonstrate how to open a Microsoft Jet database that has been secured at the share level.
DAO
Sub DAOOpenDBPasswordDatabase()
Dim db As DAO.Database
Set db = DBEngine.OpenDatabase _
(".\NorthWind.mdb", False, False, _
";pwd=password")
db.Close
End Sub
ADO
Sub ADOOpenDBPasswordDatabase()
Dim cnn As New ADODB.Connection
cnn.Open "Provider=" & _
"Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;" & _
"Jet OLEDB:Database Password=" & _
"password;"
cnn.Close
End Sub
In DAO the Connect parameter of the OpenDatabase method sets the database password when opening a database. With ADO the Microsoft Jet Provider connection property Jet OLEDB:Database Password sets the password instead.
This next listings demonstrate how to open a database that is secured at the user level using a workgroup information file named Sysdb.mdw.
DAO
Sub DAOOpenSecuredDatabase()
Dim wks As DAO.Workspace
Dim db As DAO.Database
DBEngine.SystemDB = _
"C:\Program Files\Microsoft Office" _
& "\Office\SYSTEM.MDW"
Set wks = DBEngine.CreateWorkspace _
("", "Admin", "")
Set db = wks.OpenDatabase _
(".\NorthWind.mdb")
db.Close
wks.Close
End Sub
ADO
Sub ADOOpenSecuredDatabase()
Dim cnn As New ADODB.Connection
cnn.Provider = _
"Microsoft.Jet.OLEDB.4.0;"
cnn.Properties _
("Jet OLEDB:System database") = _
"C:\Program Files\Microsoft Office" _
& "\Office\SYSTEM.MDW"
cnn.Open _
"Data Source=.\NorthWind.mdb;" & _
"User Id=Admin;Password=;"
cnn.Close
End Sub
In ADO a Microsoft Jet provider-specific connection property, Jet OLEDB:System database, specifies the system database. This is equivalent to setting the DBEngine object's SystemDB property before opening a database using DAO.
Notice that in this example the Provider property is set as a property of the Connection object rather than as part of the ConnectionString argument to the Open method. That is because before you can reference provider-specific properties from the Connection object's Properties collection, it is necessary to indicate which provider you are using. If the first line of code had been omitted, error 3265 (adErrItemNotFound), "ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application," would have occurred when trying to set the value for the Jet OLEDB:System database property.
Both DAO and ADO include a Recordset object that is the primary object used for retrieving and modifying data. A Recordset object represents a set of records in a table or a set of records that are a result of a query.
The Recordset object contains a Fields collection that contains Field objects, each of which represent a single column of data within the Recordset.
Like DAO, ADO Recordset objects can be opened from several different objects. In ADO a Recordset can be opened with the Connection object Execute method, the Command object Execute method, or the Recordset object Open method. ADO Recordset objects cannot be opened directly from Table, Procedure, or View objects. ADO Recordset objects opened with the Execute method are always forward-only, read-only Recordsets. If you need to be able to scroll or update data within the Recordset you must use the Recordset object Open method.
The CursorType, LockType, and Options parameters of the Open method determine the type of Recordset that is returned. The following table shows how the parameters to the DAO Recordset object Open method can be mapped to ADO properties.
| DAO Recordset type | ADO Recordset properties or parameters |
| dbOpenDynaset | CursorType=adOpenKeyset |
| dbOpenSnapshot | CursorType=adOpenStatic |
| dbOpenForwardOnly | CursorType=adOpenForwardOnly |
| dbOpenTable | CursorType=adOpenKeyset, Options=adCmdTableDirect |
| DAO Recordset Options values | ADO Recordset properties |
| dbAppendOnly | Properties("Append-Only Rowset") |
| dbSQLPassThrough | Properties("Jet OLEDB:ODBC Pass-Through Statement") |
| dbSeeChanges | No equivalent |
| dbDenyWrite | No equivalent |
| dbDenyRead | No equivalent |
| dbInconsistent | Properties("Jet OLEDB:Inconsistent") = True |
| dbConsistent | Properties("Jet OLEDB:Inconsistent") = False |
| DAO Recordset LockType values | ADO Recordset LockType values |
| dbReadOnly | adLockReadOnly |
| dbPessimistic | adLockPessimistic |
| dbOptimistic | adLockOptimistic |
The Microsoft Jet Provider does not support a number of combinations of CursorType and LockType—for example, CursorType=adOpenDynamic and LockType=adLockOptimistic. If you specify an unsupported combination, ADO will pass your request to the Microsoft Jet Provider, which will then degrade to a supported CursorType or LockType. Use the CursorType and LockType properties of the Recordset once it is opened to determine what type of Recordset was created.
The following listings demonstrate how to open a forward-only, read-only Recordset, and then print the values of each field.
DAO
Sub DAOOpenRecordset()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
'Open the database
Set db = DBEngine.OpenDatabase _
(".\NorthWind.mdb")
'Open the Recordset
Set rst = db.OpenRecordset _
("SELECT * FROM Customers " & _
"WHERE Region = 'WA'", _
dbOpenForwardOnly, dbReadOnly)
' Print the values for the fields in
' the first record in the debug window
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
'Close the recordset
rst.Close
End Sub
ADO
Sub ADOOpenRecordset()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
' Open the connection
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=.\NorthWind.mdb;"
' Open the forward-only,
' read-only recordset
rst.Open "SELECT * FROM Customers " & _
"WHERE Region = 'WA'", cnn, _
adOpenForwardOnly, adLockReadOnly
' Print the values for the fields in
' the first record in the debug window
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
'Close the recordset
rst.Close
End Sub
In the preceding DAO and ADO code, the Recordset is opened and then the data in the first record of the Recordset is printed to the Debug window by iterating through each field in the Fields collection and printing its Value.
A Recordset object has a current position. The position may be before the first record (BOF), after the last record (EOF), or on a specific record within the Recordset. When retrieving information with the Field object, the information always pertains to the record at the current position.
Both DAO and ADO contain several methods for moving from one record to another. These methods are Move, MoveFirst, MoveLast, MoveNext, and MovePrevious.
The following listings demonstrate how to use the MoveNext method to iterate through all of the records in the Recordset.
DAO
Sub DAOMoveNext()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
'Open the database
Set db = DBEngine.OpenDatabase _
(".\NorthWind.mdb")
'Open the Recordset
Set rst = db.OpenRecordset _
("SELECT * FROM Customers " & _
"WHERE Region = 'WA'", _
dbOpenForwardOnly, dbReadOnly)
' Print the values for the fields in
' the first record in the debug window
While Not rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Wend
'Close the recordset
rst.Close
End Sub
ADO
Sub ADOMoveNext()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
' Open the connection
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=.\NorthWind.mdb;"
' Open the forward-only,
' read-only recordset
rst.Open "SELECT * FROM Customers " & _
"WHERE Region = 'WA'", cnn, _
adOpenForwardOnly, adLockReadOnly
' Print the values for the fields in
' the first record in the debug window
While Not rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Wend
'Close the recordset
rst.Close
End Sub
Notice that the code for iterating through the Recordset in DAO and ADO is identical.
In the previous example, the ADO code could be rewritten to use the Recordset object's GetString method to print the data to the Debug window. This method returns a formatted string containing data from the records in the Recordset. Using GetString, the While loop in the previous ADO example could be replaced with the single line:
Debug.Print rst.GetString _
(adClipString, , ";")
This method is handy for debugging as well as populating grids and other controls that allow you to pass in a formatted string representing the data. GetString is also faster than looping through the Recordset and generating the string with Visual Basic for Applications code.
The ADO example could also have been rewritten more concisely by using the Recordset object's Open method's ActiveConnection parameter to specify the connection string rather than first opening a Connection object and then passing that object in as the ActiveConnection. The Recordset object's Open method call would look like this:
rst.Open "SELECT * FROM Customers " & _
"WHERE Region = 'WA'", _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=.\NorthWind.mdb;", _
adOpenForwardOnly, adLockReadOnly
Internally, these two mechanisms are essentially the same. When you pass a connection string to the Recordset object's Open method (rather than assigning a Connection object to the Recordset object's ActiveConnection property), ADO creates a new, internal Connection object. If you plan on opening more than one Recordset from a given data source, or opening Command or Catalog objects, create a Connection object and use that object for the ActiveConnection. This will reduce the amount of resources consumed and increase performance.
Once you have opened an updatable recordset by specifying the appropriate DAO Recordset object Type or ADO CursorType and LockType, you can change, delete, or add new records using methods of the Recordset object.
Both DAO and ADO allow you to add new records to an updatable Recordset by first calling the AddNew method, specifying the values for the fields, and finally committing the changes with the Update method. The following code shows how to add a new record using DAO and ADO.
DAO
Sub DAOAddRecord()
Dim db As DAO.Database
Dim rst As DAO.Recordset
'Open the database
Set db = DBEngine.OpenDatabase _
(".\NorthWind.mdb")
'Open the Recordset
Set rst = db.OpenRecordset _
("SELECT * FROM Customers", _
dbOpenDynaset)
' Add a new record
rst.AddNew
' Specify the values for the fields
rst!CustomerId = "HENRY"
rst!CompanyName = "Henry's Chop House"
rst!ContactName = "Mark Henry"
rst!ContactTitle = _
"Sales Representative"
rst!Address = "40178 NE 8th Street"
rst!City = "Bellevue"
rst!Region = "WA"
rst!PostalCode = "98107"
rst!Country = "USA"
rst!Phone = "(425) 899-9876"
rst!Fax = "(425) 898-8908"
' Save the changes you made to the
' current record in the Recordset
rst.Update
' For this example, just print out
' CustomerId for the new record
' Position recordset on new record
rst.Bookmark = rst.LastModified
Debug.Print rst!CustomerId
'Close the recordset
rst.Close
End Sub
ADO
Sub ADOAddRecord()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
' Open the connection
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=.\NorthWind.mdb;"
' Open the recordset
rst.Open "SELECT * FROM Customers", _
cnn, adOpenKeyset, adLockOptimistic
' Add a new record
rst.AddNew
' Specify the values for the fields
rst!CustomerId = "HENRY"
rst!CompanyName = "Henry's Chop House"
rst!ContactName = "Mark Henry"
rst!ContactTitle = _
"Sales Representative"
rst!Address = "40178 NE 8th Street"
rst!City = "Bellevue"
rst!Region = "WA"
rst!PostalCode = "98107"
rst!Country = "USA"
rst!Phone = "(425) 555-9876"
rst!Fax = "(425) 555-8908"
' Save the changes you made to the
' current record in the Recordset
rst.Update
' For this example, just print out
' CustomerId for the new record
Debug.Print rst!CustomerId
'Close the recordset
rst.Close
End Sub
DAO and ADO behave differently when a new record is added. With DAO the record that was current before you used AddNew remains current. With ADO the newly inserted record becomes the current record. Because of this, it is not necessary to explicitly reposition on the new record to get information such as the value of an auto-increment column for the new record. For this reason, in the preceding ADO example, there is no equivalent code to the rst.Bookmark = rst.LastModified code found in the DAO example.
ADO also provides a shortcut syntax for adding new records. The AddNew method has two optional parameters, FieldList and Values, that take an array of field names and field values respectively. The following example demonstrates how to use the shortcut syntax.
Sub ADOAddRecord2()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
' Open the connection
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=.\NorthWind.mdb;"
' Open the recordset
rst.Open "SELECT * FROM Shippers", _
cnn, adOpenKeyset, adLockOptimistic
' Add a new record
rst.AddNew Array("CompanyName", _
"Phone"), Array("World Express", _
"(425) 555-7863")
' Save the changes you made to the
' current record in the Recordset
rst.Update
' For this example, just print out the
' ShipperId for the new row.
Debug.Print rst!ShipperId
'Close the recordset
rst.Close
End Sub
The following code demonstrates how to open a scrollable, updatable Recordset and modify the data in a record.
DAO
Sub DAOUpdateRecord()
Dim db As DAO.Database
Dim rst As DAO.Recordset
'Open the database
Set db = DBEngine.OpenDatabase _
(".\NorthWind.mdb")
'Open the Recordset
Set rst = db.OpenRecordset _
("SELECT * FROM Customers " & _
"WHERE CustomerId = 'LAZYK'", _
dbOpenDynaset)
' Put the Recordset in Edit Mode
rst.Edit
' Update the Contact name of the
' first record
rst.Fields("ContactName").Value = _
"New Name"
' Save the changes you made to the
' current record in the Recordset
rst.Update
'Close the recordset
rst.Close
End Sub
ADO
Sub ADOUpdateRecord()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
' Open the connection
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=.\NorthWind.mdb;"
' Open the recordset
rst.Open "SELECT * FROM Customers " & _
"WHERE CustomerId = 'LAZYK'", _
cnn, adOpenKeyset, adLockOptimistic
' Update the Contact name of the
' first record
rst.Fields("ContactName").Value = _
"New Name"
' Save the changes you made to the
' current record in the Recordset
rst.Update
'Close the recordset
rst.Close
End SubAlternatively, in both the DAO and ADO code examples, the explicit syntax:
rst.Fields("ContactName").Value = _
"New Name"
can be shortened to:
rst!ContactName = "New Name"
The ADO and DAO code for updating data in a Recordset is very similar. The major difference between the two preceding examples is that DAO requires you to put the Recordset into an editable state with the Edit method. ADO does not require you to explicitly indicate that you want to be in edit mode. With both DAO and ADO you can verify the edit status of the current record by using the EditMode property.
One difference between DAO and ADO is the behavior when updating a record and then moving to another record without calling the Update method. With DAO any changes made to the current record are lost when moving to another record without first calling Update. ADO automatically commits the changes to the current record when moving to a new record. You can explicitly discard changes to the current record with both DAO and ADO by using the CancelUpdate method.
You can execute a query with ADO as easily as you can with DAO. ADO uses a slightly different technique for working with queries, but provides more functionality than DAO provides. You'll also write less code with ADO to do the same thing as you would with DAO.
A nonparameterized stored query is an SQL statement that has been saved in the database and does not require that additional variable information be specified in order to execute. The following listings demonstrate how to execute such a query.
DAO
Sub DAOExecuteQuery()
If gbBreakEach Then Stop
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
'Open the database
Set db = DBEngine.OpenDatabase _
(".\NorthWind.mdb")
'Open the Recordset
Set rst = db.OpenRecordset _
("Products Above Average Price", _
dbOpenForwardOnly, dbReadOnly)
' Display the records in the
' debug window
While Not rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Wend
'Close the recordset
rst.Close
End Sub
ADO
Sub ADOExecuteQuery()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
' Open the connection
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=.\NorthWind.mdb;"
' Open the recordset
rst.Open _
"[Products Above Average Price]", _
cnn, adOpenForwardOnly, _
adLockReadOnly, adCmdStoredProc
' Display the records in the
' debug window
While Not rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Wend
'Close the recordset
rst.Close
End Sub
The code for executing a nonparameterized, row-returning query is almost identical. With ADO, if the query name contains spaces you must use square brackets ([ ]) around the name.
A parameterized stored query is an SQL statement that has been saved in the database and requires that additional variable information be specified in order to execute. The following code shows how to execute such a query.
DAO
Sub DAOExecuteParamQuery()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim fld As DAO.Field
' Open the database
Set db = DBEngine.OpenDatabase _
(".\NorthWind.mdb")
' Get the QueryDef from the
' QueryDefs collection
Set qdf = db.QueryDefs("Sales by Year")
' Specify the parameter values
qdf.Parameters _
("Forms!Sales by Year Dialog!" & _
"BeginningDate") = #8/1/1997#
qdf.Parameters _
("Forms!Sales by Year Dialog!" & _
"EndingDate") = #8/31/1997#
' Open the Recordset
Set rst = qdf.OpenRecordset _
(dbOpenForwardOnly, dbReadOnly)
' Display the records in the
' debug window
While Not rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Wend
'Close the recordset
rst.Close
End Sub
ADO
Sub ADOExecuteParamQuery()
Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
' Open the connection
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=.\NorthWind.mdb;"
' Open the catalog
cat.ActiveConnection = cnn
' Get the Command object from the
' Procedure
Set cmd = cat.Procedures _
("Sales by Year").Command
' Specify the parameter values
cmd.Parameters _
("Forms![Sales by Year Dialog]!" & _
"BeginningDate") = #8/1/1997#
cmd.Parameters _
("Forms![Sales by Year Dialog]!" & _
"EndingDate") = #8/31/1997#
' Open the recordset
rst.Open cmd, , adOpenForwardOnly, _
adLockReadOnly, adCmdStoredProc
' Display the records in the
' debug window
While Not rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Wend
'Close the recordset
rst.Close
End Sub
In one more variation of the ADO code to execute a parameterized query, the example could be rewritten to not use any ADOX code.
Sub ADOExecuteParamQuery2()
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
' Open the connection
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=.\NorthWind.mdb;"
' Create the command
Set cmd.ActiveConnection = cnn
cmd.CommandText = "[Sales by Year]"
' Execute the Command, passing in the
' values for the parameters
Set rst = cmd.Execute(, _
Array(#8/1/1997#, #8/31/1997#), _
adCmdStoredProc)
' Display the records in the
' debug window
While Not rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Wend
'Close the recordset
rst.Close
End Sub
The complete technical article, "Migrating from DAO to ADO: Using ADO with the Microsoft Jet Provider," covers many subjects that are not covered in this article. It covers additional data management topics, such as opening and linking ISAM and ODBC databases; getting the current position; seeking, finding, filtering, and sorting records; and executing bulk operations. Beyond that, it covers data definition topics such as defining and retrieving the schema of a database, including creating and modifying tables, indexes, keys, relationships, and queries. It also discusses how to work with Microsoft Jet security, database replication, error handlers, transactions, and some features specific to Jet. Finally, it highlights some new features in ADO that are not available in DAO. Several appendices are included for reference.
ADO is designed to make the process of migrating from DAO simple. In the past, DAO made working with tabular databases easier than calling dynamic-link library (DLL) functions by wrapping up those functions with objects. This made data access relatively simple for most developers. ADO uses this strength of DAO and expands on it. ADO uses OLE DB to work with data sources, so you will be able to reach many more data sources than with DAO. Many of these data sources won't be stored as traditional, tabular databases. In those cases, using DAO won't be possible. ADO is similar enough to DAO that you won't need to relearn an entire object model to handle these new data sources. Once you've learned ADO, you're ready for the future of data access.