Syntax Error in SQL statement

[Shweta]

Hello everyone,

I am trying to enter the random data in a table "tblProduct" using below VBA code. But it is throwing syntax error at highlighted line.

tlProduct has three fields i.e. P_ID as long, P_Name as text and P_Price as long

Sub insert_into_table()
Dim sSQL As String


For r = 1 To 10
x = Round(VBA.Rnd() * 100, 0)
sSQL = "INSERT INTO tblProduct VALUES(" & r & "," & ""Product- "" & r & "," & x & ")"
DoCmd.RunSQL sSQL
Next
End Sub

Kindly suggest.

Thanks in advance!

Regards,
Shweta

 

[apr pillai]

Correct the code as follows, with changes to field names:

sSQL = "INSERT INTO tblProduct VALUES(" & r & "," & "Product-" & r & "," & x & ") "
sSQL = sSQL & "SELECT tblProduct.Field1, tblProduct.Field2, tblProduct.Field3 "
sSQL = sSQL & "FROM tblProduct;"

The VALUES word can be omitted. Notice the extra space given at the end of first two lines inside the last quotes.

 

[xenou]

For quotes within quotes you can also use single quotes (to enclose text literals in SQL statements).

Sub insert_into_table()
Dim sSQL As String
Dim r As Long
Dim x As Long

    For r = 1 To 10
        x = Round(VBA.Rnd() * 100, 0)
        sSQL = "INSERT INTO tblProduct VALUES(" & r & "," & "'Product-" & r & "'," & x & ")"
        Debug.Print sSQL
        'DoCmd.RunSQL sSQL
    Next
End Sub

Output:
INSERT INTO tblProduct VALUES(1,'Product-1',5)
INSERT INTO tblProduct VALUES(2,'Product-2',41)
INSERT INTO tblProduct VALUES(3,'Product-3',86)
INSERT INTO tblProduct VALUES(4,'Product-4',79)
INSERT INTO tblProduct VALUES(5,'Product-5',37)
INSERT INTO tblProduct VALUES(6,'Product-6',96)
INSERT INTO tblProduct VALUES(7,'Product-7',87)
INSERT INTO tblProduct VALUES(8,'Product-8',6)
INSERT INTO tblProduct VALUES(9,'Product-9',95)
INSERT INTO tblProduct VALUES(10,'Product-10',36)

I find this the easiest way to create the needed quotes for the raw sql statement.

Otherwise you can try this (very unpleasant) to escape double quotes:

sSQL = "INSERT INTO tblProduct VALUES(" & r & "," & """Product-" & r & """," & x & ")"

More pleasant is to stick to good old concatenation (easier to avoid mistakes):

sSQL = "INSERT INTO tblProduct VALUES(" & r & "," & Chr(34) & "Product-" & r & Chr(34) & "," & x & ")"

Both of the last two will produce output with double quotes:
INSERT INTO tblProduct VALUES(1,"Product-1",30)
INSERT INTO tblProduct VALUES(2,"Product-2",38)
INSERT INTO tblProduct VALUES(3,"Product-3",30)
INSERT INTO tblProduct VALUES(4,"Product-4",95)
INSERT INTO tblProduct VALUES(5,"Product-5",98)
INSERT INTO tblProduct VALUES(6,"Product-6",40)
INSERT INTO tblProduct VALUES(7,"Product-7",28)
INSERT INTO tblProduct VALUES(8,"Product-8",16)
INSERT INTO tblProduct VALUES(9,"Product-9",16)
INSERT INTO tblProduct VALUES(10,"Product-10",65)


But as I said, it's somewhat easier to use single quotes to enclose text literal values in SQL strings.

 

[Shweta]

Thank you all

Worked perfectly.