Insert into ms Access with ADO gives Error Message

Insert into ms Access with ADO gives Error Message

Post by mike » Wed, 27 Feb 2002 07:28:02



I am trying to insert a populated record into a table and getting an error:
Syntax Error in INSERT INTO statement
my code is recordset1.open "insert into table (field1,field2,count)
values(string,string,autonumber)",connection1,adopenstatic,adlockoptimistic

I have tried deleting the last column "count" but gives me a missing
parameter error
I suck at ms access. can anyone recommend a good access book since it has a
few peculiarities from normal ansi sql?
Thanks in advance...

 
 
 

Insert into ms Access with ADO gives Error Message

Post by Scott Brow » Wed, 27 Feb 2002 08:14:09



Quote:> I am trying to insert a populated record into a table and getting an
error:
> Syntax Error in INSERT INTO statement
> my code is recordset1.open "insert into table (field1,field2,count)

values(string,string,autonumber)",connection1,adopenstatic,adlockoptimistic

why try and insert a value into your autonumber column ?  If that's the
indexed primary key of the table you are inserting into there should be no
need to try and insert into that col...

Or have I got the wrong end of the stick with your SQL ?
S
___________________________________________
http://www.blackdogwebdesign.co.uk/irc
real-time chat over http with no applets...;0)>
http://www.boardmad.com

 
 
 

Insert into ms Access with ADO gives Error Message

Post by mike » Wed, 27 Feb 2002 11:35:03


I wouldn't think you would need to insert into the count column, but when I
leave it off, I get the error message "no value given for one or more
required parameters.". It's very troubling

:


: > I am trying to insert a populated record into a table and getting an
: error:
: > Syntax Error in INSERT INTO statement
: > my code is recordset1.open "insert into table (field1,field2,count)
: >
:
values(string,string,autonumber)",connection1,adopenstatic,adlockoptimistic
:
: why try and insert a value into your autonumber column ?  If that's the
: indexed primary key of the table you are inserting into there should be no
: need to try and insert into that col...
:
: Or have I got the wrong end of the stick with your SQL ?
: S
: ___________________________________________
: http://www.blackdogwebdesign.co.uk/irc
: real-time chat over http with no applets...;0)>
: http://www.boardmad.com
:
:
:
:
 
 
 

Insert into ms Access with ADO gives Error Message

Post by Mike Labos » Wed, 27 Feb 2002 14:12:35


Quote:> my code is recordset1.open "insert into table (field1,field2,count)

values(string,string,autonumber)",connection1,adopenstatic,adlockoptimistic

First, Access will not let you write to an autonumber for any reason except
to delete the record.  Second, switch to SQL view.  Access declares
parameters in a funky way.  If your statement works like the one below, then
lose the parameter that's still declared for the autonumber once you remove
the autonumber thing from the insert and value clauses.

INSERT INTO ThisTable
    (Field1, Field2)
VALUES
    (ThisString, ThatString)

Another thing that you should do is sort of a matter of form, since it
doesn't seem to be related to what your asking:

oRS.Open(...) is for SELECT statements that return rows.

For INSERT, UPDATE and DELETE, you should use the Execute method of the
Connection object.
--
Peace & happy computing,

Mike Labosh, MCSD MCT
"Escriba coda ergo sum." -- vbSensei

 
 
 

Insert into ms Access with ADO gives Error Message

Post by Denver Marti » Thu, 28 Feb 2002 03:24:20



Quote:> I am trying to insert a populated record into a table and getting an
error:
> Syntax Error in INSERT INTO statement
> my code is recordset1.open "insert into table (field1,field2,count)

values(string,string,autonumber)",connection1,adopenstatic,adlockoptimistic

Quote:

> I have tried deleting the last column "count" but gives me a missing
> parameter error
> I suck at ms access. can anyone recommend a good access book since it has
a
> few peculiarities from normal ansi sql?
> Thanks in advance...

Is it possible to use the following syntax, or is this DAO specific?

With rs
    .addnew
    .field("Column1") = Value1
    .field("Column2") = Value 2

    ... etc.

    .update
End With

 
 
 

1. SQL Server trigger to give warning message in MS Access front end

I have a SQL Server 7.0 database and an MS Access 97 front end
application.

I have been trying to use RAISERROR to give a warning message when I
try updating data in a table linked from my SQL Server via ODBC.
However, when I use severity level equals to or less than 10, no
message appears. When I use severity level greater than 10, the update
is not allowed. I want the update to go through, with a warning
message to the user.

Is there any way to do this without turning to the use of VBA?

Please send any replies by e-mail.

Thank you very much in advance for any help.

Best Regards,
Jorn K Baltzersen

2. PowerBuilder connection to Btrieve data

3. adding ADO support gives error messages

4. ChimneySweep: lots more tech info available now

5. Ado.open gives Dll error message

6. Concurrency Control in Parallel Databases = Ref List!

7. Error Accessing MS-Access using ADO..

8. Informix 4GL opportunities in Southern California

9. INFO: Inserting data into a MS Access DB using ADO

10. Locks error message when upsizing from MS Access to SQL

11. MS Access Error Message

12. Displaying Sql server error messages in MS Access

13. error messages reported by OLE DB /ADO with Access 2000 database at an internet server