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 Solution142 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

Open 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

Open 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

ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 110 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 110 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros