Random sorting query Access

问题:

I'm using this simple query to use a random sorting on a ms-access database:

SELECT pk FROM TABLE ORDER BY Rnd(pk) asc

And it's working fine when i test it with Microsoft Access 2010

However, when i call this query using classic asp, random sorting doesn't work.

Here's my code:

set Rs = Server.CreateObject("ADODB.Recordset")
Rs.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/dbfolder") & "\dbname.mdb"

Rs.Source = "SELECT pk FROM TABLE ORDER BY Rnd(pk) asc"
Rs.CursorType = 3
Rs.CursorLocation = 2
Rs.LockType = 3
Rs.Open()

do while not Rs.eof
    'do stuff
    Rs.Movenext
loop

回答1:

You just need to "salt" your random generator:

Rs.Source = "SELECT pk FROM TABLE ORDER BY Rnd(-Timer() * [pk]) Asc"

回答2:

Finally it seems that i found a solution.

Source:http://www.cosnetics.co.uk/articles/select-random-records-from-access/

Following what's written in the above link

Here is a very simple way of selecting random records from an Access database:

SELECT TOP 5 * FROM [tableName] ORDER BY rnd(INT(NOW*id)-NOW*id)

The only prerequisite is that you have an AutoNumber Id column. It should also be noted that although this generates a random set of records, they are not truely random in a mathematical sense, but should be sufficiently random for most uses.

This method of selecting records also works in sql server, but you have to change rnd to rand.

If you find a better solution, i'll be pleased to mark it as the good one. In the mean time, i'll leave this here. hoping that this will help somebody else. (You won't believe it but it took me some time to find it)