In Access find a random record (true random)

what I'm trying to do is everytime the pr开发者_开发百科ogram opens the image on a form is different. So I have a simple table with 2 columns ID and ImagePath, how do I create the code so a random record(ImagePath) is chosen, on a form load event or something similar? Rnd is no good, as it will be the same image everytime the database is reopened.

Thanks!

Try calling Randomize once -- before the first time you call Rnd. As, the help topic for Rnd says, "Before calling Rnd, use the Randomize statement without an argument to initialize the random-number generator with a seed based on the system timer."

Rnd is no good?

Option Compare Database
Option Explicit

Sub Test()
    Randomize
    Dim x As Integer
    'Print the first field of a 100 random records
    For x = 0 To 100
        CallRandomRecord
    Next x
End Sub

Sub CallRandomRecord()
    Dim rs As DAO.Recordset
    Dim recordCount As Long
    Dim randomRecord As Long

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM MyTable")
    rs.MoveLast 'To get the count
    rs.MoveFirst
    recordCount = rs.recordCount - 1
    randomRecord = CLng((recordCount) * Rnd)

     rs.Move randomRecord

     Debug.Print "Random Record No:" & randomRecord & "  Field 1:  " & rs.Fields(0)

End Sub

I wrote a couple functions of my own to return a random record and then timed them along with the other solutions offered here. Both of mine beat the Harkins method, but neither of them could touch @ray023's (slightly modified for benchmarking) solution. @ray023's solution is also arguably the simplest. Take that Susan Harkins!

Here's the code. You can copy it and paste into a standard module to test on your data. You just need to change the three constants at the top of the TimeThem module:

Private Declare Function GetTickCount Lib "kernel32" () As Long

Sub TimeThem()
Const Loops As Integer = 10
Const TblName As String = "Batches"
Const FldName As String = "BatchName"
Const IndexFld As String = "BatchID"
Dim i As Integer, s As Long, dummy As Variant

    s = GetTickCount
    For i = 1 To Loops
        dummy = HarkinsRandom(TblName, FldName)
    Next i
    Debug.Print "Harkins:"; GetTickCount - s

    s = GetTickCount
    For i = 1 To Loops
        dummy = RandomRecord(TblName, FldName)
    Next i
    Debug.Print "RandomRecord:"; GetTickCount - s

    s = GetTickCount
    For i = 1 To Loops
        dummy = RandomRecordWithIndex(TblName, FldName, IndexFld)
    Next i
    Debug.Print "WithIndex:"; GetTickCount - s

    s = GetTickCount
    For i = 1 To Loops
        dummy = CallRandomRecord(TblName, FldName)
    Next i
    Debug.Print "CallRandom:"; GetTickCount - s


End Sub

Function HarkinsRandom(TblName As String, FldName As String)
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset(" SELECT TOP 1 " & FldName & _
                                     " FROM " & TblName & _
                                     " ORDER BY GetRandomValue(" & FldName & ")", _
                                     dbOpenForwardOnly)
    HarkinsRandom = rs(0)
End Function

Public Function GetRandomValue(fld As Variant)
  Randomize
  GetRandomValue = Rnd(1)
End Function

Function RandomRecord(TblName As String, FldName As String)
Dim NumRecs As Long, RecNum As Long
Dim SQL As String, SubSQL As String, rs As DAO.Recordset
Dim IndexFld As String

    Randomize
    NumRecs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & TblName, dbOpenForwardOnly)(0)
    RecNum = Int(Rnd() * NumRecs + 1)
    SQL = " SELECT TOP 1 " & FldName & _
          " FROM (" & _
          "  SELECT TOP " & RecNum & " " & FldName & " " & _
          "  FROM " & TblName & _
          "  ORDER BY " & FldName & ")" & _
          " ORDER BY " & FldName & " DESC"
    Set rs = CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly)
    RandomRecord = rs(0)
End Function

Function RandomRecordWithIndex(TblName As String, FldName As String, _
                               Optional IndexedFieldName As String)
