• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

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?
0
SRP
Asked:
SRP
  • 3
  • 3
  • 2
1 Solution
 
TimCotteeCommented:
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
  .MoveFirst
  Do
    !Random = Rnd() * 100
    .MoveNext
  Loop Until .Eof
End With
rstRandom.Sort = "Random"

Or something like that anyway.
0
 
georgemanCommented:
Try this algorythm:

- load all db records in rs recordset

- get number records in recordset
  rs.MoveLast
  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
 repeat1:
     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.

Regards
George
0
 
TimCotteeCommented:
That would work as well.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
georgemanCommented:
Sorry TimCottee
I did not see your comment when I created my own.
George
0
 
TimCotteeCommented:
That is OK, you used a different method though the thought process is obviously similar.
0
 
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.
SAM
0
 
georgemanCommented:
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).
George
0
 
SRPAuthor Commented:
Cheers, I thought I had already accepted the answer. Obviously not!

Here are the points.

Thanks
SAM
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now