Stupid Access and INSERT

[ober0330]

I've tried doing a search for a solution, but to no avail.

I have a simple insert statement that should write to an Access database. It's only one table and all the datatypes are text.

CN.Execute "INSERT INTO Part_Info ( ID, Man, Cur, Desc )" & _
            "Values('" & arrAdd(0) & "', '" & arrAdd(1) & "', '" & arrAdd(2) & "', '" & arrAdd(3) & "')"

I copied my code from another project exactly and simply changed all the names of the variables.

For some reason it will not take all of these values and says there is something wrong with my insert statement. If I take out the last 2 it works. If I insert the PK (the ID field) and then do an update, I can get the update to work for Man and Cur, but I have to update them seperately. This is driving me absolutely mad. Please help me.

[Eras3r]

Create the statement in Access using their query builder, then goto SQL view and copy and paste the SQL statement it generates for you.

I had a problem with insert statements as well, it was because of the "values" statement. Never wanted to work. So I just used the sytanx of the access-generated query.

[fungi]

Here's a fast way of getting to the source of the matter that I like to use:

Assign your SQL to a variable, like szSQL = "INSERT ..."

Set a break point after you assign it and in the immediate window type debug.print szSQL or ? szSQL

You should see your complete statment. Copy and paste it as a new query in Access and run it there to see what's going on...

Just from looking it it it looks OK except you may need a space before VALUES

[Destined Soul]

Originally posted by ober5861
I've tried doing a search for a solution, but to no avail.

CN.Execute "INSERT INTO Part_Info ( ID, Man, Cur, Desc )" & _
            "Values('" & arrAdd(0) & "', '" & arrAdd(1) & "', '" & arrAdd(2) & "', '" & arrAdd(3) & "')"

Well, there's probably two things here, but one is causing your main error.

1) numbered fields complain when you have the single quotes around it, such as Num = '3' whereas the SQL usually wants Num = 3. For text fields, use the single quotes. For numbers, without.

2) be careful on inserting a specific PK. If the number is autogenerated, you don't even have to insert it at all (should be inserted automatically..)

I could be wrong, but that's what I'd try. Also, Fungi has a good idea of testing out your sql statement in access.

Destined

[ober0330]

ok... nevermind... turns out it was the damn names of the fields.