Sort contents of listbox

[mscertified]

I have a listbox with several columns. Each column has a label heading. I'd
like to be able to sort the listbox by clicking on a heading. I have coded
this and it works. The problem is it is VERY slow because I rebuild the
rowsource string and then do a requery and the query is very complex and
slow. Is there any way to sort just the listbox contents without doing a
requery???

 

[Klatuu]

No, you can't sort a list box any other way but with the row source.
However, when you change the row source, a requery is not necessary. It will
just run the same query again.

 

[Minton M]

I have a listbox with several columns. Each column has a label heading. I'd
like to be able to sort the listbox by clicking on a heading. I have coded
this and it works. The problem is it is VERY slow because I rebuild the
rowsource string and then do a requery and the query is very complex and
slow. Is there any way to sort just the listbox contents without doing a
requery???

The cheap and cheerful way to do this is to dump the results of your
query into a temporary Access table and then connect your rowsource
(with the appropriate sort) to the temp table. This will be blazingly
quick and uses the inherent way the listbox works. Simply create a
temp table with the same schema as the resultset from your query and
change your query to an append to make the magic happen.

-- James

 

[Klatuu]

I disagree, James.
I think adding a table would only add to the overhead. First, you have to
deelte all the records in the table, reload the table with an append query,
then load the list box again. In any case, it isn't necessary. I think the
performance hit the OP is getting is because he is running the same query
twice.

 

[mscertified]

No, I'm not doing the requery twice. I rely on changing the rowsource to
refresh the listbox. However, the query is complex with many joins and is
unacceptably slow in refreshing for the sorts.

 

[Klatuu]

Then I know of no way to improve performance.

 

[Minton M]

Then I know of no way to improve performance.

Dave,

I'm only suggesting that it would improve performance vs. running some
impossibly complex query that takes ages to process. If you have a
listbox with 100 rows and have to dump the data into a temp table,
it's not the prettiest solution but it's one way to improve the user
experience.

Alternatively:
- Perhaps it might be worth looking at why the original query takes so
long to run and see if there's any way to speed it up?
- Drop the data into a listview and use the column sorting in the
control you don't have to hit the DB every time on a sort.

It's only 10am and I've already spent all my two cents!

-- James

 

[Klatuu]

I think your 2 cents turned into a nickle.

Reviewing the query might be the best bet. Also, the OP should be sure
there are indexes on all the joined fields and fields used in the criteria.
Just using indexes can make a world of difference.

 

[John W. Vinson]

No, I'm not doing the requery twice. I rely on changing the rowsource to
refresh the listbox. However, the query is complex with many joins and is
unacceptably slow in refreshing for the sorts.

You do have appropriate indexes on the join, sort and criteria fields?

If so, then I might have to reluctantly agree with Minton's temp table
suggestion.

John W. Vinson [MVP]

 

[Klatuu]

Just for my own continuing education, John, explain to me how clearing a
table, loading a table, then loading the table into the list box is faster
than one query?
I just don't see it.

 

[Minton M]

Just for my own continuing education, John, explain to me how clearing a
table, loading a table, then loading the table into the list box is faster
than one query?
I just don't see it.

I can explain, thus boosting my contribution to a dime:

(a) Long query taking ~5-10 seconds to run.
(b) Delete temp, reload temp, use temp takes < 1 second.

Is (a) > (b)? It is! Therefore (b) is faster. Now of course, there's
the problem of mdb database float and all that jazz.

But I agree that it would be better to figure out why it's taking so
long in the first place. I'm thinking that my listview solution is
looking pretty sexy at this point. Or how about a little SQL Server
Express action for this fine Thursday morning?

 

[John W. Vinson]

Just for my own continuing education, John, explain to me how clearing a
table, loading a table, then loading the table into the list box is faster
than one query?
I just don't see it.

Well, it's not, especially if it's the same query. The question is perhaps one
of frequency/concurrency. If the temp table only needs to be up to the day,
you could create it in the Startup form's Load event and then just use it for
the rest of the Access session. If it needs to be up to the second, then
clearly you're right, optimizing the query is the only way to go (other than,
perhaps, rethinking the entire database schema).

John W. Vinson [MVP]