Fordraiders
asked on
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..
MergeSort
or
Quicksort
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...!
Thanks
fordraiders
ReDim intArrayX(1 To rsSql2.RecordCount, 26)
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..
MergeSort
or
Quicksort
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...!
Thanks
fordraiders
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;-)
ASKER
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.
Iwould rather let memory take a chance than deal with a bloating fish.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
--
JimFive
--
JimFive
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, http://my.advisor.com/doc/19097.
But of course you'd need to buy that / subscribe to see it.
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, http://my.advisor.com/doc/19097.
But of course you'd need to buy that / subscribe to see it.
ASKER
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
Loop
Do While ary(i, ref) < M
i = i - 1
Loop
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
Next
ii = ii + 1: i = i - 1
End If
Loop
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.
??
Thanks
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
Loop
Do While ary(i, ref) < M
i = i - 1
Loop
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
Next
ii = ii + 1: i = i - 1
End If
Loop
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.
??
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
--
JimFive
--
JimFive
ASKER
Thanks
used all suggestions...
still messing around with the array , but...using the updates to and outside mdb... ..executing code from Frontend mdb...
Thanks
used all suggestions...
still messing around with the array , but...using the updates to and outside mdb... ..executing code from Frontend mdb...
Thanks
ASKER
I guess I just dont understand how they sort on just those two columns ?
most of the examples only have 2 columns.