Excel VBA: Insert a column in an Array from an ADODB Result Set

Question

I've queried an MS-Access database from within VBA, and returned the result set to an array, as follows:

Sub ChartData()

    Dim cn As Object
    Dim rs As Object
    Dim strSql As String
    Dim strConnection As String
    Set cn = CreateObject("ADODB.Connection")

    ' Hard code database location and name
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\server1\myDB.mdb"


    ' Construct SQL query
    strSql = "TRANSFORM Count(Names) AS CountOfNames SELECT Ticker FROM Pricing GROUP BY Ticker PIVOT Source; "

    ' execute the query, and return the results to the rs object
    cn.Open strConnection
    Set rs = cn.Execute(strSql)

    ' Copy the result set to an array
    Dim myArr() As Variant
    myArr = rs.GetRows


    ' Close the connection
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

    ...

End Sub

Next, I'd like to insert one column into myArr, which has dynamic dimensions. I've attempted to use ReDim Preserve for this purpose, but learned that ReDim Preserve will only allow changing the first dimension. For example, the following code results in the Run-time error, Subscript out of range:

Sub ChartData()

    ...

    Dim newRowCount As Integer
    Dim newColCount As Integer
    newRowCount = UBound(myArr, 2) + 1
    newColCount = UBound(myArr, 1) + 2


    ReDim Preserve myArr(newColCount, newRowCount) ' Run-time error here

End Sub

Is there an elegant way to work-around this ReDim Preserve limitation to insert a column without wiping the data?

Thanks!

Add the extra column up-front strSql = " SELECT p.*, null as AddedColumn FROM Pricing p " — Tim Williams
– Tim Williams, Commented Jan 14, 2016 at 1:15
Actually, my SQL query is a Crosstab Query. Question updated. However, I'm certainly open to solutions obtained by simply changing the SQL statement. — GNUser
– GNUser, Commented Jan 14, 2016 at 1:17
If you cant do it via the SQL then create a function which creates a new array with the required dimensions and copies the data over. — Tim Williams
– Tim Williams, Commented Jan 14, 2016 at 1:18

Parfait · Accepted Answer · 2016-01-14 02:26:30Z

1

Consider adjusting SQL query at the source as suggested by @TimWilliams in the comments which avoids memory overhead and use of manipulating data objects.

Crosstab queries use an aggregate groupby within its structure which you can easily add scalar values including empty, numeric, or string values (which will be the same across all rows):

TRANSFORM Count(Names) AS CountOfNames 
SELECT Ticker, NULL As EmptyColumn, 1 As AllOnesColumn, 'SQL in Excel' As AllStringColumn
FROM Pricing 
GROUP BY Ticker 
PIVOT Source;

Alternatively, save the crosstab query as a stored query object in Access database and run a typical select statement in Excel ADO connection referencing the stored query. Again, you can add scalar columns as needed:

SELECT storedquery.*, NULL As EmptyColumn, 1 As AllOnesColumn, 
       'SQL in Excel' As AllStringColumn
FROM storedquery

answered Jan 14, 2016 at 2:26

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GNUser Over a year ago

Elegant solution indeed! Works like a charm! Thank you. I should have emphasized more in the OP that I was open to solutions outside of VBA.

Collectives™ on Stack Overflow

Excel VBA: Insert a column in an Array from an ADODB Result Set

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related