Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Pull a random record from an access database

Status
Not open for further replies.
roningeek

roningeek

MIS
Jul 17, 2001
3
US
Hi I'm trying to pull a random record from an access database. I'm using ASP and dreamweaver. Here is the code that I have generated:

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_MVTP_STRING
Recordset1.Source = "SELECT * FROM qryRandom"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>

I tried putting the Rnd() function in the query in the database but that does not seem to work...

Any help would be greatly appreciated in what i should do...
 
Sort by date Sort by votes
what about straight SQL

e.g.
Select * from Table Order by newid()

___________________________________________________________________
[sub]
onpnt.com
SELECT * FROM programmers WHERE clue > 0
(0 row(s) affected) -->faq333-3811
[/sub]
 
Upvote 0 Downvote
actually ms access I think doesn't have newid() built in. isn't rand() or something like that the random return function

anyhow, somewhere to start. keeping it sql I think will make it much more efficient

___________________________________________________________________
[sub]
onpnt.com
SELECT * FROM programmers WHERE clue > 0
(0 row(s) affected) -->faq333-3811
[/sub]
 
Upvote 0 Downvote
ron...this should do the trick...i tested it on a database and works fine

BSL

Code: 
<html>
<head>
</head>
<body> 

<% 
  Set objConn = Server.CreateObject("ADODB.Connection")
  Set objRS = Server.CreateObject("ADODB.RecordSet")
  FilePath = Server.MapPath("db/test.mdb")
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";" 
 

strSQL = "SELECT ID FROM tblTests"

objRS.Open strSQL, objConn, 3,3

Randomize()
objRS.move Int(objRS.RecordCount * Rnd)

Response.Write objRS("ID")



objRS.Close
Set objRS = Nothing
objConn.close
Set objConn = Nothing
%> 
</body>
</html>
 
Upvote 0 Downvote
The problem with the two methods I have seen so far is that they require you to pull back a recordset for the whole table just to select a single record. My preference is to only pull out a single record to get one record.
Rnc() by itself won't work because it will always be seeded by the generic seed, thus giving you the same values (and same record) every time. However, if you seed it yourself then you will get random results. Something like:
Code: 
Set objRS = objConn.Execute("SELECT TOP 1 id_fld, fld1, fld2, fld3, ... FROM tblWhatever ORDER BY Rnd(id_fld) * Second(Time()) * 1000 mod 1000")

By using just the second portion of the time and the unique autonumber field that I assumed you would have, we basically get every row using a differant seed multiplied by a new value every second. By multiplying by 1000 and modding by 1000 we are basically ordering by the first for decimal places of the returned numbers. This should give you a fairly random looking order, though it will likely repeat every minute since the Access Rnd() function is about as far from Random as you could get.

You may need to test this a bit, I played with it on a small db (only about 10 records) and it seemed to work, but not sure how it would do with a larger one.

-T

barcode_1.gif
 
Upvote 0 Downvote
Tarwn, I ran
Second(Time()) * 1000 mod 1000
in the debug windown of a VBA module and got 0 for an answer. I think that Second(Time()) will always return an integer value(23 or 02 or 59) * 1000 Mod 1000 will never show a remainder (hence the 0 value).
Also, when using Mod with a Time value, there is a delay in how quickly the expression will turn over a new seed (about every 10 seconds is what I saw). So depending on how quickly you need to reseed, that would be an issue.
CDbl(Now())/100000 would give you something that never repeats itself, changes every second and would probably do the trick.

Just a thought.

Paul
 
Upvote 0 Downvote
Yep, sorry, that second() isn't supposed to be in there, just:
Time() * 1000 mod 1000, I was hesitant to take it much higher due to the size of the number your potentially creating and the fact that you really only need a somewhat differant number.

barcode_1.gif
 
Upvote 0 Downvote
tarwn,

the randomized method works great....have you tried it? it gives you a "fairly random order" as you have stated yours does; however, not as efficient i agree since pulling all values. I used 12 records and was very randomized considered how small the table was...with hundreds are thousands it would probably provide better results. I guess the question really is...what method could provide a random amount of questions and go through..let's say 100 records for a test questionaire and go through all 100 records and NEVER repeat the SAME record while doing all 100 records?

i know mine won't do that but DOES randomize VERY well; however, will eventually pull up a record already pulled before....because it is still randomized only and not coded to ensure no repeats will show up. This was not what the original question was asking. He just wanted to "randomize records from an access database" this does that as advertized but I think if you could get it to make sure you don't get a repeat until ALL have been known to be pulled already would be the greatest feat.

Thanks for the other option:)

 
Upvote 0 Downvote
The only way I could imagine that to be possible would be to flag the record that is pulled up and set up your sql to filter out all flagged records.
In other words, do your Select statement, and follow that with an Update statement to change the Flag on the random record returned. Then the next time you run the Execute command, you would filter out any records that have the flag set to 'Yes/True/-1'.
At the same time, you would have to test to see if you got a value from the Select statement and if you didn't, reset all the flags and then repeat the Select process over again since no records would indicate that all existing records had been flagged.
Not particularly difficult, but if that is what you wanted, you could just start at the top of the recordset and work you way to the bottom and not bother with ramdomizing at all(unless there is something about consecutive records that would influence some other factor within your page).


Paul



Paul
 
Upvote 0 Downvote
Sounds good Paul,

I'm not sure if Ron is wanting this,,,it was jst something I though would make it better. Running Tarwns code and mine...did you see a difference in how randomized the results were for a small number of records?

just curious;-

Brian
 
Upvote 0 Downvote
I didn't see a lot of difference in a small number of records, but think I had more repeats using the RecordCount value than the Time() value as part of these expressions. I think the Double precision value of Time() was better suited to giving random returns than the Integer that my RecordCount used. Both seemed very usuable in the long run with just a little tweaking.

Paul
 
Upvote 0 Downvote
thanks for replying Paul...guess it's just a matter of preference/efficiency for Ron to choose from

Thanks for the input...never seen the other way of pulling records...def will experiment w/ that

Again thanks Paul and Tarwn!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
185
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
148
leifoet
leifoet
JScannell
  • Locked
  • Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
190
JScannell
JScannell
penguinspeaks
  • Locked
  • Question
Start loop from a specified point
Replies
0
Views
162
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
Wrong code in a href ?
Replies
1
Views
193
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top