OStream Delete Last Record

[swalsh84]

I have an ostream recordset that is having a bear of a time doing something simple. I have a main recordset plus a detail recordset. When attempting to delete a detail record, I have tried just about everything.

I had a SP running to delete the detail record, but when you try to reload, there are connection conflicts and the application stalls and has to force quit. When you use the built in Access macros, the first and middle records can be deleted as well as the last, but when you try to delete the first or middle record then skip to the last, it nulls the fields and leaves the last record there, undeleted.

I've narrowed down the problem to this: for each record in a streaming recordset, a bookmark is assigned but unchanged when the number of records in the recordset changes. If you delete the first, the bookmarks for all of the other records remain the same. This poses a problem because you can never compare the last records bookmark value to the record count. I've determined that one has to write different code for each case, but the last record will still not delete. See code below so that this hopefully makes more sense.

Public Sub DeleteDetail()
    If Not bDev Then On Error GoTo PROC_ERR
    PushCallStack ("mdlMain - DeleteDetail")
    Dim sBookMark As String
    Dim rsClone As ADODB.Recordset
    Dim delLDID As Long
    
    Form_fsubLossTypeDetail.Refresh
    Set rsClone = Form_fsubLossTypeDetail.RecordsetClone
    delLDID = gLossDetailID
    Debug.Print rsClone.Bookmark
    Debug.Print rsClone.AbsolutePosition
    Debug.Print rsClone.RecordCount
    
    With rsClone
        If .AbsolutePosition = .RecordCount - 1 Then
            rsClone.MoveFirst
            Do While Not rsClone.EOF
                If rsClone!LossDetail_ID = delLDID Then
                    sBookMark = rsClone.AbsolutePosition
                    rsClone.Delete
                End If
                rsClone.MoveNext
            Loop
            
            If sBookMark > 1 Then
                Form_fsubLossTypeDetail.Bookmark = sBookMark - 1
            Else
                Form_fsubLossTypeDetail.Bookmark = sBookMark
                Set rsClone = Form_fsubLossTypeDetail.RecordsetClone
            End If
        Else
            DoCmd.RunCommand acCmdDeleteRecord
'            Form_fsubLossTypeDetail.Refresh
        End If
    End With
    
PROC_EXIT:
    Set rsClone = Nothing
    'rsClone.Close
    PopCallStack
    Exit Sub
PROC_ERR:
    GlobalErrHandler Err.Number, Err.Description, True, gCLATrackID, Erl
    Resume PROC_EXIT
End Sub

I'm not sure how to make this happen. I'm open to suggestions. Thanks.

[June7]

Try setting rsClone = Nothing before Setting it again in the Else. On second thought, that doesn't seem necessary.

Want to provide project for analysis?

[swalsh84]

No luck on setting the rs to nothing.

[June7]

Am wondering why using RecordsetClone. If you have the ID as criteria for records that need to be deleted, why not just a DELETE sql?

Again, do you want to provide project for analysis?

What is 'ostream'?

[swalsh84]

I can't post as it's a proprietary project and won't work if you had it any way. The access file is just a GUI for a SQL server app because IT won't let me have VS.

I'm using recordsetclone because the form recordset is not accessible when you are using a streaming recordset via ostream. This is the code used to create a streaming record set. What changes in the form, changes on the SQL server.

Public Function Clone(ByVal oRs As ADODB.Recordset, Optional ByVal LockType As ADODB.LockTypeEnum = adLockUnspecified) As ADODB.Recordset
    If Not bDev Then On Error GoTo PROC_ERR
    PushCallStack ("mdlFunctions - Clone")
    
    Dim oStream As ADODB.Stream
    Dim oRsClone As ADODB.Recordset
    
    'save the recordset to the stream object
    Set oStream = New ADODB.Stream
    oRs.Save oStream
    
    'and now open the stream object into a new recordset
    Set oRsClone = New ADODB.Recordset
    oRsClone.Open oStream, , , LockType
    
    'return the cloned recordset
    Set Clone = oRsClone
    
    'release the reference
    Set oRsClone = Nothing
    
PROC_EXIT:
    PopCallStack
    Exit Function
PROC_ERR:
    GlobalErrHandler Err.Number, Err.Description, True, gCLATrackID, Erl
    Resume PROC_EXIT

The original method was to use a stored procedure to delete the record in question. The procedure didn't fail, but when a different procedure that loads up the streaming recordset tried to run, it locked itself up even after trying to kill the connection and rekindle it.

I resorted to using the Access built in macros to delete and that's when I discover the last record issue above.

[June7]

Sorry, this is beyond my experience.