Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 189220 - Last Review: March 2, 2005 - Revision: 2.2
BUG: "Syntax Error in INSERT INTO..." on ADO Recordset.Update
This article was previously published under Q189220
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.
Using the Microsoft Access ODBC Driver version4.00.4202.00 or later, the error message is:
Run-time error '-2147467259(80004005)':
[Microsoft][ODBC Microsoft Access Driver]Syntax error in INSERT INTO statement.
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.
- You are using the Microsoft Access ODBC driver.
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)
Here are several 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.
- Use the Microsoft OLE DB Provider for Jet version 3.51 or 4.0 instead of the Microsoft OLE DB Provider for ODBC and the Microsoft Access ODBC Driver.
Microsoft has confirmed this to be a bug in the Microsoft products listed
at the beginning of this article.
APPLIES TO
- Microsoft Data Access Components 1.5
- Microsoft Data Access Components 2.0
- Microsoft Data Access Components 2.1 Service Pack 2
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
- Microsoft Open Database Connectivity Driver for Access 3.5
- Microsoft Open Database Connectivity Driver for Access 4.0
| kbbug kbdatabase kbdriver kbjet KB189220 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate