Microsoft Access Checkbox with SQL Backend Does Not Update

I’ve been researching it and have found many similar cases… but none exactly the same. I’ve tried a lot of different resolutions in the previous cases mentioned and none of them fixed this one.

I just recently did a SQL Server Migration and am now troubleshooting the issues that have sprung up with the new SQL back end. This is the last one that I can not figure out:

  • SqlDataAdapter.Fill method slow
  • Run MSsql Query something like foreach
  • Foreign key in one table have high read performance or another table with two primary key?
  • Using joins and if statements in SQL server 2012
  • How to check if a column exists in SQL Server table
  • how can i update SQL table logic
  • I have a split form with a check box control, bound to a SQL Server View through a DSN-Less connection. Before the migration the Bound Access Query could not be updated… so I programatically updated the forms checkboxes through VBA using two different methods:

    1. Was a check all/ check none checkbox which when clicked checked or unchecked all of the checkboxes in the datasheet.

    2. Gave the ability for the user to check or uncheck each individual checkbox by using the checkboxes Mouse_Down Event.

    Here is the code for each of the two methods:

        ' Check All/ Check None
        Dim rsSelect As DAO.Recordset
        Dim rsUpdate As DAO.Recordset
        Dim SQL As String
        Dim CurrDb As Database
        Dim currFilter As String
    
    On Error GoTo chkSelect_Click_Error
    
       ' Capture current filter
        If Me.FilterOn Then currFilter = Me.Filter
        Set rsSelect = Me.RecordsetClone
    
        Set CurrDb = CurrentDb
    
         rsSelect.MoveFirst
        Do While Not rsSelect.EOF
             SQL = "SELECT * FROM tblTimesheet WHERE [TimesheetID] = " & rsSelect("TimesheetID")
            Set rsUpdate = CurrDb.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
            If Not rsUpdate.EOF Then
                If Me.chkSelect Then
                    With rsUpdate
                        .Edit
                        rsUpdate("TimesheetSelect") = True
                        .Update
                    End With
                Else
                     With rsUpdate
                        .Edit
                        rsUpdate("TimesheetSelect") = False
                        .Update
                    End With
                End If
            End If
            rsSelect.MoveNext
         Loop
        rsUpdate.Close
        rsSelect.Close
        Me.Requery
        If currFilter > "" Then
            Me.Filter = currFilter
            Me.FilterOn = True
        End If
        If Me.chkSelect Then
            Me.lblSelect.Caption = "Select None"
        Else
            Me.lblSelect.Caption = "Select All"
        End If
    
       On Error GoTo 0
       Exit Sub
    
    chkSelect_Click_Error:
    
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure chkSelect_Click of VBA Document Form_frmTimesheetSummary"
    

    And Secondly:

        ' Check/ Uncheck Individual Checkbox
        Dim rsUpdate As DAO.Recordset
        Dim SQL As String
        Dim CurrDb As Database
        Dim currFilter As String
    
       ' Capture current filter
        If Me.FilterOn Then currFilter = Me.Filter
    
        Set CurrDb = CurrentDb
         SQL = "SELECT * FROM tblTimesheet WHERE [TimesheetID] = " & Me.TimesheetID
        Set rsUpdate = CurrDb.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
        If Not rsUpdate.EOF Then
            If Me.TimesheetSelect Then
                With rsUpdate
                    .Edit
                    rsUpdate("TimesheetSelect") = False
                    .Update
                End With
            Else
                 With rsUpdate
                    .Edit
                    rsUpdate("TimesheetSelect") = True
                    .Update
                End With
            End If
        End If
    
    
        rsUpdate.Close
        Me.Form.Requery
        'Me.Repaint
        Me.Refresh
        If currFilter > "" Then
            Me.Filter = currFilter
            Me.FilterOn = True
        End If
    

    Both of these procedures worked with an Access back end… but the “Check Individual” procedure refuses to work now. When I check a checkbox it does update the SQL Backend… but the control itself refuses to update the new status… I of course have tried Requery, but also Repaint and Refresh and it refuses to update unless I completely close the form and reopen it again.

    The real kicker in all this is that the Check All method still works! I’ve spent hours on this and am hoping to get some fresh eyes on it because it should be working if the backend is updating!!

    ADDITIONAL NOTES ADDED LATER: In response to some of the great reasoning below I feel I should include this additional notes:

    1. I am using SQL Server 2012 and the SQL Server Native Client 11.0 Driver for my connection string.

    2. I am using Microsoft Access 2010 32 bit

    3. The SQL Server field is a bit I’ve removed all nulls and set allow nulls to ‘no’ with a default of 0

    3 Solutions collect form web for “Microsoft Access Checkbox with SQL Backend Does Not Update”

    Some things come to mind:

    1) You can replace the whole rsUpdate construction in the second procedure by this:

    SQL = "UPDATE tblTimesheet SET TimesheetSelect = " & _
          IIf(Me.TimesheetSelect, "0", "-1") & _
          " WHERE TimesheetID = " & Me.TimesheetID
    CurrDb.Execute SQL, dbSeeChanges
    

    It depends on your TimesheetSelect datatype whether you should use “-1” or “1”.

    2) Me.Form.Requery should be Me.Requery.

    3) If it still doesn’t work, adding a TIMESTAMP column to tblTimesheet might help Access recognize that the record was changed. This is generally a good thing to have, but shouldn’t be necessary.

    Recommended reading: https://stackoverflow.com/a/2861581/3820271
    tblTimesheet does have a primary key, doesn’t it?

    If Andre’s suggestions don’t solve it, instead of Me.Requery, try resetting the form’s recordsource. Me.RecordSource = .
    You don’t say what version of SQL Server you are using or which ODBC driver. Make sure you are using the correct ODBC Driver for your version of SQL Server and not the default ‘SQL Server’ driver.

    Thanks to all who posted… the answer that ended working for me in this case was ditching the split form… I had heard it suggested that split forms can be problematic at times so redesigned the form in a standard Parent/ Subform setup which completely resolved the issue.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.