CODEWITHSUNDEEP
excelvba
Alexa
Alexa

Reputation: 49

Single ADO connection with multiple record sets

I have been trying to loop though an array, opening multiple record sets in teh same connected workbook.

The first iteration is fine, however up subsequent iterations, the record set is building upon the previous record sets.

I have tried erasing the array of data, closing the record set, setting a new record set, setting the record set to nothing, and even closing the connection and opening a new one.

It is still cumulative.

Dim connection As New ADODB.connection
Dim rs As New ADODB.Recordset

strPath = "path"
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPath & ";Extended Properties=""Excel 12.0;HDR=Yes;"";"
connection.Open strConn

Dim tarArr As Variant, tarElm As Variant, recsetElm as Variant
tarArr = Array("CW", "NW")

For Each tarElm In tarArr
    If tarElm = "CW" Then
        distroSht = "Capital Warfare"
    ElseIf tarElm = "NW" Then
        distroSht = "Northwest Pacific"
    End If

    strQuery = "SELECT Address FROM [" & distroSht & "$]"

    Set rs = New ADODB.Recordset
    rs.Open strQuery, connection
    recset = rs.GetRows

    For Each recsetElm In recset
        mailTo = mailTo & recsetElm & "; "
    Next recsetElm

    Erase recset
    rs.Close
    Set rs = Nothing
Next tarElm

Upvotes: 1

Views: 878

Answers (1)

alowflyingpig
alowflyingpig

Reputation: 738

Simply move the creation on the recordset within the loop so that when each iteration takes plac, the record set is created and removed when loop has finished.

Like this:

Dim connection As New ADODB.connection
Dim rs As ADODB.Recordset

strPath = "path"
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPath & ";Extended Properties=""Excel 12.0;HDR=Yes;"";"
connection.Open strConn

Dim tarArr As Variant, tarElm As Variant, recsetElm as Variant
tarArr = Array("CW", "NW")

For Each tarElm In tarArr

    If tarElm = "CW" Then
        distroSht = "Capital Warfare"
    ElseIf tarElm = "NW" Then
        distroSht = "Northwest Pacific"
    End If

    strQuery = "SELECT Address FROM [" & distroSht & "$]"

    Set rs = ADODB.Recordset
    rs.Open strQuery, connection
    recset = rs.GetRows

    For Each recsetElm In recset
        mailTo = mailTo & recsetElm & "; "
    Next recsetElm

    Erase recset
    rs.Close
    Set rs = Nothing
    mailTo = vbNullString

Next tarElm

Upvotes: 1

Related Questions