How to use recordset efficientlly in Excel VBA

Posted on 2011-10-17
Medium Priority
Last Modified: 2012-05-12
Generally i don't work a lot with Excel VBA but I've been assigned a task to create some reports in excel using VBA...
Your help would be appreciate a lot....
Namelly, I should get data from SQL Server database, and my concept would be to get all data from xyz table from SQL Server in ADO recordset and then to disconect from the database...
In terms to get data in excel spreadsheet I would create different userforms in order to provide end users to define parameters which will be used to filter data in previously loaded recordset and then represented on worksheet itself...
Unfortunytelly, I was not able to find any good example how to pass parameters in recordset to get data filtered...

Here is clarification what I would like to achieve:

1. step load recordset like connect to SQL Server database and execute query "Select * from my_table" in order to populate recordset (ok this is not the issue)
2. step - After end user define parameters on the user form then I should execute query against recordset from step 1 which would look like "Select * from recordset where parameter1 = 'My_value' and parameter2 in ('My_value2',''My_value3')"

Can anyone help me in order to provide me with the example how to make this working (if possiblle)..

Thanks a lot...
Question by:alsam
LVL 85

Accepted Solution

Rory Archibald earned 2000 total points
ID: 36979472
You cannot execute a query against a recordset, but you can apply filters to it. Personally, I suspect it would be more efficient to requery the database after the parameters are set rather than loading the entire table into memory, especially if you have a lot of data.

Author Comment

ID: 36979573
Ok rorya,
I just wanted expert opinion about this....generally my goal was to release resources by disconecting end user from the database (or to limit to the minimum possibble connection attempts as possibble)...
Anyway, with your suggested approach then I should not have problems and no further assistance would be required....I'm going to leave behind my original idea...
Ok, I would leave this question open just for a short while (until tomorrow) in case someone else has to share experience regarding the issue...
Thanks for your time and effort....

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question