• VBA Index
• Printer Friendly Version
• Download the Code
• Download the Database

Selecting Random Records from an Access Table

It was a simple enough question, but the answer wasn't so easy to come by. I was teaching an introductory Access VBA class and one of the students asked how he could select a random set of 25 records from a table in his database.

The solution, when it eventually came to me, demonstrates several useful techniques in Access VBA including creating and filling tables, and running queries 'on the fly' without having to build them first.

Working with Random Numbers

The task is to select a set of records (say 25 of them) at random from a table. Access and VBA have a random number function. Try it out... open a new module and then open the Immediate Window (Access 2000/2002) or go to the Immediate Pane of the Debug Window (Access 97)(keys CTRL+G). Type the line:

?Rnd

and press ENTER. The Rnd function returns a random value less than 1 but greater than or equal to zero.

Perhaps the Rnd function could help select records at random. My first plan was to create a query showing the required fields from the source table, with an additional calculated field in the form of a random number. I could then sort the data by the value in the new field and pick the first 25 records. Queries have a Top Values property that you can set to any number or percentage, so setting this property to 25 would select the first 25 records. Unfortunately, it didn't work! Because the Rnd function is called only once, when the query is run, it generates only one number and the same number is given to each record. Back to the drawing board!

Using VBA to Select Data at Random

I was going to have to allocate an individual random number to each record. This meant working a record at a time, and I was going to need VBA to help me. I was working in Access 97 (changes in VBA syntax mean that it you have use a slightly different version for Access 2000/2002 see below). Here's my finished code:

Sub PickRandom()
    Dim db As Database
    Dim tdf As TableDef
    Dim fld As Field
    Dim rst As Recordset
    Dim strSQL As String
    Dim strTableName As String

' 1: Create a new temporary table containing the required fields
    strSQL = "SELECT tblStaff.Firstname, tblStaff.Lastname " & _
             "INTO tblTemp " & _
             "FROM tblStaff;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    
' 2: Add a new field to the new table
    Set db = CurrentDb()
    Set tdf = db.TableDefs("tblTemp")
    Set fld = tdf.CreateField("RandomNumber", dbSingle)    
    tdf.Fields.Append fld

' 3: Place a random number in the new field for each record
    Set rst = db.OpenRecordset("tblTemp", dbOpenTable)    
    rst.MoveFirst
    Do
        Randomize
        rst.Edit
            rst![RandomNumber] = Rnd()
        rst.Update
        rst.MoveNext
    Loop Until rst.EOF    
    rst.Close
    Set rst = Nothing
    
' 4: Sort the data by the random number and move the top 25 into a new table
    strTableName = "tblRandom_" & Format(Date, "ddmmmyyyy")
    strSQL = "SELECT TOP 25 tblTemp.Firstname, tblTemp.Lastname " & _
             "INTO " & strTableName & " " & _
             "FROM tblTemp " & _
             "ORDER BY tblTemp.RandomNumber;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

' 5: Delete the temporary table
    db.TableDefs.Delete ("tblTemp")
End Sub

How the Code Works

First of all the variables are declared. All those Dim statements might seem a bit of a chore but they help your code run faster and help avoid errors. It is also good practice to put all your variable declarations together at the top of your code. Then comes the code that does the work...

Step 1: Build a New Table

The string variable strSQL is filled with an SQL statement that performs the equivalent of an Access Make-Table query. The SQL statement copies two fields (Firstname and Lastname) from the source table (tblStaff) into a new table (tblTemp). I haven't included a "WHERE" clause containing criteria because I want all the records copied.

Using an "action" query offers two benefits. It makes coding simpler - I don't have to separately build a table and then fill it with data, and it allows me to use the DoCmd.RunSQL statement. This statement can only be used with action queries (such as make-table, update, append and delete queries). 

In Access all action queries ask the user's permission before completing their task (you have probably seen the sort of thing... "You are about to delete 27 row(s) from the specified table." It isn't necessary to display a warning in this instance. If I did, and  the user chose to "cancel" the operation the code would crash! So the make-table operation is preceded by a line switching warnings off and, very importantly, followed by a line switching warnings on again immediately afterwards.

So, now I have a new table containing all the records from the original table but only the fields I need.

Step 2: Add a Field to the New Table

