Access form associated with disabled ADODB.Recordset: save changes

I came here looking for the same answer as you after a ton of searching and trial and error. Finally, I was able to accomplish exactly what you are trying to do. I realize this is an old post, but I haven't seen any answers that actually gave an answer that would allow for what you are trying to do. I will use your example and try to apply what I had to change and add to make it work correctly.

Dim rs As ADODB.Recordset
Dim conn As ADODB.Connection

Private Sub Form_Load()
    If CurrentProject.Connection.State = adStateOpen Then CurrentProject.Connection.Close

    Set conn = New ADODB.Connection
    conn.Open CurrentProject.Connection.ConnectionString

    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open "select * from amsPor", conn, adOpenForwardOnly, adLockBatchOptimistic

    If Not rs Is Nothing Then
        If Not rs.ActiveConnection Is Nothing Then Set rs.ActiveConnection = Nothing
        If Not (rs.eof And rs.BOF) Then
            Set Me.Recordset = rs
        End If

        If conn.State = adStateOpen Then
            conn.Close
        End If
    End If

    Call AddNewRecord(Me.Recordset)
End Sub


Private Sub AddNewRecord(ByRef rs As ADODB.Recordset)
    On Error Resume Next 

    If Not rs Is Nothing Then
        If rs.Supports(adAddNew) Then
            rs.AddNew
            rs.Fields("FirstName").Value = "John"
            rs.Fields("LastName").Value = "Doe"

            If rs.Supports(adUpdate) Then rs.Update
        End If
    End If

    If Err.Number <> 0 Then
        Debug.Print "AddNewRecord Err Msg: " & Err.Description
        Err.Clear
    End If
End Sub


Private Sub Form_Unload(Cancel As Integer)
    Select Case MsgBox("Save changes ?", vbQuestion + vbYesNoCancel)
    Case vbYes
        Call UpdateDbWithRS(Me.Recordset)
    Case vbCancel
        Cancel = True
    Case Else
        ' Nothing.
    End Select
End Sub


Private Sub UpdateDbWithRS(ByRef rs As ADODB.Recordset)
    If Not rs Is Nothing Then
        If CurrentProject.Connection.State = adStateOpen Then CurrentProject.Connection.Close

        Set conn = New ADODB.Connection
        conn.Open CurrentProject.Connection.ConnectionString
        rs.ActiveConnection = conn

        If rs.Supports(adUpdateBatch) Then
            rs.UpdateBatch

            If Not conn Is Nothing Then
                If conn.State = adStateOpen Then conn.Close
                Set conn = Nothing
            End If
            If Not rs Is Nothing Then
                If rs.State = adStateOpen Then rs.Close
                Set rs = Nothing
            End If
        End If
    End If
End Sub

      

        
        
        
      

    

With the above code, I was able to Add a Record to my Recordset and make sure it doesn't show up in my database table. Then when I executed UpdateDbWithRS the Record that I added to the Recordset earlier was moved to my database table.

The biggest changes I had to make to your code was to change conn.Open CurrentProject.Connection

to conn.Open CurrentProject.Connection.ConnectionString

, add code If CurrentProject.Connection.State = adStateOpen Then CurrentProject.Connection.Close

to fix the error I was getting about a connection already opened. Then the last biggest change I made was replacing CursorType adOpenStatic

with adOpenForwardOnly

. I'm not sure if the last change is really necessary, but I used it based on a disabled RecordSet example I found on this Microsoft support site .