Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

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.

Avatar of Jeffrey Davidson
Jeffrey Davidson asked on
Microsoft AccessMicrosoft SQL ServerSQL
110 Comments1 Solution116 ViewsLast Modified:
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


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

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

ASKER CERTIFIED SOLUTION
Avatar of John Tsioumpris
John TsioumprisFlag of Greece imageIT Supervisor
Commented:
This problem has been solved!
Unlock 1 Answer and 110 Comments.
See Answers