I'm running an Access 2016 Front End and a SQL Server 2014 back end. I'm unable to Update Access Date fields in my project.

I can't seem to get Access and SQL to communicate when it comes to Dates. The project I'm working on was formerly an All Access system. The project has evolved over the years and has many forms, reports, queries, macros, vba codes, tables ...etc.

A few of the situations that I need some help with are:
1. Using Access Update queries to work, again regarding dates, in both systems.
2. Using Access Update queries to work, again regarding dates, in both systems with respect to subforms that are populated with associated tables for Access/SQL Server.

I imported all of my Access tables into SQL Server using the SSMS Import tool. I then linked the Access tables and removed the schema name from them.

I will show a few of my attempts at doing this:

Private Sub AddRelease_1()
Dim StepsTaken As String
Dim dbs As DAO.Database
Dim RS As DAO.Recordset
Dim SQL As String
Dim Counter As Integer
Set dbs = CurrentDb


On Error GoTo ErrorHandler
Counter = Forms!frmTestRequests_Main![txtCounter].value


If (Forms!frmTestRequests_Main![chkAnalytical] = -1) Then       '-1 = True
    'update fields
    Forms![frmTestRequests_Main]!txtProject_Release = Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![Project_Release]
    'Do we need AddNew or Update?
    'If a record exists where Release is 1 and Counter is the current counter; then we need to Update
    SQL = "SELECT tblTestRequests.Counter, tblTestRequests.Project_Release FROM tblTestRequests WHERE tblTestRequests.Counter = " & Counter & " AND tblTestRequests.Project_Release = " & 1
    Set RS = CurrentDb.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
    If RS.RecordCount > 0 Then              'The record is not in the table
    RS.Close
            SQL = ""
            SQL = SQL & "Update rdLab.tblTestRequests SET "
            SQL = SQL & "tblTestRequests.Project_Request = " & Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![Project_Request] & ","
            SQL = SQL & "tblTestRequests.IsRelease_Valid = " & Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![IsRelease_Valid] & ","
            SQL = SQL & "tblTestRequests.ProjectName = '" & Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![ProjectName] & "',"
            SQL = SQL & "tblTestRequests.Objective = '" & Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![Objective] & "',"
            SQL = SQL & "tblTestRequests.Project_Notification_Date = #" & Format(Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![Project_Notification_Date], "mm/dd/yyyy") & "#,"
            SQL = SQL & "tblTestRequests.Target_Completion_Date = #" & Format(Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![Target_Completion_Date], "mm/dd/yyyy") & "#,"
            SQL = SQL & "tblTestRequests.Strategic_Bucket = '" & Forms![frmTestRequests_Main]![txtStrategic_Bucket] & "',"
            SQL = SQL & "tblTestRequests.Requestor = '" & Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![Requestor] & "',"
            SQL = SQL & "tblTestRequests.Requesting_Group = '" & Forms!
[frmTestRequests_Main]![frmSubTestRequests_Subform].Form![Requesting_Group] & "',"
            SQL = SQL & "tblTestRequests.ECR_Number = '" & Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![ECR_Number] & "',"
            SQL = SQL & "tblTestRequests.QBB_Test_Tracker = " & Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![QBB_Test_Tracker] & ","
            SQL = SQL & "tblTestRequests.Results_Folder = '" & Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![Results_Folder] & "',"
            SQL = SQL & "tblTestRequests.Project_Status = '" & Forms![frmTestRequests_Main]![cboNewProjectStatus].Column(1) & "',"
            SQL = SQL & "tblTestRequests.Project_Status_Notes = '" & Forms![frmTestRequests_Main]![txtProjectStatus_Notes] & "',"
            SQL = SQL & "tblTestRequests.Processed_Release = " & -1 & " "
            SQL = SQL & "From rdLab.tblTestRequests "
            SQL = SQL & "WHERE rdLab.tblTestRequests.Counter = " & Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![Counter] & " "
            SQL = SQL & "AND rdLab.tblTestRequests.Project_Release = 1;"
       
        'Perform Update
            'dbs.Execute SQL, dbFailOnError + dbSeeChanges
            DoCmd.SetWarnings False
            DoCmd.RunSQL SQL
            DoCmd.SetWarnings True
           
            Forms!frmTestRequests_Main.Form.Requery
            Forms!frmTestRequests_Main.Form.Refresh
            Call modCode.RequeryOpenForms

Select allOpen in new window

here is another:

    End If
        SQL = ""
        SQL = SQL & "Update tblSubTestRequests SET "
        SQL = SQL & "tblSubTestRequests.Project_Request = " & Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![Project_Request] & ","
        SQL = SQL & "tblSubTestRequests.IsRelease_Valid = " & Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![IsRelease_Valid] & ","
        SQL = SQL & "tblSubTestRequests.ProjectName = '" & Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![ProjectName] & "',"
        SQL = SQL & "tblSubTestRequests.Objective = '" & Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![Objective] & "',"
        SQL = SQL & "tblSubTestRequests.Project_Notification_Date = #" & Format(Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![Project_Notification_Date], "mm/dd/yyyy") & "#,"
        SQL = SQL & "tblSubTestRequests.Target_Completion_Date = #" & Format(Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![Target_Completion_Date], "mm/dd/yyyy") & "#,"
        SQL = SQL & "tblSubTestRequests.Requestor = '" & Forms![frmTestRequests_Main]![txtRequestor] & "',"
        SQL = SQL & "tblSubTestRequests.Requesting_Group = '" & Forms![frmTestRequests_Main]![txtRequesting_Group] & "',"
        SQL = SQL & "tblSubTestRequests.ECR_Number = '" & Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![ECR_Number] & "',"
        SQL = SQL & "tblSubTestRequests.QBB_Test_Tracker = " & Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![QBB_Test_Tracker] & ","
        SQL = SQL & "tblSubTestRequests.Results_Folder = '" & Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![Results_Folder] & "',"
        SQL = SQL & "tblSubTestRequests.Project_Status = '" & Forms![frmTestRequests_Main]![cboNewProjectStatus].Column(1) & "',"
        SQL = SQL & "tblSubTestRequests.Project_Status_Notes = '" & Forms![frmTestRequests_Main]![txtProjectStatus_Notes] & "',"
        SQL = SQL & "tblSubTestRequests.Processed_Release = " & -1 & " "
        SQL = SQL & "From rdLab.tblSubTestRequests "
        SQL = SQL & "WHERE tblSubTestRequests.Counter = " & Forms![frmTestRequests_Main]![frmSubTestRequests_Subform].Form![Counter] & " "
        SQL = SQL & "AND tblSubTestRequests.Project_Release = 1;"
    'Perform Update
    'dbs.Execute SQL, dbFailOnError + dbSeeChanges
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQL
    DoCmd.SetWarnings True
   
    RS.Close
    dbs.Close
    Set dbs = Nothing

Select allOpen in new window

The above are code snippets.
The "Main Form" of the program requires that I run an AddNew query, this always works. The subform/subtable requires that I Update (or Edit) the existing records in the subtable, this always fails. I know it fails because it doesn't update the subtable fields I told it to.

I hope that is enough information for someone to help me. The other issue is that I'm on a pretty tight timeframe to get this project completed.

Any ideas?

Thanks,
Jeff