Help with VBA SQL recordset

[rickybid]

Hi Guys and Girls.

I just have a quick question. My VBA knowledge is ok, however I am not very good at programming with SQL.

I have a piece of code which works fine. It is just that it is not robust, I have created a loop with an string and open an recordset at the end of my loop I close the record set and in the beginning I open this again.

I think it could work faster and I hope you guys can help me with some other solutions.

here is my code

 c = 37   
Do Until Cells(c, 2).Value = Empty
        TeamMember = Cells(c, 2).Value
            
        r = 36
        For i = 4 To 34
            Days = Cells(r, i).Value
            
             strSQL = "SELECT [" & TeamMember & "] From [Planning$] Where [Days]=" & "#" & Format(Days, "mm/dd/yyyy") & "#" & ""
             rs.Open strSQL, DBConnection, adOpenDynamic, adLockOptimistic
             Cells(c, i).Offset(0, 0).CopyFromRecordset rs
             rs.Close
            Next i
        c = c + 1
    Loop

Thanks

 

[Fazza]

Hello

With the code working OK that is a good result.

How is it not robust?

Faster. To be able best advise needs, i think, some further understanding of what is being done is needed. Perhaps a single cross-tab query could give the required result. Or if multilple queries are required, a fraction of a speed gain can be via loading input worksheet data into an array (so a single reading of data from the worksheet instead of multiple ones as currently), and storing recordset results in an array until the end of the queries, and then loading this to the worksheet in a single operation instead of multiple as now. Bigger speed gains would be from changing the recordset approach, instead of multiple recordsets faster will be to open a connection and execute multiple queries with that connection. This is also ADO and not too different; just better suited for multiple queries.

regards, Fazza