BUG: "Syntax Error in INSERT INTO..." on ADO Recordset.Update

ID: Q189220

The information in this article applies to:

Microsoft Data Access Components version 1.5

SYMPTOMS

When you call the AddNew method, and then call the Update method of a Recordset, the following error is returned:

   Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
   [Microsoft][ODBC Microsoft Access 97 Driver] Syntax error in INSERT INTO
   statement.

CAUSE

This error occurs if the following are true:

The CursorType is adOpenForwardOnly (0)
The CursorLocation is adUseServer.
A field being updated has a space in the field name.

Calling the AddNew and Update methods, on a Server-Side, Forward-Only cursor, is an invalid operation. ActiveX Data Objects (ADO) compensates for this code flaw by generating an INSERT statement to perform the operation. When ADO generates the INSERT statement, it does not delimit the field names. Therefore, field names that contain a space generate a syntax error.

For example, a valid INSERT statement would be as follows:

   INSERT INTO Table ([field with space]) Values(value)

ADO generates the following code:

   INSERT INTO Table (field with space) Values(value)

RESOLUTION

Here are four possible resolutions:

Do not use spaces in the field names in your database.
Use a CursorLocation of adUseClient (3).
Do not use a CursorType of adOpenForwardOnly (0), such as adOpenKeyset (1), adOpenDynamic (2), or adOpenStatic (3).
Perform the operation by generating your own INSERT statement, with delimiters on the field names, instead of using the AddNew and Update methods.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. We are researching this bug and will post new information here in the Microsoft Knowledge Base as it becomes available.

Additional query words: kbADO150bug kbADO100bug kbADO

Version           : WINDOWS:1.5
Platform          : WINDOWS
Issue type        : kbbug

Last Reviewed: July 13, 1998