How to use recordset efficientlly in Excel VBA

Posted on 2011-10-17
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

    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.
    LVL 1

    Author Comment

    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....

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Title # Comments Views Activity
    25th of every month 7 38
    Copy paste as values 4 33
    IF OR formula Excel - multiple conditions 9 39
    Excel 2010 question 3 23
    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now