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 represents 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=adOpenDynamic |
| dbOpenSnapshot | CursorType=adOpenStatic |
| dbOpenForwardOnly | CursorType=adOpenForwardOnly |
| dbOpenTable | CursorType=adOpenDynamic, Options=adCmdTableDirect |
| DAO Recordset Options values |
ADO Recordset properties |
| dbAppendOnly | Properties("Append-Only Rowset") |
| dbSQLPassThrough | Properties("Jet OLEDB:ODBC Pass-Through Statement") |
| dbSeeChanges | Not currently supported. |
| dbDenyWrite | Not currently supported. |
| dbDenyRead | Not currently supported. |
| 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 prints 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.
ADO Recordset objects have an additional property, CursorLocation, not found in DAO, which affects the functionality and performance of the Recordset. This property has two valid values: adUseServer and adUseClient. The default is adUseServer, which indicates that the provider's or data source's cursors should be used.
When the CursorLocation property is set to adUseClient, ADO will invoke the Microsoft Cursor Service for OLE DB to create the Recordset. The Cursor Service retrieves data from the underlying data provider using a forward-only, read-only cursor and stores all of the data in its own cache on the client. When data is requested through ADO, the Cursor Service returns the data from its own cache rather than passing the request down to the provider. This often results in fairly significant performance gains when the underlying data source is on a remote server as is often the case with Microsoft SQL Server™. However, when the data is stored in a local Microsoft Jet database, this can result in fairly significant performance degradation as the data is being cached twice on the client, once in Microsoft Jet and once in the Cursor Service.
While there may be a performance penalty for using the Cursor Service, it does provide some functionality found in DAO that is not currently exposed in the Microsoft Jet Provider. For example, adUseClient must be specified for CursorLocation in order to sort an existing Recordset. (See the section "Filtering and Sorting Data in a Recordset" for more information about how to use the Cursor Service to sort a Recordset.)
When developing your application, you'll generally want to specify adUseServer as the CursorLocation to get performance and functionality similar to DAO. However, in the few cases where the Microsoft Jet Provider does not provide the functionality needed, consider using client cursors.
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
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' 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
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' 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.
When working with records in a Recordset it may be useful to know what the record number of the current record is. Both ADO and DAO have an AbsolutePosition property that can be used to determine the record number. The following code listings demonstrate how to use the AbsolutePosition property in both DAO and ADO.
DAO
Sub DAOGetCurrentPosition()
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)
' Print the absolute position
Debug.Print rst.AbsolutePosition
' Move to the last record
rst.MoveLast
' Print the absolute position
Debug.Print rst.AbsolutePosition
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOGetCurrentPosition()
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.CursorLocation = adUseClient
rst.Open "SELECT * FROM Customers", cnn, adOpenKeyset, _
adLockOptimistic, adCmdText
' Print the absolute position
Debug.Print rst.AbsolutePosition
' Move to the last record
rst.MoveLast
' Print the absolute position
Debug.Print rst.AbsolutePosition
' Close the recordset
rst.Close
End Sub
The ADO and DAO code for determining the current position within the Recordset looks very similar. However, note that the results printed to the Debug window are different. With DAO the AbsolutePosition property is zero-based; the first record in the Recordset has an AbsolutePosition of 0. With ADO the AbsolutePosition property is one-based; the first record in the Recordset has an AbsolutePosition of 1.
Note that in the previous ADO code example the CursorLocation property is set to adUseClient. If the CursorLocation is not specified or is set to adUseServer, the AbsolutePosition property will return adUnknown (-1) because the Microsoft Jet Provider does not support retrieving this information. See the section "Using Client Cursors" for more information about using the CursorLocation property.
In addition to the AbsolutePosition property, DAO also has a PercentPosition property that returns a percentage representing the approximate position of the current record within the Recordset. ADO does not have a property or method that provides the functionality equivalent to DAO's PercentPosition property.
Both DAO and ADO have two mechanisms for locating a record in a Recordset: Find and Seek. With both mechanisms you specify criteria to use to locate a matching record. In general, for equivalent types of searches, Seek provides better performance than Find. However, because Seek uses an underlying index to locate the record, it is limited to Recordset objects that have associated indexes. For Microsoft Jet databases only, Recordset objects based on a table (DAO dbOpenTable, ADO adCmdTableDirect) with an index support Seek.
The following listings demonstrate how to locate a record using Find.
DAO
Sub DAOFindRecord()
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("Customers", dbOpenDynaset)
' Find the first customer whose country is USA
rst.FindFirst "Country = 'USA'"
' Print the customer id's of all customers in the USA
Do Until rst.NoMatch
Debug.Print rst.Fields("CustomerId").Value
rst.FindNext "Country = 'USA'"
Loop
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOFindRecord()
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 "Customers", cnn, adOpenKeyset, adLockOptimistic
' Find the first customer whose country is USA
rst.Find "Country='USA'"
' Print the customer id's of all customers in the USA
Do Until rst.EOF
Debug.Print rst.Fields("CustomerId").Value
rst.Find "Country='USA'", 1
Loop
' Close the recordset
rst.Close
End Sub
DAO includes four find methods: FindFirst, FindLast, FindNext, and FindPrevious. You choose which method to use based on the point from which you want to start searching (beginning, end, or current record) and in which direction you want to search (forward or backward).
ADO has a single method: Find. Searching always begins from the current record. The Find method has parameters that allow you to specify the search direction as well as an offset from the current record at which to begin searching (SkipRows). The following table shows how to map the four DAO methods to the equivalent functionality in ADO.
| DAO method | ADO Find with SkipRows |
ADO search direction |
| FindFirst | 0 | adSearchForward (if not currently positioned on the first record, call MoveFirst before Find) |
| FindLast | 0 | adSearchBackward (if not currently positioned on the last record, call MoveLast before Find) |
| FindNext | 1 | adSearchForward |
| FindPrevious | 1 | adSearchBackward |
DAO and ADO require a different syntax for locating records based on a Null value. In DAO if you want to find a record that has a Null value you use the following syntax:
"ColumnName Is Null"
or, to find a record that does not have a Null value for that column:
"ColumnName Is Not Null"
ADO, however, does not recognize the Is operator. You must use the = or <> operators instead. So the equivalent ADO criteria would be:
"ColumnName = Null"
or:
"ColumnName <> Null"
So far, each of the criteria shown in the preceding examples are based on a value for a single column. However, with DAO, the Criteria parameter is like the WHERE clause in an SQL statement and can contain multiple columns and compare operators within the criteria.
This is not the case with ADO. The ADO Criteria parameter is a string containing a single column name, comparison operator, and value to use in the search. If you need to find a record based on multiple columns, use the Filter property (see the section "Filtering and Sorting Data in a Recordset") to create a view of the Recordset that only contains those records matching the criteria.
DAO and ADO behave differently if a record that meets the specified criteria is not found. DAO sets the NoMatch property to True and the current record is not defined. If ADO does not find a record that meets the criteria, the current record is positioned either before the beginning of the Recordset if searching forward (adSearchForward) or after the end of the Recordset if searching backward (adSearchBackward). Use the BOF or EOF properties as appropriate to determine whether a match was found.
The following listings demonstrate how to locate a record using Seek.
DAO
Sub DAOSeekRecord()
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("Order Details", dbOpenTable)
' Select the index used to order the data in the recordset
rst.Index = "PrimaryKey"
' Find the order where OrderId = 10255 and ProductId = 16
rst.Seek "=", 10255, 16
' If a match is found print the quantity of the order
If Not rst.NoMatch Then
Debug.Print rst.Fields("Quantity").Value
End If
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOSeekRecord()
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 "Order Details", cnn, adOpenKeyset, adLockReadOnly, _
adCmdTableDirect
' Select the index used to order the data in the recordset
rst.Index = "PrimaryKey"
' Find the order where OrderId = 10255 and ProductId = 16
rst.Seek Array(10255, 16), adSeekFirstEQ
' If a match is found print the quantity of the order
If Not rst.EOF Then
Debug.Print rst.Fields("Quantity").Value
End If
' Close the recordset
rst.Close
End Sub
Because Seek is based on an index, it is important to specify an index before searching. In the previous example, this is not strictly necessary because Microsoft Jet will use the primary key if an index is not specified.
In the ADO example, the Visual Basic for Applications Array function is used when specifying a value for more than one column as part of the KeyValues parameter. If only one value is specified, it is not necessary to use the Array function.
As with the Find method, use the NoMatch property with DAO to determine whether a matching record was found. Use the BOF and EOF properties as appropriate with ADO.
The Seek method will work correctly only for Microsoft Jet 4.0 databases. It will fail with a run-time error for all earlier formats, even if you use the Microsoft Jet 4.0 database engine to open the database. This will cause a problem if the application is written to support older database formats. If so, use the Supports method of the Recordset object to determine whether the Seek method is available for the open Recordset. However, if all client applications use the newer format, this check is unnecessary. Use either Microsoft Access 2000 or the CompactDatabase method to convert older databases to the newer format.
In general, when using ADO with the Microsoft Jet Provider you will have better performance if you filter and sort data by specifying an SQL WHERE or ORDER BY clause in the SQL statement or stored query used to open the Recordset.
The following listings demonstrate how to use the Filter property.
DAO
Sub DAOFilterRecordset()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstFlt As DAO.Recordset
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Open the Recordset
Set rst = db.OpenRecordset("Customers", dbOpenDynaset)
' Set the Filter to be used for subsequent recordsets
rst.Filter = "Country='USA' And Fax Is Not Null"
' Open the filtered recordset
Set rstFlt = rst.OpenRecordset()
Debug.Print rstFlt.Fields("CustomerId").Value
' Close the recordsets
rst.Close
rstFlt.Close
End Sub
ADO
Sub ADOFilterRecordset()
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 "Customers", cnn, adOpenKeyset, adLockOptimistic
' Filter the recordset to include only those customers in
' the USA that have a fax number
rst.Filter = "Country='USA' And Fax <> Null"
Debug.Print rst.Fields("CustomerId").Value
' Close the recordset
rst.Close
End Sub
The DAO and ADO Filter properties are used slightly differently. With DAO the Filter property specifies a filter to be applied to any subsequently opened Recordset objects based on the Recordset for which you have applied the filter. With ADO the Filter property applies to the Recordset to which you applied the filter. The ADO Filter property allows you to create a temporary view that can be used to locate a particular record or set of records within the Recordset. When a filter is applied to the Recordset, the RecordCount property reflects just the number of records within the view. The filter can be removed by setting the Filter property to adFilterNone.
The following listings demonstrate how to sort records with the Sort method.
DAO
Sub DAOSortRecordset()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstSort As DAO.Recordset
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Open the Recordset
Set rst = db.OpenRecordset("Customers", dbOpenDynaset)
' Sort the recordset based on Country and Region both in
' ascending order
rst.Sort = "Country, Region"
' Open the sorted recordset
Set rstSort = rst.OpenRecordset()
Debug.Print rstSort.Fields("CustomerId").Value
' Close the recordsets
rst.Close
rstSort.Close
End Sub
ADO
Sub ADOSortRecordset()
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.CursorLocation = adUseClient
rst.Open "Customers", cnn, adOpenKeyset, adLockOptimistic
' Sort the recordset based on Country and Region both in
' ascending order
rst.Sort = "Country, Region"
Debug.Print rst.Fields("CustomerId").Value
' Close the recordset
rst.Close
End Sub
Like the Filter property, the DAO and ADO Sort properties differ in that the DAO Sort applies to subsequently opened Recordset objects, and for ADO it applies to the current Recordset.
Note that the Microsoft Jet Provider does not support the OLE DB interfaces that ADO could use to filter and sort the Recordset (IViewFilter and IViewSort). In the case of Filter, ADO will perform the filter itself. However, for Sort you must use the Cursor Service by specifying adUseClient for the CursorLocation property prior to opening the Recordset. The Cursor Service will copy all of the records in the Recordset to a cache on your local machine and will build temporary indexes in order to perform the sorting. In many cases, you may achieve better performance by reexecuting the query used to open the Recordset and specifying an SQL WHERE or ORDER BY clause as appropriate.
Also, you may not get identical results with DAO and ADO when sorting Recordset objects. Different sort algorithms can create different sequences for records that have equal values in the sorted fields. In the previous example, the DAO code gives 'RANCH' as the CustomerId for the first record, while the ADO code gives 'CACTU' as the CustomerId. Both results are valid.
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, then 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) 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
' 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 previous 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 Sub
Alternatively, 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 previous 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.
Two kinds of fields in a Microsoft Jet database are used to store lengthy values. Pictures, OLE objects, or whole files can be stored in an OLE object field as binary data. An OLE object field is also called a Binary Large Object (BLOB) field. A Memo field stores text data only. Memo fields are sometimes called BLOB fields, too, but that is somewhat misleading. A Memo field is merely a variable-length Text field. Both fields are handled similarly.
Large variable-length fields like these must be handled differently than ordinary fixed-length fields, such as integer and date fields. Typically, a variant, a string, or a dynamic byte array is used to read or update the value of the field. However, sometimes the length of the data stored in the field will be too large to store at once in a variant. Breaking the data into smaller, manageable chunks reduces the memory that must be allocated to work with these values. The GetChunk and AppendChunk methods of the Field objects in DAO and ADO break up the data in these fields into smaller chunks.
The following listings demonstrate how to use GetChunk to read large values from a Memo field.
DAO
Sub DAOReadMemo()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sNotes As String
Dim sChunk As String
Dim cchChunkReceived As Long
Dim cchChunkRequested As Long
Dim cchChunkOffset As Long
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Open the Recordset
Set rst = db.OpenRecordset _
("SELECT Notes FROM Employees", dbOpenDynaset)
' Initialize offset
cchChunkOffset = 0
' cchChunkRequested artifically set low at 16
' to demonstrate looping
cchChunkRequested = 16
' Loop through as many chunks as it takes
' to read the entire BLOB into memory
Do
' Temporarily store the next chunk
sChunk = rst!Fields("Notes").GetChunk _
(cchChunkOffset, cchChunkRequested)
' Check how much we got
cchChunkReceived = Len(sChunk)
' Adjust offset for next iteration
cchChunkOffset = cchChunkOffset + cchChunkReceived
' If we got anything,
' concatenate it to the main BLOB
If cchChunkReceived > 0 Then
sNotes = sNotes & sChunk
End If
Loop While cchChunkReceived = cchChunkRequested
' For this example, print the value of
' the Notes field for just the first record
Debug.Print sNotes
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOReadMemo()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sNotes As String
Dim sChunk As String
Dim cchChunkReceived As Long
Dim cchChunkRequested As Long
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Open the recordset
rst.Open "SELECT Notes FROM Employees ", _
cnn, adOpenKeyset, adLockOptimistic
' cchChunkRequested artifically set low at 16
' to demonstrate looping
cchChunkRequested = 16
' Loop through as many chunks as it takes
' to read the entire BLOB into memory
Do
' Temporarily store the next chunk
sChunk = rst.Fields("Notes").GetChunk(cchChunkRequested)
' Check how much we got
cchChunkReceived = Len(sChunk)
' If we got anything,
' concatenate it to the main BLOB
If cchChunkReceived > 0 Then
sNotes = sNotes & sChunk
End If
Loop While cchChunkReceived = cchChunkRequested
' For this example, print the value of
' the Notes field for just the first record
Debug.Print sNotes
' Close the recordset
rst.Close
End Sub
The code to use GetChunk and AppendChunk in ADO is similar to the code in DAO. In the DAO example the offset to read the next chunk of data from the field must be explicitly calculated and given as a parameter to the GetChunk method. In ADO the Recordset stores the offset for consecutive GetChunk calls on the same field automatically. Any clones of the Recordset share this offset. This offset is automatically incremented as you call GetChunk consecutively on a single field. Reading from a different field in the Recordset or moving the Recordset to a different record will reset the offset to the beginning of the field.
The following listings demonstrate how to update binary data in an OLE object field without using the GetChunk or AppendChunk methods.
DAO
Sub DAOUpdateBLOB()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rgPhoto() As Byte
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Open the Recordset
Set rst = db.OpenRecordset( _
"SELECT Photo FROM Employees", dbOpenDynaset)
' Get the first photo
rgPhoto = rst.Fields("Photo").Value
' Move to the next record
rst.MoveNext
' Put the Recordset in Edit Mode
rst.Edit
' Copy the photo into the next record
rst.Fields("Photo").Value = rgPhoto
' Save the changes you made to the
' current record in the Recordset
rst.Update
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOUpdateBLOB()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rgPhoto() As Byte
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Open the recordset
rst.Open "SELECT Photo FROM Employees ", _
cnn, adOpenKeyset, adLockOptimistic
' Get the first photo
rgPhoto = rst.Fields("Photo").Value
' Move to the next record
rst.MoveNext
' Copy the photo into the next record
rst.Fields("Photo").Value = rgPhoto
' Save the changes you made to the
' current record in the Recordset
rst.Update
' Close the recordset
rst.Close
End Sub
The code for updating binary data without using GetChunk or AppendChunk is almost identical to the code for updating ordinary fields such as text, numeric, or date fields. The only difference is that it uses a dynamic byte array to store the values. The previous ADO code shows the same differences from DAO as was shown earlier in the example for updating a record.
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. ADO also has a number of abbreviated syntaxes that allow you to do the same thing as you would with DAO but with a lot less code.
A non-parameterized 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()
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
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' 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
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' Close the recordset
rst.Close
End Sub
The code for executing a non-parameterized, 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
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' 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
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' Close the recordset
rst.Close
End Sub
Alternatively, the ADO example could be written more concisely by specifying the parameter values using the Parameters parameter with the Command object's Execute method. The following lines of code:
' 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
could be replaced by the single line:
' Execute the Command, passing in the
' values for the parameters
Set rst = cmd.Execute(, Array(#8/1/1997#, #8/31/1997#), _
adCmdStoredProc)
Note that the Microsoft Jet Provider does not support the Refresh method of the Parameters collection. This means you cannot retrieve the parameters needed for a parameterized query; instead, you must manually populate the Parameters collection.
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
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' Close the recordset
rst.Close
End Sub
The ADO Command object's Execute method can be used for row-returning queries, as shown in the previous section, as well as for non-row-returning queries—also known as bulk operations. The following code examples demonstrate how to execute a bulk operation in both DAO and ADO.
DAO
Sub DAOExecuteBulkOpQuery()
Dim db As DAO.Database
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Execute the query
db.Execute "UPDATE Customers SET Country = 'United States' " & _
"WHERE Country = 'USA'"
Debug.Print "Records Affected = " & db.RecordsAffected
' Close the database
db.Close
End Sub
ADO
Sub ADOExecuteBulkOpQuery()
Dim cnn As New ADODB.Connection
Dim iAffected As Integer
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Execute the query
cnn.Execute "UPDATE Customers SET Country = 'United States' " & _
"WHERE Country = 'USA'", iAffected, adExecuteNoRecords
Debug.Print "Records Affected = " & iAffected
' Close the connection
cnn.Close
End Sub
Unlike DAO, which has two methods for executing SQL statements, OpenRecordset and Execute, ADO has a single method, Execute, that executes row-returning as well as bulk operations. In the ADO example the constant adExecuteNoRecords indicates that the SQL statement is non-row returning. If this constant is omitted, the ADO code will still execute successfully, but you will pay a performance penalty. When adExecuteNoRecords is not specified, ADO will create a Recordset object as the return value for the Execute method. Creating this object is unnecessary overhead if the statement does not return records and should be avoided by specifying adExecuteNoRecords when you know that the statement is non-row returning.