random databases.

I've got a relatively small database sorted by number.

How can I sort this to display these entries in a random order, each time I run the program, and not repeat entries?
Who is Participating?
georgemanConnect With a Mentor Commented:
Try this algorythm:

- load all db records in rs recordset

- get number records in recordset
  NumberRecords = rs.RecordCount

- create new empty recordset newrs

- create array(NumberRecords) as integer (or long if you have big database)

- for i=1 to NumberRecords
     'get random number
     RandomNumber = Rnd()
    'if RandomNumber doesn't exist in array - add it to array
    'else goto Repeat1

     'get record number=RandomNumber from rs recordset
     rs.findfirst "number = " & RandomNumber
    'upload record from rs to newrs
  next i

- newrs is now copy of rs but organised in random order.

TimCotteeHead of Software ServicesCommented:
You could add an extra field to the recordset, then fill this with a random number and order it on this.

rstRandom.Open "SELECT Number,Field1,Field2...Field3,Random = 0.0 FROM TableName",cnnDatabase,adOpenStatic,adLockOptimistic
With rstRandom
    !Random = Rnd() * 100
  Loop Until .Eof
End With
rstRandom.Sort = "Random"

Or something like that anyway.
TimCotteeHead of Software ServicesCommented:
That would work as well.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Sorry TimCottee
I did not see your comment when I created my own.
TimCotteeHead of Software ServicesCommented:
That is OK, you used a different method though the thought process is obviously similar.
SRPAuthor Commented:
This is a v. good answer, and a very simple. But one brief question, will it produce a different random order each time the program runs?

OK cheers.
It depands how you will organise the process of creating of random numbers. One tip: you can use for that current date/time info so random numbers will be really random because these values are unique for every time when you start your program (or when you start this process inside program).
SRPAuthor Commented:
Cheers, I thought I had already accepted the answer. Obviously not!

Here are the points.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.