Hack #85. Sort Records Randomly
Get a unique sort of records whenever you need one.
Records in a table are always in some kind of order. A primary key or other index might have been applied. Even when all indexes are removed, the records are in the order in which the table received them.
A hack is available for getting a true random sort of the records. Literally sort them on random values! To get this to work, you add an extra field to the table. You then populate the field with randomly generated values. Let's look at some code:
Sub random_sort_field() Dim conn As ADODB.Connection Set conn = CurrentProject.Connection Dim ssql As String Dim recset As New ADODB.Recordset Dim tbl As String tbl = "tblCustomers" ' the table name could be passed as an argument ssql = "Alter Table " & tbl & " Add Column RandomSort Long" 'may already have field so trap error On Error Resume Next conn.Execute ssql Randomize recset.Open "select * From " & tbl, conn, adOpenDynamic, adLockOptimistic Do Until recset.EOF recset.Fields("RandomSort") = Int(Rnd() * 50000) recset.MoveNext Loop recset.Close Set recset = noting conn.Close MsgBox "done" End Sub
The tableâtblCustomers in this exampleâreceives a new field named RandomSort. However, the field might already be there from the last time this code was run, so an On Error
statement precedes the operation:
ssql = "Alter Table " & tbl & " Add Column RandomSort Long" 'may already have field so trap error On Error Resume Next conn.Execute ssql
The code then ...
Get Access Hacks now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.