Sort RecordSet

[Jon von der Heyden]

Hi All

I'm toying with sorting arrays using ADO recordsets... I know there are other ways of processing arrays but for now I'm just trying to understand how to process recordsets...

No problem sorting numbers, but I have trouble sorting text. The code below works a treat but if I substitute the values in my array with e.g. single characters A-Z then I hit a snag...

Example table that feeds arrays:

Excel Workbook
	C	D	E	F	G
1	Nums1	Nums2		Chars1	Chars2
2	67.59	65.91		O	E
3	83.05	36.54		K	V
4	51.19	22.97		J	C
5	68.31	95.54		W	N
6	46.16	73.81		J	F
7	52.78	19.21		T	J
8	56.73	12.36		W	R
9	26.95	76.4		S	U
10	12.24	20.59		R	L
11	4.32	49.01		M	C
12	4.32	45.93		S	X
13	89.15	30.43		T	R
14	33.14	66.45		D	O
15	17.34	19.41		D	V
16	92.34	76.83		V	M
...

Excel 2003



The code:

[COLOR=blue]Public[/COLOR] [COLOR=blue]Sub[/COLOR] phoo2()
    [COLOR=blue]Dim[/COLOR] arr()
    [COLOR=blue]Dim[/COLOR] lngArrItem [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
    [COLOR=blue]Dim[/COLOR] recData [COLOR=blue]As[/COLOR] ADODB.Recordset
 
    arr = Range("C2:D16")
    [COLOR=blue]Set[/COLOR] recData = [COLOR=blue]New[/COLOR] ADODB.Recordset
 
    [COLOR=blue]With[/COLOR] recData
        [COLOR=blue]With[/COLOR] .Fields
            .Append "Field1", adDouble
            .Append "Field2", adDouble
        [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
        .Open
        [COLOR=blue]For[/COLOR] lngArrItem = [COLOR=blue]LBound[/COLOR](arr) [COLOR=blue]To[/COLOR] [COLOR=blue]UBound[/COLOR](arr)
            .AddNew
            recData!Field1 = arr(lngArrItem, 1)
            .Update
            recData!Field2 = arr(lngArrItem, 2)
            .Update
        [COLOR=blue]Next[/COLOR] lngArrItem
        .Sort = "Field1,Field2"
        arr = Application.Transpose(.GetRows())
    [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
 
    Range("K2").Resize([COLOR=blue]UBound[/COLOR](arr), 2) = arr
 
    [COLOR=blue]Set[/COLOR] recData = [COLOR=blue]Nothing[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

So if I change the range that loads the array to Range("F2:G16") then I need to change the field data types to reflect. The problem is I don't know what data type to choose and when I choose e.g adVariant then I get an error on the sort line 'Sort order cannot be applied'.

The data types don't correspond with TypeNames to I can't use that.

Any ideas?

 

[ukmikeb]

Jon

Something like this perhaps :-

MyCheck = VarType(Range("C2"))

and then translate accordingly in your field statement.

hth

Mike

 

[Jon von der Heyden]

VarType returns 8 which is string, but there is no adString data type for recordset field datatype. I have experiment with several of them that I thought might work but no luck as yet.

 

[Richard Schollar]

Try field type of adBSTR

If that doesn't work, try adVarChar

 

[Jon von der Heyden]

Hi

adBSTR fails on the sort line with err desription 'Sort order cannot be applied'.

adVarChar fails on the Append line with err description 'Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another'.

So adBSTR suggests that it is an acceptable data type but I don't understand why it can't sort it. adVarChar seems to be an incorrect data type but then I don't really understand what that err description means.

Edit: It doesn't appear that any data type works so perhaps I'm missing something else?

 

[ukmikeb]

Jon

In "Access 2003 Programming by Example" by Julitta Korol it mentions a Text datatype - adVarWChar.

Perhaps that will solve it.

Mike

 

[Jon von der Heyden]

Nailed it! adVarChar does work, but need to pass 3rd argument DefineSize:

[COLOR=blue]Public[/COLOR] [COLOR=blue]Sub[/COLOR] phoo2()
    [COLOR=blue]Dim[/COLOR] arr()
    [COLOR=blue]Dim[/COLOR] lngArrItem [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
    [COLOR=blue]Dim[/COLOR] recData [COLOR=blue]As[/COLOR] ADODB.Recordset
 
    arr = Range("F2:G16")
    [COLOR=blue]Set[/COLOR] recData = [COLOR=blue]New[/COLOR] ADODB.Recordset
 
    [COLOR=blue]With[/COLOR] recData
        .CursorLocation = adUseClient
        [COLOR=blue]With[/COLOR] .Fields
            .Append "Field1", adVarChar, 255
            .Append "Field2", adVarChar, 255
        [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
        .Open
        [COLOR=blue]For[/COLOR] lngArrItem = [COLOR=blue]LBound[/COLOR](arr) [COLOR=blue]To[/COLOR] [COLOR=blue]UBound[/COLOR](arr)
            .AddNew
            recData!Field1 = arr(lngArrItem, 1)
            .Update
            recData!Field2 = arr(lngArrItem, 2)
            .Update
        [COLOR=blue]Next[/COLOR] lngArrItem
        .Sort = "Field1,Field2"
        arr = Application.Transpose(.GetRows())
    [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
 
    Range("K2").Resize([COLOR=blue]UBound[/COLOR](arr), 2) = arr
 
    [COLOR=blue]Set[/COLOR] recData = [COLOR=blue]Nothing[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

Thanks Richard , and thanks for your efforts too Mike.

I think this is actually a tasty way of sorting arrays...

 

[Richard Schollar]

Did you try all the alternatives? Such as adWChar or adVarWChar?

Apparently you can't sort a recordset on a adBSTR field

I would have thought the adVarChar would have worked

 

[Richard Schollar]

Hmm, I took too long to post there!

It's OK I suppose except that you haver to create a recordset, upload the values and then sort. You could do the same thing on a sheet...