• I am creating a new home page for this site, where the focus will be on directing people to the forums. If you would like to provide a testimonial, I would be most grateful. The thread where this is discussed can be found here: Seeking Testimonials Alternatively, just private message me.

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

cathie

Registered User.
Local time
Today, 08:29
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, 05:29
Joined
Aug 30, 2003
Messages
34,320
First thought is the empty value after the date (two commas together).
 

arnelgp

error reading drive A:
Local time
Today, 20:29
Joined
May 7, 2009
Messages
9,846
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, 08:29
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

error reading drive A:
Local time
Today, 20:29
Joined
May 7, 2009
Messages
9,846
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, 08:29
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!
 

Solo712

Registered User.
Local time
Today, 08:29
Joined
Oct 19, 2012
Messages
762
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 (Users: 0, Guests: 1)

Top Bottom