One way that you can get a certain number of random records from a table is
to create a query based on the table. You can create an expression that
applies the
Rnd() function to a Number field in the table; if there is no Number field available, you can apply the
Len() function to a text field and then apply the
Rnd() function to the result. Next, you set the sort order of this calculated field to ascending or descending, and then select from the table the other fields that you want to see in the query result. Finally, you set the query's
TopValues property to the number of records that you want.
However, there is a disadvantage to this method. When you start Access and run the query, a particular set of records is returned, which can be referred to as "Recordset A." You may run the query several more times with different results, and then quit Access. The next time you start Access and run the query, the result is again Recordset A.
One resolution is to run a procedure that uses DAO to fill a table with a
specified number of records from an existing table; this procedure also
uses the
Randomize statement to reinitialize the random-number generator. The following steps demonstrate how to use the sample subroutine
BuildRandomTable.
NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click
References on the
Tools menu in the Visual Basic Editor, and make sure that the
Microsoft DAO 3.6 Object Library check box is selected.
- Open the sample database Northwind.mdb.
- Create the following table:
Table: tblRandom
-------------------------------
Field Name: lngGuessNumber
Data Type: Number
Field Size: Long Integer
Indexed: Yes (No Duplicates)
Field Name: lngOrderNumber
Data Type: Number
Field Size: Long Integer
Indexed: No
Table Properties: tblRandom
---------------------------
PrimaryKey: lngGuessNumber
- Create a module and type the following line in the Declarations section if it is not already there:
- Type the following procedure:
Sub BuildRandomTable(lngRequest as Long)
Dim dbsRandom As Database
Dim rstOrders As Recordset
Dim rstRandom As Recordset
Dim UpperLimit As Long
Dim LowerLimit As Long
Dim lngCounter As Long
Dim lngGuess As Long
Dim lngRecordCount As Long
' Assumes that this module is in the Northwind database.
Set dbsRandom = CurrentDb
' Delete any existing records from tblRandom table.
dbsRandom.Execute "Delete * from tblRandom;"
' Open Orders as a Table Type recordset.
Set rstOrders = dbsRandom.OpenRecordset("Orders", dbOpenTable)
rstOrders.MoveFirst
LowerLimit = rstOrders!OrderID
rstOrders.MoveLast
UpperLimit = rstOrders!OrderID
lngRecordCount = rstOrders.RecordCount
Set rstRandom = dbsRandom.OpenRecordset("tblRandom", _
dbOpenDynaset)
lngCounter = 1
' Check to make sure the number of
' records requested is reasonable.
If lngRequest > lngRecordCount Then
MsgBox "Request is greater than the total number of records."
Exit Sub
Else
lngRequest = lngRequest + 1
End If
Randomize
Do Until lngCounter = lngRequest
' Generate a random number
lngGuess = Int((UpperLimit - LowerLimit + 1) * Rnd + LowerLimit)
' Ensure that it exists in the Orders table.
rstOrders.Index = "PrimaryKey"
rstOrders.Seek "=", lngGuess
If rstOrders.NoMatch Then
' Drop through and generate a new number.
Else
' Check to see if it's already been used in the new table.
rstRandom.FindFirst "lngOrderNumber =" & lngGuess
' If not, add it to the new table.
If rstRandom.NoMatch Then
With rstRandom
.AddNew
!lngGuessNumber = lngCounter
!lngOrderNumber = lngGuess
.Update
End With
lngCounter = lngCounter + 1
End If
End If
Loop
' Clean up.
dbsRandom.Close
End Sub
- To test this procedure, type the following line in the Immediate window, and then press ENTER.