VBA SQL Syntax Woes

TerryHogarth21

Board Regular
I'm creating a SQL string in VBA to insert to a SQL table.

This syntax string is driving me nuts as I have an inaccurate syntax of an apostrophe or extra quotation somewhere when verifying each of the values
Code:
 sSQL = sSQL & " VALUES (" & _
"'" & Sheet28.Cells(lRow, 1) & "', " & "'" & Sheet28.Cells(lRow, 2) & ", " & "'" & Sheet28.Cells(lRow, 3) & "', " & "'" & Sheet28.Cells(lRow, 4) & "', " & "'" & Sheet28.Cells(lRow, 5) & "', " & "'" & Sheet28.Cells(lRow, 6) & "', " & "'" & Sheet28.Cells(lRow, 7) & "', " & "'" & Sheet28.Cells(lRow, 8) & "', " & "'" & Sheet28.Cells(lRow, 9) & "', " & _

I want to ensure also that for the field types with data including Numbers in the SQL table and Excel I just need it to look like this
Code:
 Sheet28.cells(lRow, 1)
and for Text values it should include the apostrophe like this
Code:
 "'" & Sheet28.cells(lRow, 1) & "'"
.

For date fields that have values I think it's serial in Excel but date/time in SQL table so I'm assuming I don't need the apostrophe?

If anybody can clarify that would be great!

Thanks
 

TerryHogarth21

Board Regular
Ok made the changes to include "#" for date fields but getting the error somewhere in the 2nd line.
Code:
   sSQL = sSQL & " VALUES ("
    sSQL = sSQL & Sheet28.Cells(lRow, 1) & ", " &  Sheet28.Cells(lRow, 2) & ", " & Sheet28.Cells(lRow, 3) & ", "  & Sheet28.Cells(lRow, 4) & ", " & Sheet28.Cells(lRow, 5)  & ", " & Sheet28.Cells(lRow, 6) & ", " &  Sheet28.Cells(lRow, 7) & ", " & "'" & Sheet28.Cells(lRow, 8)  & ", " & "'" & Sheet28.Cells(lRow, 9) & "', "
Got a feeling it's this line
Code:
 sSQL = sSQL & sheet28.cells(lRow,1)  & ","
since that's the first field that's a number it doesn't need the apostrophe but am I missing a quotation or something after joining it with the prior sql string?
 

Kyle123

Well-known Member
", " & "'" & Sheet28.Cells(lRow, 8) & ", "

Should be

", " & "'" & Sheet28.Cells(lRow, 8) & "', "
 

TerryHogarth21

Board Regular
Success! thanks Kyle123

- Also to clarify you do not need '#' for date/time format in the vba syntax to get it into a sql statement.
 

Kyle123

Well-known Member
Whether you need the # very much depends on your database. Access needs it whereas SQL server for example stores dates as strings. It is important though to always always pass dates in the format yyyy-mm-dd
 

Some videos you may like

This Week's Hot Topics

Top