Solved SQL Append Syntax Error (1 Viewer)

[LanaR]

I've been banging my head on my keyboard for some time of this piece of code

 QryAppend = "INSERT INTO TBL_Grape2 [( Grape )] " & _
             "values ( '" & GrapeSendStr & "' ) ;"

When it tries to run, I'm getting a "Syntax error in INSERT INTO statement". I'm sure it's something very simple but have yet to figure out exactly what

 

[Minty]

Try

QryAppend = "INSERT INTO TBL_Grape2 ( Grape ) " & _
             "values ( '" & GrapeSendStr & "' ) ;"

 

[LanaR]

Bingo thanks @Minty

 

[Minty]

You are welcome - If your field name had spaces or was a reserved word you would need the square brackets around the field name;

QryAppend = "INSERT INTO TBL_Grape2 ( [Grape Name] ) " & _
"values ( '" & GrapeSendStr & "' ) ;"

 

[arnelgp]

without using Any Delimeter, you can get away with:

 QryAppend = "INSERT INTO TBL_Grape2 ( Grape ) " & _
             "values p1;"
With Currentdb.CreateQueryDefs("", QryAppend)
    .Parameters("p1") = GrapeSendStr
    .Execute
End With

 

[LanaR]

@Minty I'm very concious of avoiding the pitfalls of using spaces and reserved words in filed names

The query is part of a much larger procedure to normalise a table that until now I was happy being de-normalised. It has to copy grape names into a new table and separate fields that contain more than one grape name (separators being either "," or "&") and not create duplicates. The procedure has been running for a while now, Can't recall how many records there were (several hundred from memory). So hopefully I got all the logic right, and it produces the result I'm after

 

[LanaR]

Would have worked had I reversed the order of the parenthesis and square brackets

 

[Minty]

Would have worked had I reversed the order of the parenthesis and square brackets

Yup - close but no cigar

 

[LanaR]

It's been running for over 30 minutes now, and whilst it is manipulating copies of the data in the live tables, I am a little nervous about what I'll see when it finally finishes

 

[LanaR]

Clearly a deeper flaw in my logic. Back to the drawing board

 

[Minty]

Can you post up some sample starting data, and what you want it to look like - we may be able to help.

 

[LanaR]

Thanks @Minty it's currently a logic problem, that I will, continue to, tussle with.

Should I have no joy, I will certainly avail myself of your kind offer

 

[LanaR]

OK the code seems to be working now but one small hitch, it crashes when it encounters a string that contains an apostrophe
for example Montepulciano d'Abruzzo.

Given that there is only one record that contains an apostrophe, the very simple (and obvious) solution is to edit the data prior to running the procedure and insert once it's finished.

However, it's piqued my curiosity as to how you could handle this with code

 

[Minty]

Arnel's code get's around that by passing in the parameter without the need for escaping it.
The other route is to use triple quotes;

QryAppend = "INSERT INTO TBL_Grape2 ( [Grape Name] ) values ( """ & GrapeSendStr & """ ) ;"

or literal double quotes

QryAppend = "INSERT INTO TBL_Grape2 ( [Grape Name] ) values ( " & chr(34) & GrapeSendStr & chr(34) & " ) ;"

 

[LanaR]

@Minty I'll post the full code once it's running to my satisfaction

 

[arnelgp]

QryAppend = "INSERT INTO TBL_Grape2 ( Grape ) " & _
             "values ( '" & Replace$(GrapeSendStr, "'", "''") & "' ) ;"

 

[LanaR]

Ok the code seems to be doing it job of looping through the records, separating the discrete items and inserting them into a new table. That part is working just fine. Thanks peeps

However, I'm using Dcount() prior to running the append query to test if the next value, to be added to the new table already exists there. This is however failing to detect duplicates, and I believe that this is because the new data is not being properly committed to the table until the whole procedure is completed. I reached this conclusion by inserting a MsgBox to report the item and count, and it always reports 0 (even if I have seen the same item reported numerous time). I know the Dcount() is structured correctly as it will report the correct count once the procedure has finished.

 

[arnelgp]

you need to index on a field(s) with No duplicate so you won't need for dcount().

 

[LanaR]

No that just causes a key violation error the first time the append tries to enter a duplicate. The duplicate needs to be detected before the query tires to append it

 

[NauticalGent]

Montepulciano d'Abruzzo.

Some of the best wine in the world is made from grapes that come from this region!