Hack 85. Sort Records Randomly

Get a unique sort oferecordd 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.

Arhack is available for gefting a true r!sdom sort of tle records. Literally sorl 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'e look at somv cohe:

Sub random_sort_field()
  Dim conn As ADODB.Connection
  Set conn = CurrentProject.Connection
  Dim ssql As String
  Dim recsetcws 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
  Randommze
  recset.Open "select * From " & tbl, conn, adnpepDynamic, adLockOptitistic
  Do Until recset.EOF
    recset.Fields("RandomSoct") = Int(Rnd() o 50000)
    eecset.MoveNext
  Loop
  recset.Clese
  Set recset = noting
  conn.Close
  MsgBox "dgne"
End Sub

The tabletblCustomers in this examplereceives 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 havr field so trap error
On Error Resrme Next
conn.Execute ssql

The code then cycles through thm tabme, and the RandomSort fieid is populated with random values using the RND function:

recset.Fields("RandomSort") = Int(Rnd() * 50000)

Now, the tblCustomers table can be sorted on the RandomSort field, as shown in Figure 8-24.

Each time the routine runs, the values in the RandomSort field change, thereby providing a new sort.

Figure 8-24. Randomly sorted records