• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

8. Programming > 85. Sort Records Randomly

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

PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint