Why we need to use more than one recordset in a app?

[seema_s]

Hi,

Could somebody explain me the use and advantage of using more recordsets in an application.

[Hack]

Typically I will use one Public recordset throughout an application.

However, there are times when I need a second recordset for a specific issue. During those times, I will create an event level recordset, do what I need to do, then destroy it.

Often you may need certain elements of one recordset as variables in another query which would generate another recordset. Once you are done with both, then the temp recordset gets destroyed and you continue on with your primary recordset.

[dee-u]

One Recordset may hold records from one table and another one holds records from a different table and you may need to perform some processing involving those records from the tables involved.

[ganeshmoorthy]

that depends your logic, comfortability and style...this is not mandatory to have one recordset in your app. Having so many recordsets also will cause problems if not closed properly...

[dee-u]

At best you may just instantiate one when you need it then properly dispose of it afterwards like the ff...

Private Sub SetContainerGrid(Optional ByVal ControlNum As String = vbNullString)
    Dim adoRecordset    As ADODB.Recordset
    Dim strSQL          As String
    Dim li              As ListItem
    
    strSQL = "SELECT ContainerNum, ContainerSize, ID FROM BrokerageContainers WHERE ControlNum = '" & ControlNum & "'"
    
    Set adoRecordset = New ADODB.Recordset
    lvwContainers.ListItems.Clear
    With adoRecordset
        .Open strSQL, connImport, adOpenKeyset, adLockReadOnly
        Do While Not .EOF
            Set li = lvwContainers.ListItems.Add(Text:=.Fields("ContainerNum") & vbNullString)
            li.Tag = .Fields("ID")
            li.ListSubItems.Add Text:=.Fields("ContainerSize") & vbNullString
            .MoveNext
        Loop
        .Close
    End With
    Set adoRecordset = Nothing
End Sub