In an ODBCDirect workspace, the default Recordset object is a read-only, forward-only-type Recordset object. Therefore, if you create the default Recordset object by opening it without specifying a value for the type argument, you won’t be able to edit data on the server. If you want to edit data on the server, you need to explicitly specify a lock type other than dbReadOnly for the lockedits argument of the OpenRecordset method.
When using an updatable ODBCDirect Recordset object, you can’t read or set the Value property of a field with a Memo (SQL_LONGVARCHAR) or OLE Object (SQL_LONGVARBINARY) data type more than once without refreshing the current record. If you try to do so, DAO will return error 3032. To work around this, you can use Move 0 to refresh the current record after a reading or setting the Value property of the field once. For example, to read and then set the Value property of the field, first read the property, use Move 0 to refresh the current record, then write the new value.
When performing operations on an ODBCDirect Recordset object within a transaction, the cursor is closed when you commit the transaction. If you try to use a method that requires the cursor immediately after committing the transaction, such as the RecordCount method, an error will occur. To avoid this, use the Requery method on the Recordset object after committing the transaction to reopen the cursor. The cursor is closed after you commit or roll back a transaction because this is the default behavior of the Microsoft SQL Server ODBC Driver. It is possible to change this default to keep the cursor open, however to do so, you must use the ODBC API to call the SQLSetConnectOption function to change the SQL_PRESERVE_CURSORS option.
See Also For more information, see Microsoft ODBC 3.0 Software Development Kit and Programmer’s Reference.
Because you can’t open a table-type Recordset object in an ODBCDirect workspace, you can’t use the Index property or the Seek method to retrieve data. Also, Recordset objects opened against ODBC data sources do not support any of the Find methods: FindFirst, FindNext, FindPrevious, and FindLast. In a client/server environment, it’s more efficient to retrieve only the data that you need, rather than retrieving more records than you need and then searching through those records for the data that you want. Therefore, design your queries to return only the records that you need.