syntax error in insert into statement

cocosteel
cocosteel used Ask the Experts™
on
i have just started getting an error message

run time error '-2147217900 (80040e14)':
SYNTAX ERROR IN INSERT INTO STATEMENT

when using the following code
trying to insert into an access database

conn.Execute "INSERT INTO tblPayment (PaymentID, MemberID, PaymentTypeID, Amount, Currency, Date) VALUES ('" & XMLPaymentIDNumber & "', '" & XMLMemberIDNumber & "', '1', '" & ArrEmployeePayments(0) & "', '" & ArrEmployeePayments(1) & "', '" & ArrEmployeePayments(2) & "')"

where conn is in the module as a function

Function conn() As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=c:\db1.mdb"
End Function

i have broken the problem down and the insert will work if i remove the last 2 fields and values in the insert statement ( ie Currency, Date)

on the database Currency and Date are only text fields and are of an ample size for the data they have to receive

thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
TRy copy the whole SQL into the debug window, then paste it in the Queries window, and test to run it.

SQL = "INSERT INTO tblPayment (PaymentID, MemberID, PaymentTypeID, Amount, Currency, Date) VALUES ('" & XMLPaymentIDNumber & "', '" & XMLMemberIDNumber & "', '1', '" & ArrEmployeePayments(0) & "', '" & ArrEmployeePayments(1) & "', '" & ArrEmployeePayments(2) & "')"


Debug.print SQL
Cocosteel,

Don't know what's causing the error message, because I don't know what values are in the ArrEmployeePayments array, but why don't you store the constructed SQL-statement in a string-variable first? The will ease the debugging proces.

...
Dim strSQL As String

strSQL = "INSERT INTO tblPayment (PaymentID, MemberID, PaymentTypeID, Amount, Currency, Date) VALUES ('" & XMLPaymentIDNumber & "', '" & XMLMemberIDNumber & "', '1', '" & ArrEmployeePayments(0) & "', '" & ArrEmployeePayments(1) & "', '" & ArrEmployeePayments(2) & "')"

Debug.Print strSQL 'place a breakpoint over here

Conn.Execute strSQL
...

Good luck,

Mark

Sorry RyanCys, you were too fast for me
Hi,

Currency and Date are data types, and should not be used as field names.  That being said, perhaps enclosing those 2 fields in square brackets [ ] may do it (i.e., [Currency], [Date] ).

Ken

Author

Commented:
thanks for all your suggestions

however, kenspencer solved it, i just had to rename my db columns currency and date

 

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial