Excel VBA - Loop through recordsets ADODB with For Loop Error - Help

Hi all,

Basically, to reduce overhead and repetitive code, I need to loop through SQL query definitions 10 times. I have ten unique recordset.source strings. With each query, fields are pulled to userform... BUT I cannot seem to get the for loop to allow this. I get error "Operation is not allowed when the object is closed"... .. .. .. .. . . . Its not closed. Im opening it every time.

Please see my code below:

Please Login or Register  to view this content.

Thank you for the help!

Jiindon,

Same error.

But I did not follow your last recommendation. The closeconnection line name must close the recordset and connection. It is also out of the loop so its not suspect to be any issue.

The code works find without a loop, i should have mentioned.

I still receive the same error.

Thanks for your help with this.

Please Login or Register  to view this content.

You seem to using the same connection string but different data source, so get

Please Login or Register  to view this content.

to outside of the loop. (before the loop) then apply the changes I suggested.

Which line is the error occurring on? Can you step thru the macro to test and update us (use F8 to step thru the macro)

Hi,

Thank you both for your replies and concerns with my problem.

So I was able to get the code to work using Jindon's suggestions:

Please Login or Register  to view this content.

I realize now that I am seeing clearly, i was asking the code to close the recordset and connection despite the connection and recordset already being closed in the end of the For Loop, thus "Operation can't be completed when object is close".

Thanks for the help with this. Much better code than what I had before. Faster!!

Good to know it worked and thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Jindon,

I will confirm that I have no issues this evening. Then solve the thread.

Thank you