I'm trying to run an update query from Access VBA to update a 2014 SQL Server table. The SQL server table(s) have linked Access 2016 tables.
If I use the Access syntax for dates (#) I get a type mismatch and the update doesn't work. If I use (') for dates I still get a type mismatch and the update doesn't work. If I run the update query (as it is below) in SQL query builder the query runs fine and performs the Update.
What's the solution to running a date update in SQL server with linked Access tables?
Code:
Update rdLab.tblSubTestRequests SET tblSubTestRequests.Project_Request = 1,tblSubTestRequests.IsRelease_Valid = -1,tblSubTestRequests.ProjectName = 'Latest Project Request',tblSubTestRequests.Objective = 'This is the objective.',tblSubTestRequests.Project_Notification_Date = '01/01/2000',tblSubTestRequests.Target_Completion_Date = '01/01/2001',tblSubTestRequests.Requestor = 'Davidson, Jeffrey',tblSubTestRequests.Requesting_Group = 'R&D',tblSubTestRequests.ECR_Number = '12345',tblSubTestRequests.QBB_Test_Tracker = -1,tblSubTestRequests.Results_Folder = 'Latest Project Request',tblSubTestRequests.Project_Status = 'Reject',tblSubTestRequests.Project_Status_Notes = 'rejected',tblSubTestRequests.Processed_Release = -1 From rdLab.tblSubTestRequests WHERE tblSubTestRequests.Counter = 1683 AND tblSubTestRequests.Project_Release = 1;