Syntax error in UPDATE statement using ADO

[mshelton23]

I've looked everywhere for a solution to this issue with no luck. I'm using ADO to connect a workbook to a second workbook and then update certain fields in the second workbook. I can get this to work perfectly on any data type except a date. The second workbook has a column called Timestamp set to "yyyy-mm-dd" format. I'd like for the appropriate cells (based on criteria) in the Timestamp column to be updated with the current date. Everything I've tried yields the "Syntax Error in UPDATE statement" error. Here is my code below where I'm trying to update two lines (based on ID of 59 and 60) with a Timestamp. Any help is very much appreciated!!!


Dim ws As Worksheet
Dim myPath As String, connStr As String
Dim mySQL As String
Dim myDate As Date

myDate = Format(Date, "yyyy-mm-dd")

Set ws = ThisWorkbook.Sheets("Worklist")

myPath = <workbook path=""></workbook>

connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & myPath & "';" & _
"Extended Properties=""Excel 12.0;HDR=YES"";"

mySQL = "UPDATE [Data$] " & _
"SET Timestamp = " & myDate & " " & _
"WHERE ID in (59,60)"


Dim conn As New ADODB.Connection

With conn
.Open connStr
.Execute mySQL
.Close
End With

Set conn = Nothing

 

[John_w]

The solution that works for me is to specify an alias (a temporary name) for the table with "UPDATE table_name AS alias_name ..." and use the same alias to qualify the column names in the table. In this example the alias I've chosen is 'X'.

    myDate = Format(Date, "YYYY-MM-DD")
    mySQL = "UPDATE [Data$] AS X " & _
            "SET X.Timestamp = '" & myDate & "' " & _
            "WHERE X.ID IN (59,60)"

Note that "YYYY-MM-DD" is the standard SQL date format (ISO 8601) and nothing to do with your Timestamp column being in the same format. As long as your Timestamp column is an Excel date-time you can give it any format and the SQL UPDATE statement should still work.

 

[John_w]

I should have said that myDate should be declared As String, not As Date. Another solution is to surround each column name with square brackets:

    Dim myDate As String
    myDate = Format(Date, "YYYY-MM-DD")
    mySQL = "UPDATE [Data$] " & _
            "SET [Timestamp] = '" & myDate & "' " & _
            "WHERE [ID] IN (59,60)"