Error 3146 in Access 2007, not in Access 2010

[opopanax666]

Hello everyone,



Following piece of code gives me no trouble in A2010, but gives an error 3146 in A2007, so probably a syntax problem, but I can't see anything wrong:

strSQL = "INSERT INTO dbo_tblGEPRODUCEERD ([Geprod_ID], [Barcode], [Weging_datum], [Product_id], [Bestemming_id], [Gewicht_bruto], [Gewicht_tarra], [Gewicht_netto], [Geprod_status_id]) VALUES (" & lngNewGeprod_ID & ", '" & "*" & strCODE & "*" & "', " & SQLDate(varDATUM) & ", " & intPRODUCT & ", " & intBESTEMMING & ", " & sinBRUTO & ", " & sinTARRA_TOTAAL & ", " & sinNETTO & ", 1)"
Set db = CurrentDb()
db.Execute strSQL, dbFailOnError
Set db = Nothing

Geprod_ID: integer (= lngNewGeprod_ID)
Barcode: string (= "*" & strCODE & "*")
Weging_datum: date (= SQLDate(varDATUM))
Product_id: integer (= intPRODUCT)
Bestemming_id: integer (= intBESTEMMING)
Gewicht_bruto: single (= sinBRUTO)
Gewicht_tarra: single (= sinTARRA_TOTAAL)
Gewicht_netto: single (= sinNETTO)
Geprod_status_id: integer (= 1)

The "SQLDate(varDATUM)" is the Allen Brown function found here. It results in a "date-string" (i.e. "#dd/mm/yyyy hh:mm:ss#"). After getting the 3146, I tried handling this as a string, so putting it beween quotation marks, but that gave a 3464 error, so that wasn't the problem.

I can't see anything wrong with the rest, so is there something different between 2007 & 2010 that I'm not aware of?

[pbaldy]

First, what is the text of the error? Second, this technique will help make sure the SQL is coming out correctly.

http://www.baldyweb.com/ImmediateWindow.htm

[opopanax666]

First, what is the text of the error? Second, this technique will help make sure the SQL is coming out correctly.

http://www.baldyweb.com/ImmediateWindow.htm

Found it! First changed everything to recordsets (which I wanted to do anyway), but got the same error.
Then used the procedure found here to get some better info.
Gave me an "error 544 - cannot insert explicit value for identity column when identity_insert is set to OFF", which reminded me of the fact I inserted a value in a SQL autonumber field (which I intended to change, but forgot about ).

So, changed that, and everything works now!..

[pbaldy]

Glad you got it sorted.