Dim NumRecs As Long, RecNum As Long
Dim SQL As String, SubSQL As String, rs As DAO.Recordset
Dim IndexFld As String

    Randomize
    NumRecs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & TblName, dbOpenForwardOnly)(0)
    RecNum = Int(Rnd() * NumRecs + 1)
    If Len(IndexedFieldName) = 0 Or IndexedFieldName = FldName Then
        SQL = " SELECT TOP 1 " & FldName & _
              " FROM (" & _
              "  SELECT TOP " & RecNum & " " & FldName & " " & _
              "  FROM " & TblName & _
              "  ORDER BY " & FldName & ")" & _
              " ORDER BY " & FldName & " DESC"
    Else
        SQL = " SELECT TOP 1 " & FldName & _
              " FROM (" & _
              "  SELECT TOP " & RecNum & " " & FldName & ", " & IndexedFieldName & _
              "  FROM " & TblName & _
              "  ORDER BY " & IndexedFieldName & ")" & _
              " ORDER BY " & IndexedFieldName & " DESC"

    End If
    Set rs = CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly)
    RandomRecordWithIndex = rs(0)
End Function

Function CallRandomRecord(TblName As String, FldName As String)
    Dim rs As DAO.Recordset
    Dim recordCount As Long
    Dim RandomRecord As Long

    Set rs = CurrentDb.OpenRecordset("SELECT " & FldName & " FROM " & TblName)
    rs.MoveLast 'To get the count
    rs.MoveFirst
    recordCount = rs.recordCount - 1
    RandomRecord = CLng((recordCount) * Rnd)

     rs.Move RandomRecord

    CallRandomRecord = rs(0)
'     Debug.Print "Random Record No:" & randomRecord & "  Field 1:  " & rs.Fields(0)

End Function

And here are the results of the test running against a table with about 50,000 records (it's a locally linked Jet table; ie, it's in an .mdb on the same computer as where I ran the test):

Harkins: 4461 
RandomRecord: 2528 
WithIndex: 1918 
CallRandom: 172 

Harkins: 4150 
RandomRecord: 2278 
WithIndex: 2043 
CallRandom: 47 

CallRandom: 63 
WithIndex: 2090 
RandomRecord: 2324 
Harkins: 4197 

CallRandom: 46 
WithIndex: 1997 
RandomRecord: 2169 
Harkins: 4150 

I ran it four times reversing the order after the first two to account for potential caching advantages. As you can see, my two functions ran about twice as fast as the Harkins solution, but @ray023's solution was at its slowest more than 25 times faster (and at its fastest nearly 100 times faster).

But by all means, benchmark against your own data.

See this article by Susan Harkins on TechRepublic: http://www.techrepublic.com/blog/howdoi/how-do-i-retrieve-a-random-set-of-records-in-microsoft-access/149

I used her GetRandomValue function in this query, which returns a different record each time.

SELECT TOP 1 f.id, GetRandomValue(f.id) AS rnd_value
FROM tblFoo AS f
ORDER BY 2;

The function:

Public Function GetRandomValue(fld As Variant)

  Randomize

  GetRandomValue = Rnd(1)

End Function

Caution: This approach requires running a function against every row of the table. It may be tolerable for small to medium tables. But you should not use it with very large tables.

I may be too simple to understand the problem, but it seems to me that if you want to retrieve a single random image, then all you need to do is generate a single random number that somehow keys into the table of images available to you. If there are 100 images to choose from, you want a random number from 1 to 100.

So, you generate that number:

  Round(100 * Rnd(), 0)

...and then you use that to retrieve the image. If the table of images has an Autonumber PK, you could just use that, and it would be VERY FAST. If your image is in a subform, you could set the LinkMaster to the literal PK value and that would retrieve the image for you.

On the subject of Randomize(), I can't seem to get it to repeat when I call Rnd() in the Immediate window, so I'm not sure if it's needed.

But it all seems like a very simple operation to me, one that may not require any SQL or the use of a recordset. If you go the recordset route, I'd recommend opening it once and persisting it and then navigating it each time you need it, rather than opening it repeatedly each time you need a new image. But if I were doing this, I'd make things as simple for myself as possible and go the Autonumber PK route for the images. If you wanted to do it in SQL, that would be:

  SELECT Images.ID, Images.Path
  FROM Images 
  WHERE Images.ID = Round(100 * Rnd(), 0)

Obvoiusly, you'd change 100 to an appropriate number. If you need Randomize(), then replace the direct Round(100 * Rnd(), 0) with a function that calls Randomize() and then returns Round(100 * Rnd(), 0).

But maybe I'm missing some important details that makes this much more complicated than I seem to think it is.