Discussion Locked

SQL Acting funny for INSERT INTO

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I'm working with Visual Basic 6.0 and an Access 2000 Database accessing it through ADODB 2.5 objects (declared in code) and I'm getting some strange behaviour.
When I try to make an Insert Into statement for a specific table I get "Error in Insert Into Statement" message from the ADO object.
If I process a Debug.Print SQL and copy the output and run the exact same SQL in Access it works fine. Also any other Insert statements I've writen do work fine (all for other tables tho.)

Thanks In Advance
Shawn

5 replies

by peyison

Hard to say without seeing your insert statement, but one thing to check for is to see if you are using reserved words for field names. The list of reserved words is larger for ADO than for Access, so queries may work in Access, but not via ADO. Some words to check for are User, Password and Size. If you have fields with these names, or with other common names that might have other meanings in ADO, try changing the field name - UserId, UserPassword, Size1, etc.

If this is not the case, try posting your resolved query string here for others to look at (from debug.print).

Hope this helps.

by srudolph_

The problem was either me using 'Year' as the field name or the field got corrumpted (this happened before in the same database).

by xxx123

Peyson's statements are correct. I would also like to point out that when you use ADO to connect to a database, you are creating a SQL statement that is more-or-less ANSI standard and it gets translated by the OLE DB provider for the specific database you are using.

So it is entirely possible to create a SQL statement that ADO does not like but Access will accept. (Access SQL is the most non-ANSI-standard sql there is.)

by srudolph_

Changing the name of my 'year' field did help (actually created a new field) but I must agree Access doesn't meet many ANSI standards (or any others)

This discussion is closed to new replies.