RagnarokX66
New Member
- Joined
- Sep 11, 2011
- Messages
- 36
Hello,
I'm working on a project wherein I want to be able to query a large dynamic table (10,000+ records) with an sql statement and return the results to an array that can be used in a data validation list. I've been running tests with a small sample list.
I found I'm able to write a script that will give a name to the range the table resides in. Once named, I can query the range with ADODB. I can then use the getrows method to return the record set to an array. Once in an array I can manipulate the data to return a one dimensional array that I can use in a data validation list via the vba Join function.
Anyway, I'm running into a couple of problems. 1- if I change any records in the named range, my query will not update. Also I find that if I change the name of the range in the query to a different named range (a named range as valid as the former) the query will not execute.
If anyone if familiar with these types of problems please help.
Thanks,
Ragnarok
I'm working on a project wherein I want to be able to query a large dynamic table (10,000+ records) with an sql statement and return the results to an array that can be used in a data validation list. I've been running tests with a small sample list.
I found I'm able to write a script that will give a name to the range the table resides in. Once named, I can query the range with ADODB. I can then use the getrows method to return the record set to an array. Once in an array I can manipulate the data to return a one dimensional array that I can use in a data validation list via the vba Join function.
Anyway, I'm running into a couple of problems. 1- if I change any records in the named range, my query will not update. Also I find that if I change the name of the range in the query to a different named range (a named range as valid as the former) the query will not execute.
If anyone if familiar with these types of problems please help.
Thanks,
Ragnarok