Is there an equivalent to RDO.OpenResultset in ADO ?

Question

Hello, all is in the title :-)

Some explanation :

In order to use a vb6 with C# (COM / Interop) I have a performance issue with Data Access. I don't know why but the code is four times slower in C# via Interop.

I'm trying to find a workaround and I would to replace the rdo by ADO to gain performance.

old code (with rdo) :

strSelect = _
        QUERY1 & ";" & _
        QUERY2 & ";" & _
        QUERY3 & ";" & _
        QUERY4 & ";" & _
        QUERY5 & ";" & _
        QUERY6

 'Fp.Cn is a rdoConnection
 Set Fp.rs = Fp.Cn.OpenResultset(strSelect)


'ComboBox 1
    Call LoadCombo(cboOne)
    Fp.rs.MoreResults
'ComboBox 2
    Call LoadCombo(cboTwo)
    Fp.rs.MoreResults
'ComboBox 3
    Call LoadCombo(cboThree)
    Fp.rs.MoreResults
'ComboBox 4
    Call LoadCombo(cboFour)
    Fp.rs.MoreResults
'ComboBox 5
    Call LoadCombo(cboFive)
    Fp.rs.MoreResults
'ComboBox 6
    Call LoadCombo(cboSix)
    Fp.rs.MoreResults
Fp.rs.Close

Now the code in LoadCombo :

Public Sub LoadCombo(ByRef cboComboBox As ComboBox, ByRef rslResultSet As rdoResultset)


 cboComboBox.Clear

    With rslResultSet
        While Not .EOF
            cboComboBox.AddItem .rdoColumns(1)
            cboComboBox.ItemData(cboComboBox.NewIndex) = .rdoColumns(0)
            .MoveNext
        Wend
    End With
End Sub

How to modify this code with ADO ?

Regards,

Florian

Answer 1

For your recordset, you would want to use the following code:

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set rs = New ADODB.Recordset

conn.Open "YourDSN", "username", "pwd" ' Or other appropriate conn string here

rs.Open strSelect, conn ' This is your OpenResultset equivalent

And then for each combobox, call

Call LoadCombo(cboOne, rs) 
Set rs = rs.NextRecordset  ' Instead of Fp.rs.MoreResults    
...

If you haven't already, you'll need to add the ADO reference to your project (probably Microsoft Active Data Objects 2.8)

Answer 2

This is the nearest equivalent in ADO:

' Fp.Cn is an active and open ADODB.Connection  '
' Fp.rs is an ADODB.Recordset object  '

Set Fp.rs = Fp.Cn.Execute(strSelect)