Sorting Algorithms in VBA?

K

Kosio

I am creating a form that goes through tables and displays certain
records in a listbox based on a sertain search criteria. I would like
to do this in VBA if it is possible instead of using queries since I
would like to manipulate the listbox in which the data is displayed.

I understand that I could create a listbox that uses a query to
populate it's rowsource and then copy that data over to a listbox that
has a value rowsource, but I would like to avoid using multiple
listboxes on my form.

Is there a way for me to sort a table/recordset in VBA using a
heap-sort per say and then use a binary search on the record (since
it's in order), to find my desired record.

I thought it would go something like:

Dim dbDatabase as DAO.Database
Dim rsRec as DAO.Recordset
Dim intI as integer

set dbDatabse = CurrentDb
set rsRec = dbDatabse.OpenRecordset("Table Name")

If Not rsRec.EOF then
rsRec.MoveLast
rsRec.MoveFirst
endif

Now I would like be able to use a heap sort algorithm with rsRec as my
array. Will anything I do to this record affect the way it is stored
in Access?
 
K

Kosio

I know in a table you can set which field you wish to sort by, when I
open the recordset, will they stay in that sorted order?
 
A

Albert D.Kallal

You can sort the results by changing

set rsRec = dbDatabse.OpenRecordset("Table Name")

to

set rsRec = dbDatabse.OpenRecordset("select * from [Table Name] order by
LastName")
 
D

Dirk Goldgar

Kosio said:
I am creating a form that goes through tables and displays certain
records in a listbox based on a sertain search criteria. I would like
to do this in VBA if it is possible instead of using queries since I
would like to manipulate the listbox in which the data is displayed.

I understand that I could create a listbox that uses a query to
populate it's rowsource and then copy that data over to a listbox that
has a value rowsource, but I would like to avoid using multiple
listboxes on my form.

Is there a way for me to sort a table/recordset in VBA using a
heap-sort per say and then use a binary search on the record (since
it's in order), to find my desired record.

I thought it would go something like:

Dim dbDatabase as DAO.Database
Dim rsRec as DAO.Recordset
Dim intI as integer

set dbDatabse = CurrentDb
set rsRec = dbDatabse.OpenRecordset("Table Name")

If Not rsRec.EOF then
rsRec.MoveLast
rsRec.MoveFirst
endif

Now I would like be able to use a heap sort algorithm with rsRec as my
array. Will anything I do to this record affect the way it is stored
in Access?
Click to expand...

I'm not at all sure I follow you. Any sorting of a recordset that you
do in memory using VBA is likely to be substantially slower than just
specifying the sort order for the recordset when you open it, as in ...

set rsRec = dbDatabse.OpenRecordset( _
"SELECT * FROM Table Name " & _"
ORDER BY This, That, TheOther DESC")

The database engine can use indexes, if they are available, to speed the
sort. And if you want to sort the same recordset multiple times in
different orders, you can set the recordset's Sort property and open
another recordset from the first one to get the records sorted in the
specified order.

Further, if you are looking for a particular record, specifying the
criteria necessary to find that record at the time you query the table
is probably going to be faster than doing your own search, even if you
sort the recordset and make it a binary search. Again, defining indexes
on the appropriate fields in the table has a major impact on the speed
of such a query.

I recognize that this isn't exactly the answer you were looking for.
There's code posted out on the web to sort an array using various
algorithms. You can't really move the records around in a recordset,
but you could -- if you really want to -- load an array with the keys
and the Bookmark properties of the records in the array, then sort that
array and use the bookmarks to position the recordset to the record you
want. It seems to me like the wrong way to go about it, though.
 
K

Kosio

Thanks for your help, didn't realize you could specify a criteria when
you opened a recordset from a table. I thought I could merely open the
table recordset and then I would be forced to implement my own
searching/sorting algorithms based on the table recordset.
 
D

Dirk Goldgar

Kosio said:
Thanks for your help, didn't realize you could specify a criteria when
you opened a recordset from a table. I thought I could merely open
the table recordset and then I would be forced to implement my own
searching/sorting algorithms based on the table recordset.
Click to expand...

That explains why you were asking what you were asking. If you have
further questions, feel free to post back.
 
Top