VBA code slow to process. asynchronous fetch for sql statement?

I have been tasked with a project to display a list referenced to a specific criteria. I know I can do this in the local environment but choose to move to the vba side for simplicity and hopefully speed.

Problem is that its slow. I have 2 active x buttons on the "view" tab, The first one is to update the combo boxes by searching the data$ tab for unique values.

I figure that I could build a named range and reference that in the script instead of searching through the data set.

the second button is "show data" this displays the user selected criteria in a named range. I was reading that I could run an asynchronous fetch instead of synchronous fetch so that the code can move along and populate the named range. I was trying to search and find examples so that I could implement it but my familiarity with vba is limited and I am learning this as I go. I have only been dabbling in vba for about 3 years mostly using the macro recorder and then modifying the code to clean it up. I am also learning sql at the same time so please excuse any ignorance on my part.

The below code was borrowed and modified for my use. don't mind the 'strsql comments. these were left in after the borrowed code had to be debugged.

This is the code in module one that defines my constants for the connection and recordset

Please Login or Register  to view this content.

code for the functions on the view tab.

Please Login or Register  to view this content.

Hi and welcome to the forum.

The first problem I saw is that you're using ADO to query the active (open) workbook. Unfortunately, due to a bug, this causes a memory leak:
http://support.microsoft.com/kb/319998

Perhaps ADO can be put to one side and this can be done more simply? If you want to keep the solution entirely Excel-based, then perhaps you could achieve your goal by automating the advanced filter feature instead?