Forcing recordset to 2 dimensional array?

[Ken Puls]

Hi guys,

I've been using the following bit of code to retrieve a recordset from a database for a while, and it's worked great for me. Basically it stuffs a recordset into a variant array. If the recordset returns nothing, then it sets it to an empty 2 dimensional array, which is exactly what I need to test later.

Dim aryError(0, 0) As Variant

On Error Resume Next
GetRecordsetArray = CurrentRecordset(sSQL).GetRows
If Err.Number <> 0 Then
GetRecordsetArray = aryError
End If
On Error GoTo 0


Now, the issue I have is that because the array is a variant array, if I get a single record back, it creates a one dimensional array. The issue that I have is that trying to stuff a one dimensional array into my listbox fails, where the 2 dimensional works perfect.

I'm after one of two things here...
1) How would I force my single record to be placed in a two dimensional array
AND/OR
2) How do I elegantly test to see if the returned array is a one dimensional array so that I can write it into a two dimensional array myself.

Thanks!

[Ken Puls]

Okay, correction here...

My recordset code above actually always retrieves into a 2 dimensional array (unless no records are returned, which I've dealt with.)

My issue seems to lie that when the array is being passed back to the variant variable, it is being changed from a 2 dimensional array (with the second dimension empty) to a single dimensional. I'll reproduce this a bit more:

The class property (shown above) in full:
Property Get GetRecordsetArray(sSQL As String) As Variant
Dim aryError(0, 0) As Variant

On Error Resume Next
GetRecordsetArray = CurrentRecordset(sSQL).GetRows
If Err.Number <> 0 Then
GetRecordsetArray = aryError
End If
On Error GoTo 0

End Property
An extract from the calling routine:
Private Sub ufPopulateListbox()

Dim temparray() As Variant

'Populate listbox
With Me.lstBox
.Clear
temparray = Application.Transpose(db.GetRecordsetArray("My working SQL here"))
.List = temparray()
.ListIndex = -1
End With
End Sub

Now, the funny thing is that if I have a two dimensional array, this updates just fine, thank you. If it's one dimensional, nothing happens. No error, it just doesn't put the data in the listbox.

[Jacob Hilderbrand]

You can test the recordset.


rs.Fields.Count


Or you can test for the Ubound value of the second demension (which will error out when it doesn't exist).


x = UBound(GetRecordsetArray, 2)

[Ken Puls]

Thanks, Jake. I just finished working it out. :)

With regards to testing the recordset, the issue isn't there. The recordset returns a two dimensional array. The Property Get statement works, it's what happens when the array returned from the Property Get statement is returned to the Variant array in the calling procedure.

There's a couple of things of interest there. When it is passed back to the calling Variant, if there is a single record, it will be converted to a one dimensional array. Regardless of how many dimensions it has, it is converted from a 0 based array to a 1 based array.

Because of one dimensional array issue, I need to do my tests in the calling sub, unless someone can give me a way to call the array, ensuring that it will be two dimensions, but having no idea how many records. (I could return 0, 1, 2, etc...

What I've elected to do is a test in my calling procedure, although using a slightly different approach than you suggested. I basically used a variant to set to the first element of the array, assuming it has two dimensions. If it fails, I convert the single dimension into a two dimensional array. The code all follows. I have to say, it seems unnecessary. If I could force it to return two dimensions all the time, it would sure save some work.

Private Sub ufPopulateListbox()

Dim aryList() As Variant
Dim aryTemp() As Variant
Dim vElement As Variant
Dim lElement As Long

'Get array of data
aryTemp = Application.Transpose _
(db.GetRecordsetArray("My working SQL here"))

'Test for two dimensions
On Error Resume Next
vElement = aryTemp(1, 1)
If Err.Number <> 0 Then
'Must be one dimensional array, so convert to two
'dimensions
ReDim aryList(0, UBound(aryTemp))
For lElement = 0 To UBound(aryTemp) - 1
aryList(0, lElement) = aryTemp(lElement + 1)
Next lElement
Else
aryList = aryTemp
End If
On Error GoTo 0

'Populate listbox
With Me.lstBox
.Clear
.List = aryList
.ListIndex = -1
End With

End Sub

[TonyJollans]

Hi Ken,

The Transpose is causing your problem. I can see why it changes the array base - it is a Worksheet Function and worksheets are 1-based. I can't see why it should create a one dimensional array, andI don't think it should.

The good news is that you don't need it:
With Me.lstBox
.Clear
.Column = db.GetRecordsetArray("My working SQL here")
.ListIndex = -1
End With

[Ken Puls]

The Transpose is causing your problem. I can see why it changes the array base - it is a Worksheet Function and worksheets are 1-based. I can't see why it should create a one dimensional array, andI don't think it should.

Good stuff, Tony. :) Whether it should or not, I can tell you that it definately does both change to a 1 based array AND a one dimensional array.


The good news is that you don't need it: {snip}

Well, unfortunately, I actually do. The routine has grown, and I have to replace the -1/0 values in the array with a True/False for my userform. The great news, though, is that I solved it when testing my file on Excel 97.

I know that there was an issue with running the code on Excel 97. I think that the issue was around 97 not being happy with transposing the array, but I can't be sure. What I can say for sure is that I remembered this little function called "TransposeDim" that I have on an article on my site (http://www.excelguru.ca/node/23). As soon as I hit the error in 97, I went back to that, and low and behold... it transposes my two dimensional array beautifully!

So what I ended up with was this:
Private Sub ufPopulateListbox()

Dim aryList As Variant
Dim lElement As Long

'Get array of data
aryList = TransposeDim _
(db.GetRecordsetArray("My working SQL here"))

'Replace Separator values with True/False
For lElement = 0 To UBound(aryList)
aryList(lElement, 3) = CStr(aryList(lElement, 3))
Next lElement

'Populate listbox
With Me.lstBox
.Clear
.List = aryList
.ListIndex = -1
End With

End Sub

[TonyJollans]

I probably wasn't clear enough. Sorry.

If you use lstBox.Column instead of lstBox.List the array will effectively be transposed without you having to do it separately.

As for changing values, why not do it in the Query?

[Ken Puls]

I probably wasn't clear enough. Sorry.

I you sure I wasn't just being dense? ;)


If you use lstBox.Column instead of lstBox.List the array will effectively be transposed without you having to do it separately.

Does this work for multi column listboxes? Or would I need to do each column separately?


As for changing values, why not do it in the Query?

Uh... :o:... my SQL skills aren't good enough.

[TonyJollans]

I you sure I wasn't just being dense? ;)

It was late at night when you replied :)


Does this work for multi column listboxes? Or would I need to do each column separately?
Yes. No.


Uh... :o:... my SQL skills aren't good enough.
All sorts of ways, but something on the lines of

SELECT [Field1], [Field2], IIf([Field3]="Conditional","True","False"), [Field4]
FROM [TableName]
WHERE etc.;

[Ken Puls]

Okay... I'll have to give this a try. LOL!