This query runs perfectly in Access 2010 but when I try to execute it in my code I get Syntax error in INSERT INTO statement

Question

I am a newby programmer and I have had a look at other posts similar to this and they say that the problem is usually due to reserved words being used, however I am sure that I am not using any. The sql runs fine in access and creates the record (using the sql from the variable v_sqlquery, however when in VB the following error appears "Syntax error in INSERT INTO statement". Can anyone help me please, I have spent a few hours trying to solve this? Thanks in advance.

    Dim v_SQLquery As String 'stores the sql query
    Dim v_command As OleDbCommand 'passes command to db
    Dim v_results As OleDbDataReader
    Dim v_custID As Integer
    Dim v_balance As String
    Dim v_purchase As String
    Dim v_date As Date
    Dim v_time As Date
    Dim v_transID As Integer


    v_custID = txt_custID.Text
    v_balance = txt_custbalance.Text
    v_purchase = txt_puramount.Text
    v_date = DateValue(Now) 'the current date
    v_time = TimeValue(Now) 'the current time

    If v_purchase = "" Then
        MsgBox("Please enter the purchase amount.")

    ElseIf v_purchase > v_balance Then
        MsgBox("There are not enough funds in the account to complete this transaction.")

        v_SQLquery = "INSERT INTO Transaction (Trans_type, Trans_amount, Trans_date, Trans_time, Cust_ID) VALUES ('P','" & v_purchase & "','" & v_date & "','" & v_time & "','" & v_custID & "');"


        v_command = New OleDbCommand(v_SQLquery, v_connection)


        v_results = v_command.ExecuteScalar 
    End If 

    v_connection.close()

Answer 1

The immediate error is because Transaction is a reserved word. Bracket that table name in your INSERT statement.

"INSERT INTO [Transaction] (

Fixing that issue may expose other issues. For example Access' db engine recognizes a date enclosed in # characters, such as #2015-02-04#, as Date/Time datatype. But the same date enclosed in single quotes, '2015-02-04', will be handled as a string value (which it is). We can't tell yet whether that issue applies to your situation. But there is a way to avoid such delimiter issues.

As others advised already, you would be wise to use a parameterized INSERT instead of concatenating values into the statement text. Not only will that approach protect you from SQL injection, it also avoids delimiter issues for the values you're inserting.

Note with either approach, the reserved word issue for your table name will still apply.