Using ADO to add date to another workbook

[mshelton23]

I'm using ADO to connect one workbook to another and then update certain fields. This works with no issue with the exception of dates. The workbook being updated has a "Timestamp" column formatted as "yyyy-mm-dd." No matter what I try, the update fails when the SQL is run with the error "Syntax error in UPDATE statement." I've tried changing the numberformats, different data types, but nothing works. Here is my code below which always fails at ".Execute mySQL"



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 <some criteria=""></some>


Dim conn As New ADODB.Connection

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

Set conn = Nothing

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...r-update-statement-using-ado.html#post5294432

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.