Sort RecordSet

Jon von der Heyden

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,912
Office Version
  1. 365
Platform
  1. Windows
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
CDEFG
1Nums1Nums2Chars1Chars2
267.5965.91OE
383.0536.54KV
451.1922.97JC
568.3195.54WN
646.1673.81JF
752.7819.21TJ
856.7312.36WR
926.9576.4SU
1012.2420.59RL
114.3249.01MC
124.3245.93SX
1389.1530.43TR
1433.1466.45DO
1517.3419.41DV
1692.3476.83VM
...
Excel 2003



The code:
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?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Jon

Something like this perhaps :-

Code: 
MyCheck = VarType(Range("C2"))

and then translate accordingly in your field statement.

hth

Mike
 
Upvote 0
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.
 
Upvote 0
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?
 
Last edited:
Upvote 0
Jon

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

Perhaps that will solve it.

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

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("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...
 
Upvote 0
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 :(
 
Upvote 0
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...
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Operational Risk Methodology on VBA
Replies
0
Views
85
Mazurofgod
M
Z
  • Solved
VBA: Advanced Filter Limitations
Replies
3
Views
635
zero269
Z
I
  • Question Question
Listbox not being populate when code is run
Replies
4
Views
643
ipbr21054
I
I
  • Solved
Table not sorting A-Z when code run
Replies
3
Views
479
ipbr21054
I
I
  • Question Question
Sort table but not a fixed rangeas whats currently in use
Replies
0
Views
365
ipbr21054
I

Forum statistics

Threads
1,223,990
Messages
6,175,816
Members
452,672
Latest member
missbanana

Share this page

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 MrExcel.com.
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 "mrexcel.com".
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
Back
Top