Access Cookbook

Sort an Array in Access Basic

Problem

Although it’s a database product, Access doesn’t include a way to sort an array. You need to present sorted arrays in an application, and you can’t find a reasonable way to sort them without first saving them to a table. You know you’ve seen array-sorting methods in other languages. Can you write a sorting routine that executes quickly?

Solution

It’s true that Access doesn’t provide a built-in sorting mechanism for arrays. Entire volumes in libraries are devoted to the study of various sorting and searching algorithms, but it’s not necessary to dig too deep for array-sorting methods for Access. Because you’ll probably place any large data sets into a table, most arrays in Access aren’t very large. Therefore, almost any sort will do. This solution uses a variant of the standard quicksort algorithm. (For more information on various sorting and searching algorithms, consult your computer library. This is a big topic!)

To try the sorting mechanism, load the module named basSortDemo in 07-07.MDB. From the debug window, type:

TestSort 6

where the 6 can be any integer between 1 and 20, indicating the number of random integers between 1 and 99 that you want the routine to sort. The sample routine, TestSort, will create the array of integers and send it off to VisSortArray, a special version of the sorting routine acbSortArray that shows what it’s doing as it works. Figure 7-11 shows the output from a sample session.

Figure 7-11. The output from a sample ...