Visual Basic Q&A

Symptoms
When you use ADO to edit and then update or delete newly added records in a Microsoft Access 97 table, you may receive the following error message when you call the Update method:

Run-time error ‘-2147217864 (80040e38)’:
Row cannot be located for updating. Some values may have been changed since it was last read.This error message may also occur when you edit or delete newly added records in a DataGrid control. The DataGrid control issues an Update behind the scenes when you move to another row.
NOTE: This error does not occur when you edit or delete existing records.
Resolution
The Access 97 table contains an AutoNumber field, and the auto-incremented values of newly added records may not be available for the client recordset. For example, if the AutoNumber field is 5 for the new record in the table, the value of this field in the recordset is always 0. ADO uses this value to locate the record when ADO builds an Update Action Query and sends the value to the Microsoft Jet engine to perform the Update on the specified record. However, the Jet engine cannot locate the record based on that value; thus, you receive the above-mentioned error message.

Symptoms
With SQL Server’s quoted_identifier option set to Off, you may receive the following error:

Run-time error ‘-2147217900 (80040e14)’:
Line 1: Syntax error near ‘tablename’ This error occurs when you are using client-side cursors with the Microsoft OLE DB Provider for SQL Server (SQLOLEDB). The error occurs on an ActiveX Data Objects (ADO) recordset’s Update method and may occur on an AddNew method.
Resolution
With ADO client-side cursors, when you invoke an ADO recordset’s AddNew or Update method, the OLE DB Provider prepares a SQL statement to send to SQL Server.
The Microsoft OLE DB Provider for SQL Server automatically quotes identifiers on an ADO recordset’s Update method and may quote identifiers on an AddNew method. Identifiers include table names and field names.
For example, updating the Titles table in the Pubs database with the following code:

MyADORecordet.Update The preceding would be prepared similar to the following:

UPDATE “titles” SET “title”=’Hello World’ WHERE “title_id”=’3′ Note that the table name is in quotes, “titles”, and that each field name is in quotes, “title”, “title_id”, and so on.
If SQL Server’s Quoted_Identifier option is set to Off, SQL Server will not recognize table names and field names enclosed in quotes.
The error “Syntax error near ‘tablename’” occurs.

Symptoms
The following error occurs when using RDO or ADO to update datain an Access (Jet) database:

Query is too complexDue to a limitation of the Jet database engine, this error can occur if the recordset to be updated contains morethan 40 fields.
Using the Jet 3.51 engine, the error occurs if the recordset to be updated contains more than 50 fields. With the Jet 4.0 engine and later, the error occurs if the recordset to be updated contains more than 99 fields.
Using the Jet 4.0 engine and later, you may also see the errors:

Expression too complex -or-

Too many defined fieldsThis article describes the problem scenario, the Jet limitation, and several workarounds.
Resolution
When executing the Update() or BatchUpdate() methods of RDO’s rdoResultsetobject, or the Update() or UpdateBatch() methods of ADO’s Recordset object,the ODBC error S1000 “Query is too complex” (Visual Basic run-time error40002) occurs. An error with the same description occurs using the Microsoft Jet OLE DB providers versions 3.51 and later.
This behavior occurs because the default behavior of ADO and RDO is to useeach field in the recordset to determine the record to be updated on theserver. That is, when the update is attempted, a SQL Update query is sentto the server. Part of this query is a WHERE clause that is used toidentify the record to be updated. An AND clause appears within the WHEREclause for each field to be used in that identification. Updating arecordset with more than 40 fields involves a WHERE clause with more than40 ANDs.
The Jet engine allows only 40 AND clauses within a SQL WHERE or HAVINGclause. Jet 3.51 increased this limit to 50 AND clauses, and Jet 4.0 increased the limit to 99 AND clauses. The Access engine returns a “Query is too complex” error whenit encounters such a SQL statement. See the REFERENCES section of thisarticle for more information.
There are several ways to avoid this behavior:For new applications, use DAO when working with Access databases. DAOwas designed for this purpose, and will not issue a SQL query to performthe update. Note that RDO and especially ADO were designed to operateefficiently with many other types of databases and are recommended forapplications that will interact with various servers or that will beupsized in the future to use Microsoft SQL Server, for example.When opening the recordset to be updated (such as the RDO OpenResultsetor ADO OpenRecordset methods), select specific, and 40 or fewer fields.Use a server-side cursor:
To do this in ADO, set the CursorLocation location property of theRecordset object to adUseServer before opening the Recordset:

…Dim rs As ADODB.RecordsetSet rs = New ADODB.Recordsetrs.CursorLocation = adUseServerrs.Open …… In RDO, set the CursorDriver property of the rdoEnvironment objectbefore calling the OpenConnection() method:

…Dim eng As RDO.rdoEngineDim cn As RDO.rdoConnectionDim env As RDO.rdoEnvironmentDim rs As RDO.rdoResultsetSet eng = New rdoEngineSet env = eng.rdoCreateEnvironment(…)env.CursorDriver = rdUseServerSet cn = env.OpenConnection(…)Set rs = cn.OpenResultset(…)… Ensure that a unique key is used to identify the record to be updated,rather than the default behavior of using every field. A unique key(though not necessarily a primary key) must be defined in the underlyingrecordset for this technique to work. To do this in ADO (this will not work in versions prior to ADO2.0), set the Recordset object’s “Update Criteria” property toadCriteriaKey. This will work both for the Update() and UpdateBatch()methods:

…Dim rs As ADODB.RecordsetSet rs = New ADODB.Recordsetrs.CursorLocation = adUseClientrs.Properties(“Update Criteria”).Value = adCriteriaKeyrs.Open …… For RDO, use the ClientBatch cursor library and ensure that theResultset object’s UpdateCriteria property is not set tordCriteriaAllCols. By default, this property is set to rdCriteriaKey,which will cause the use of the primary key to determine the row(s) tobe updated on the server:

…Dim eng As RDO.rdoEngineDim cn As RDO.rdoConnectionDim env As RDO.rdoEnvironmentDim rs As RDO.rdoResultsetSet eng = New rdoEngineSet env = eng.rdoCreateEnvironment(…)env.CursorDriver = rdUseClientBatchSet cn = env.OpenConnection(…)Set rs = cn.OpenResultset(…)rs.Edit…rs.UpdateCriteria = rdCriteriaKeyrs.Update…