HELP! - Run-time error '3034': Syntax error in insert into (1 Viewer)

cathie

Registered User.
Local time
Today, 10:49
Joined
Oct 21, 2017
Messages
10
Can anybody eyeball something that I can't see???

Code:
            MySql = "INSERT INTO All2 (LoanType, Street, Unit, City, State, Zip, Borrower, Vendor, DateRecd, Wholesaler, Processed, " & _
                                "UploadID, Result, Latitude, Longitude, ZillowID, Zestimate, ZRestimate, Zerror) VALUE " & _
                                "('" & LoanType & "','" & Street & "','" & Unit & "','" & City & "','" & State & "','" & Zip & _
                                "','" & Borrower & "'," & Vendor & ",#" & Format(DateRcvd, "mm/dd/yyyy") & "#," & Wholesaler & "," & Processed & _
                                "," & UploadID & "," & Result & ",'" & Latitude & "','" & Longitude & "','" & ZillowID & _
                                "'," & Zestimate & "," & ZRestimate & ",'" & ZError & "')"
            Debug.Print MySql
            CurrentDb.Execute MySql, dbFailOnError

Debug.Print Result = "INSERT INTO All2 (LoanType, Street, Unit, City, State, Zip, Borrower, Vendor, DateRecd, Wholesaler, Processed, UploadID, Result, Latitude, Longitude, ZillowID, Zestimate, ZRestimate, Zerror) VALUE ('2nd NPN','1 Main Street','','Ijamsville','MD','21754','John Smith',123,#10/01/2017#,,1,568,1,'39.31063','-77.330752','36881773',477935.00,2478.00,'')"
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:49
Joined
Aug 30, 2003
Messages
36,118
First thought is the empty value after the date (two commas together).
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:49
Joined
May 7, 2009
Messages
19,169
create a public function in a Standard Module to
generate the correct Delimiter for your VALUES.
in this case you would not need to worry if
it is date, integer, null is being feed in the
Query:

Code:
Public Function SQLFix(value As Variant) As String
    Select Case VarType(value)
    Case VbVarType.vbBoolean, VbVarType.vbByte, VbVarType.vbCurrency, _
        VbVarType.vbDouble, VbVarType.vbDecimal, VbVarType.vbInteger, _
        VbVarType.vbLong, VbVarType.vbLongLong, VbVarType.vbSingle
        SQLFix = value
    Case VbVarType.vbDate
        SQLFix = "#" & Format(value, "mm/dd/yyyy") & "#"
    Case VbVarType.vbString
        SQLFix = Chr(34) & value & Chr(34)
    Case VbVarType.vbNull
        SQLFix = "Null"
    End Select
End Function

then you need only to modify your SQL and remove the "'"
and "#":

MySql = "INSERT INTO All2 (LoanType, Street, Unit, City, State, Zip, Borrower, Vendor, DateRecd, Wholesaler, Processed, " & _
"UploadID, Result, Latitude, Longitude, ZillowID, Zestimate, ZRestimate, Zerror) VALUE " & _
"(" & SQLFix(LoanType) & "," & SQLFix(Street) & "," & SQLFix(Unit) & "," & SQLFix(City) & "," & SQLFix(State) & "," & SQLFix(Zip) & "," & _
"','" & Borrower & "'," & Vendor & ",#" & Format(DateRcvd, "mm/dd/yyyy") & "#," & SQLFix(Wholesaler) & "," & SQLFix(Processed) & "," & _
"," & SQLFix(UploadID) & "," & SQLFix(Result) & "," & SQLFix(Latitude) & "," & SQLFix(Longitude) & "," & SQLFix(ZillowID) & _
"," & SQLFix(Zestimate) & "," & SQLFix(ZRestimate) & "," & SQLFix(ZError) & ")"
Debug.Print MySql
CurrentDb.Execute MySql, dbFailOnError
 

cathie

Registered User.
Local time
Today, 10:49
Joined
Oct 21, 2017
Messages
10
Re: HELP! - Run-time error '3134': Syntax error in insert into

Thanks so much for the replies and assistance! I tried your solution with some minor tweaking and unfortunately, I still get the same result :( I tried with and without a closing semi-colon... the last 2 numeric figures are defined as currency fields in the database; I'm not sure if that makes a difference.

Code:
            MySql = "INSERT INTO All2 (LoanType, Street, Unit, City, State, Zip, Borrower, Vendor, DateRecd, Wholesaler, Processed, " & _
                    "UploadID, Result, Latitude, Longitude, ZillowID, Zestimate, ZRestimate, Zerror) VALUE " & _
                    "(" & SQLFix(LoanType) & "," & SQLFix(Street) & "," & SQLFix(Unit) & "," & SQLFix(City) & "," & SQLFix(State) & "," & SQLFix(Zip) & "," & _
                    SQLFix(Borrower) & "," & Vendor & "," & SQLFix(DateRcvd) & "," & SQLFix(Wholesaler) & "," & SQLFix(Processed) & _
                    "," & SQLFix(UploadID) & "," & SQLFix(Result) & "," & SQLFix(Latitude) & "," & SQLFix(Longitude) & "," & SQLFix(ZillowID) & _
                    "," & Zestimate & "," & ZRestimate & "," & SQLFix(ZError) & ");"
            Debug.Print MySql
            CurrentDb.Execute MySql, dbFailOnError

Debug.Print result = "INSERT INTO All2 (LoanType, Street, Unit, City, State, Zip, Borrower, Vendor, DateRecd, Wholesaler, Processed, UploadID, Result, Latitude, Longitude, ZillowID, Zestimate, ZRestimate, Zerror) VALUE ("2nd NPN","10009 Doctor Perry Road",Null,"Ijamsville","MD","21754","Lee Smith",123,#10/01/2017#,Null,1,568,1,"39.31063","-77.330752","36881773",477935.00,2480.00,Null);"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:49
Joined
May 7, 2009
Messages
19,169
error 3034:
"You tried to commit or rollback a transaction without first beginning a transaction."

do you have your table "ALL2" open in the form/subform?
if so, are you in edit mode when?

if the form is Dirty (something has been changed and not
saved yet), you need to save it first:

Me.Dirty = False

also it is not wise that you have ALL2 table
open in subform/form and you are issuing an insert/update
query on it.

you MUST use Recordset insert/Update, eg:

Dim rs As DAO.Recordset
set rs = Me.RecordsetClone
rs.AddNew
''' set the fields here
...
rs.Update
rs.Close
set rs=Nothing
 

cathie

Registered User.
Local time
Today, 10:49
Joined
Oct 21, 2017
Messages
10
Hi arnelgp :)

I am a twit of major magnitude! I got it to work! I had an incorrect column name.

That SQLFix code is terrific though, and I appreciate you sharing it!

Thanks again for all your help!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:49
Joined
May 7, 2009
Messages
19,169
Glad you figure it out
 

Solo712

Registered User.
Local time
Today, 10:49
Joined
Oct 19, 2012
Messages
828
Hi arnelgp :)

I am a twit of major magnitude! I got it to work! I had an incorrect column name.

That SQLFix code is terrific though, and I appreciate you sharing it!

Thanks again for all your help!

Let me guess: DateRcvd vs DateRecd ? :)

Best,
Jiri
 

Users who are viewing this thread

Top Bottom