[thelist] Select random records from an Access database

Ken Schaefer ken at adOpenStatic.com
Fri Mar 19 18:41:35 CST 2004


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Anthony Baratta" <anthony at baratta.com>
Subject: Re: [thelist] Select random records from an Access database


: At 11:44 AM 3/19/2004, Marco Orlandi wrote:
: >Hi all........
: >               I need to select 8 random records among the last 100 in an
: > Access database, containing an undefined number of records.
: >
: >               8 and 100 are example values: actually the user should be
: > able to choose both numbers ( using a form......... ), but the most
: > difficult job to me is to built the select code, via a VBScript ASP
page.......
:
: What you can do is build a temp table with an identity (autonumber)
column.
: In the temp table you just have the id of the rows from the table you want
: to "randomly" pick from. Then the user or your program picks 1 thru 100,
: the program links the two tables together and you get your rows. When you
: are done, drop the temp table.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I don't think there's a need for such a messy solution :-)
Temporary tables don't work in Access like they do in SQL Server (they are
not isolated "per connection", and they are not created in memory). The
table you create would actually be created on disk, and it would be
accessible to all users, potentially leading to collision problems if you
have multiple users...

This seems to work:

<%
Randomize()

intRandomNumber = Int(1000*Rnd)+1

strSQL = _
    "SELECT TOP 3 TableID, Field1, Rnd(" & -1 * (intRandomNumber) &
"*TableID) " & _
    "FROM table1 " & _
    "ORDER BY 3"
%>

(assuming you have the fields TableID, Field1 in a table called Table1)

Cheers
Ken



More information about the thelist mailing list