Named Range Query with ADODB


New Member
Sep 11, 2011

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.



Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Here is a copy of the function I have written that queries the names range "RngD" and generates an array of the values. I have another function that calls this function and gets the array into a datavalidation list for the cell the function is called from.

Again- changes to the range being queried are not reflected in repeated runnings of this script. The information stored in ArrayD is always the original information from the first execution of the script. Also I can not change the name of the range being queried without generating an error.


Public Function DataValidation(arg1 As Range)

Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim Pm As ADODB.Parameter
Dim Cm As ADODB.Command
Dim ConnString As String
Dim SQL As String
Dim qt As QueryTable
ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Greg\Documents\Test Charge Credit Name Data.xlsm ;Extended Properties=Excel 12.0;Persist Security Info=False"
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString

SQL = "Select * from RngD where PropID = '" & arg1 & "'"

''Set Cm = New ADODB.Command
''With Cm
'' .ActiveConnection = oCn
''.CommandText = SQL
''.CommandType = adCmdText

''Set Pm = .CreateParameter("Prop", adVariant, adParamInput)
''Pm.Value = arg1

''.Parameters.Append Pm

''Set oRS = .Execute
Set oRS = New ADODB.Recordset
oRS.Open SQL, oCn, 3, 3
''oRS.Source = SQL
''oRS.ActiveConnection = oCn

With oRS
i = .Fields.Count
j = .RecordCount
End With
ArrayD = oRS.GetRows(j)

''End With
''Set qt = Worksheets(5).QueryTables.Add(Connection:=oRS, _



If oRS.State <> adStateClosed Then
End If

If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing

DataValidation = WorksheetFunction.Transpose(ArrayD)

End Function
Upvote 0
This is killing me
I have tried the following:

Changing the query to reference the sheet that the named range is contained in
Changing the query to refer to the specific range on the sheet the data is on
Running the procedure as a sub rather than a function
Erasing the array at the end of the procedure so that no old info may be stored there

In all of these cases my results return data not contained in the table being queried.
If I delete or change records, these changes are not reflected in the new query.

I'm sure there is something fundamental I am missing here
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back