This step opens the definition of my new table - think of it as opening the table in design view - and creates a new field (called RandomNumber) of the correct data type for the data I am going to put into it (i.e. Double the kind of number returned by the Rnd function). Finally the field is appended to the fields collection of the table definition - like saving the changes to the table.

At this point my new table has a new field which contains no data. The next task is to add that data.

Step 3: Add the Random Numbers

Now the new table is opened as a table-type recordset giving me access to the records it contains and allowing me to edit them. The statement rst.MoveFirst makes sure that the first record is selected before initiating a loop that will move through all the records in the table (EOF means "End Of File").

Inside the loop the command Randomize initializes the random number generator then rst.Edit opens the record for editing, rst![RandomNumber] = Rnd() places a random number into the empty RandomNumber field, and rst.Update updates the record (i.e. saves the changes). Then rst.MoveNext selects the next record.

Because this code is inside the loop, it repeats until it has processed all the records in the table. The line rst.Close closes the table and Set rst = Nothing makes sure that Access releases it from the memory.

I now have a table with a new field in which each record has an individually created random number.

Step 4: Pick 25 Records

I am going to pick 25 of those records at random and place them in a new table. I can do this with a make-table query. The SQL statement sorts the records into ascending order by the RandomNumber field. Because the numbers were allocated randomly, this will sort the records into a "random" order. It also uses "SELECT TOP" to choose the first 25 of these randomly sorted records - effectively a randomly chosen collection of records - and paste just the field I want (Firstname and Lastname) into a new table.

I have used a variable called strTableName to generate a unique name for the table that combines the text tblRandom_ with the current date in the format ddmmmyyyy. The result will look something like tblRandom_27Sep2001. If I were going to create more than one table each day I would modify the function to add the time as well. I would have to change the function from Date to Now as the former does not provide the time. So using Format(Now,"ddmmmyyyy_hhmmss") would result in something like tblRandom_27Sep2001_113506.

As in Step 1 a DoCmd.RunSQL statement is used to create the table, with warnings temporarily switched off.

When you use code to create a table make sure you have taken account of what might happen if a table with the same name already exists. A make-table SQL statement like the ones used here will simply overwrite the existing table without warning you. But if you are building a table with VBA the presence of one with the same name will cause an error.

Step 5: Delete the Temporary Table

The job is almost finished. All I have to do is delete the temporary table I created to hold the data whilst it was being sorted. The last line of code takes care of that, and I am left with a new table in my database containing 25 records chosen at random from my source table. Job done!

Changes for Access 2000/2002

The code listing above works in Access 97. To run it in Access 2000/2002 you need to make some minor changes. Edit the declarations at the top of the code to read:

Sub PickRandom()
   Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Dim fld As DAO.Field
   Dim rst As DAO.Recordset
   Dim strSQL As String
   Dim strTableName As String

The changes are marked here in red. This tells Access that you are using DAO (Data Access Objects) to refer to database objects in your code - newer versions of Access default to the ADO (ActiveX Data Objects) system. Now go to Tools > References and check the entry for Microsoft DAO 3.6 Object Library and click OK. If you don't find 3.6 then 3.5 will do. This adds a set of references to the Access VBA library so that it understands the DAO coding system used here.

Download the Code

Click one of the images or text links below to download a .bas file containing the code used here. A .bas file is a simple ASCII text file that you can open and view or edit in Notepad. You can import the file directly into Access. Follow the instructions next to the icon for the version you use:

Access 97: Click the icon or text link to download the file vbatut02_97.bas to your hard disk. To import the code it contains into your database, open the database and open a new module (or you can use an existing one). Choose Insert > File and set Files of Type: to Basic Files. Locate and select vbatut02_97.bas and click OK to import the code into your module.

Access 2000/2002 Click the icon or text link to download the file vbatut02_2K.bas to your hard disk. To import the code it contains into your database, open the database and open the Visual Basic Editor (keys: ALT+F11). Choose File > Import File then locate and select vbatut02_2K.bas and click Open to import the code as a new module. To place the code directly into an existing module, open the module and place the cursor where you want the code to go. Then choose Insert > File and set Files of Type: to Basic Files (*.bas). Locate and select vbatut02_2K.bas and click OK to import the code into your module. You will need to delete the two lines of code Option Compare Database and Option Explicit as these will already be present in an existing module.

Download the Database

You can download a copy of the database used in this tutorial and most of my Access and Access VBA tutorials. Visit the Downloads Page to get the file.