Indexing /Sorting Array in Access 2003 vba: Large array with multiple columns

Access 2003

I have an array that has multiple columns (26)

After I have filled the array I want to specifically Index Columns  intArrayX(u, 6) and intArrayX(u, 7)
In DESC  order...

My data inside the array will be large amounts:  8000-10,000  rows

I know I can put these in a table and then let the query do the sorting for me , But I would like to know how to do this in a array routine.
and load the array again...
but would prefer code....using algorithym
BubbleSort will not work..

I have tried endlessly to find a  GOOD EXAMPLE on creating an index  for my type of array...
I can't find any !

Any additional help would be  greatly appreciated...!


ReDim intArrayX(1 To rsSql2.RecordCount, 26)
ReDim intArrayX(1 To rsSql2.RecordCount, 26)
u = u + 1
' start to add values to the array
intArrayX(u, 0) = rsSql2.Fields(0).Value ' Item-Grainger Sku
'intArrayX (u, 1)= rsSql2.Fields(1).Value ' NOUNPHRS1
'intArrayX (u, 2)= rsSql2.Fields(2).Value ' NOUNPHRS2
'intArrayX (u, 3)= rsSql2.Fields(3).Value ' NOUNPHR3
intArrayX(u, 4) = rsSql2.Fields(4).Value ' RichText
'intArrayX(u, 5) = "(found: " & wdFound & " )" ' wdfound nouns
intArrayX(u, 6) = rsSql2.Fields(6).Value 'score ' < ---------------SORTED BY Desc  score
intArrayX(u, 7) = rsSql2.Fields(7).Value ' COUNT FROM RANKLIST2  <-------------------...sorted by desc count
intArrayX(u, 8) = rsCust.Fields("fldDid").Value
intArrayX(u, 9) = rsSql2.Fields(9).Value ' WWGMFRNUM
intArrayX(u, 10) = rsSql2.Fields(10).Value ' WWGMFGNAME
intArrayX(u, 11) = rsSql2.Fields(11).Value '  DESC
intArrayX(u, 12) = rsSql2.Fields(12).Value  ' COMMENTS
intArrayX(u, 13) = rsSql2.Fields(13).Value ' REDBOOKNUM
intArrayX(u, 14) = rsSql2.Fields(14).Value ' XREF
intArrayX(u, 15) = rsSql2.Fields(15).Value ' SPIN
intArrayX(u, 16) = rsSql2.Fields(16).Value ' UOM
intArrayX(u, 17) = rsSql2.Fields(17).Value ' UOM QTY
intArrayX(u, 18) = rsSql2.Fields(18).Value ' SHIP
intArrayX(u, 19) = rsSql2.Fields(19).Value ' SHIP QTY
intArrayX(u, 20) = rsSql2.Fields(20).Value ' ALT1
intArrayX(u, 21) = rsCust.Fields("fldMfgname").Value
    intArrayX(u, 22) = rsCust.Fields("fldMfgnameOrig").Value
     intArrayX(u, 23) = rsCust.Fields("fldMfrnum").Value
   intArrayX(u, 24) = rsCust.Fields("fldMfrnumOrig").Value
  intArrayX(u, 25) = Trim(rsCust.Fields("fldDescription").Value)
intArrayX(u, 26) = rsCust.Fields("fldDescriptionOrig").Value

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

First what about this to shorten it up a bit:

ReDim intArrayZ(1 to rsSql2.RecordCount, 26)
for i = 0 to 20
  intArrayX(u,i) = rsSql2.Fields(i)
intArrayX(u, 21) = rsCust.Fields("fldMfgname").Value
intArrayX(u, 22) = rsCust.Fields("fldMfgnameOrig").Value
intArrayX(u, 23) = rsCust.Fields("fldMfrnum").Value
intArrayX(u, 24) = rsCust.Fields("fldMfrnumOrig").Value
intArrayX(u, 25) = Trim(rsCust.Fields("fldDescription").Value)
intArrayX(u, 26) = rsCust.Fields("fldDescriptionOrig").Value

You can't index an array.  You can sort an array.  That being the case, why don't the standard sort algorithms you mentioned work for you?  Alternatively, use an insertion sort.
FordraidersAuthor Commented:
Because I only need it sorted say... on 23 and 24 desc..

I guess I just dont understand how  they sort on just those two columns ?

most of the examples only have 2 columns.
As I have said in previous posts, why turn a very straightforward operation in Access into one of the most difficult operations outside of the RDBMS environment?  Like doing a tonsillectomy via the anus;-)
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

FordraidersAuthor Commented:
because I deal with large amounts of data and access bloats like a big fish. Until I have to force it to lose weight.

Iwould rather let memory take a chance than deal with a bloating fish.

If Access is bloating, it is usually because functions and subs are not properly terminated - closing objects, setting them to Nothing, or you work a lot with images - that sort of thing.  

I Google I entered - sorting arrays using vba - and got several hits, one of which was:
A better solution than a two dimensional array would be to use a structure perhaps a class and make an array of objects and then sort the array of objects.  In order to sort the array as is you will need to "swap" 26 elements of the array for each sort operation.

Leigh PurvisDatabase DeveloperCommented:
Can you describe the circumstances under which you're requiring this functionality?

You're loading data into an array.
You want to sort it.  But what are you then doing with that data?

You're opening a recordset - and using it to load the array.
The bloat of the database would only occur if you're then writing back that open data.  

Is your concern that you're maintaining a connection to the database while the recordset is open?
(That's not necessarily a bad thing).
If the data isn't for writing (which it couldn't be done directly from an array anyway) then the recordset being read only will give assurance that it's not being updated.
Just close and destroy your objects properly as Ray mentions.
If you don't want the connection to the database then use an ADO recordset (if you're not already) and disconnect it.  (You're then dealing with nothing but a memory structure in code... umm a bit like an array ;-)

FWIW when I have manipulation to perform on the set I always look to a recordset before an array.
(Even if the data in question isn't from a database - I'd build the recordset and fill it with whatever).
Sorting, and especially filtering, are then so efficient and trivial.  (As are creating copies of the whole thing to manipulate separately - i.e. Clones).

If you're determined then Tom Anselmo published his EZArray class in, I believe, the August edition of Access Advisor,
But of course you'd need to buy that / subscribe to see it.
FordraidersAuthor Commented:
I have 2 loops   1st rs is customer records(approx 50-60 records).
A variable is being created and passed to rsSql2
from the rsSql2 (is a query), I'am examining each record, at times there could be 3-7,000 records.
As each record is examined  2 fields are being updated from the rsSql2(bloat). I have to clear these fields out for next rsSql2

After each record is completed, it gets dumped into another table temporarely.

until all the records in rsSql2 are examined. this is where the bloating occurs(I think). ( and sorted) . I then append those temp records into another final table.  My goal , I want to , from the rsSql2 ,  put the records into the array after they are examined , sort the array , and then take the records from the array and place them in my final table. Loop to another  rs.

To avoid Updating fields in tables and then resetting those fields. I figure an array can do all that in memory to avoid having the database potentially having the database bloat to 2gig limit..

I did finally find a method to sort Descending.
Sub QuicksortD(ary, LB, UB, ref)
Dim M As Variant, temp
Dim i As Long, ii As Long, iii As Integer
i = UB
ii = LB
M = ary(Int((LB + UB) / 2), ref)
Do While ii <= i
Do While ary(ii, ref) > M
ii = ii + 1
Do While ary(i, ref) < M
i = i - 1
If ii <= i Then
For iii = LBound(ary, 2) To UBound(ary, 2)
temp = ary(ii, iii): ary(ii, iii) = ary(i, iii)
ary(i, iii) = temp
ii = ii + 1: i = i - 1
End If
If LB < i Then QuicksortD ary, LB, i, ref
If ii < UB Then QuicksortD ary, ii, UB, ref
End Sub

I plan on creating a 27th field and concatenate fields 6 and 7. and then use the function to sort on 27.



Leigh PurvisDatabase DeveloperCommented:
Can you elaborate on what the tables/fields actually are and what calculations you're performing and what you're writing back?

Is there a reason why you sort only *after filling the array?
Why sort at all?  Which data do you write back?

As a question about sorting an array I suppose you have your answer, but at the moment it feels very much like you're still employing a lot if extra overhead.  As I mentioned recordsets can be used for a lot of manipulation, or you could even use a table in a temp db - filling it and then execute all updates in one statement to your live data...
But without such details it's hard to offer anything more than conxeptual advice (not even that really).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Based on the vague description, I'm pretty sure that you can simplify the process by using SQL properly.  In addition, instead of sorting the array, just sort your recordset first.
FordraidersAuthor Commented:
used all suggestions...
still messing around with the array , but...using the updates to and outside  mdb... ..executing code from Frontend mdb...

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.