ACC2000: How to Fill a Table with Random Records from Another Table

• Microsoft Access 2000

SUMMARY

This article demonstrates a method that you can use to fill a table with random records from another table. The sample procedure in this article uses Data Access Objects (DAO) to fill a table with a user-specified number of records from an existing table. The following example randomly selects records from the Orders table in the Northwind sample database and adds them to a new table.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

MORE INFORMATION

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.

The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you need to reference the Microsoft DAO 3.6 Object Library.

1. Open the sample database Northwind.mdb.



2. 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 

3. Create a module and type the following line in the Declarations section if it is not already there:




 Option Explicit 

4. 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 

5. To test this procedure, type the following line in the Immediate window, and then press ENTER.




 BuildRandomTable 5 

REFERENCES

For more information about using a query to select random records, please see the following article in the Microsoft Knowledge Base:

Q208855 ACC2000: Find N Records in Random Order.

For more information about the Rnd function and the Randomize statement, click Microsoft Access 2000 Help on the Help menu, type "random numbers" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: seed OFF2000

Keywords : kbcode kbdta AccCon MdlGnrl MdlDao KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto