A record of random access to a recordset

Source: Internet
Author: User
Tags unique id
Access | Randomly assume that the datasheet has a unique ID field and at least one record. The method of randomly accessing one of the records is very simple and can be divided into four steps:
1. Get the total number of records N.
2. Store all ID numbers in an array
3, to produce a random number of not greater than N m
4, take out the first m ID number from the array, query the data table, get the record data.
Here's a partial code:
$#@60;%
Set conn = Server.CreateObject (' ADODB. Connection ')
Conn.Open ' $#@60;conn string$#@62; '

* * * * * * * * * * * (step 1)

Set rs = Conn.execute (' Select count (id) from sometable ')
Rcount = RS (0)

* * * * * * * * * * * (step 2)

Set rs = Conn.execute ("SELECT ID from sometable")
CNT = 1
Dim RRs
ReDim RRs (Rcount)
Do as not rs.eof
RRs (CNT) = RS (0)
CNT = cnt + 1
Rs.movenext
Loop

* * * * * * * * * * * (step 3)

Randomize
CURRENTRR = CLng (rnd*rcount+0.5)
ID = RRs (CURRENTRR)

* * * * * * * * * * * (step 4)

sql = "Select Otherfield from SomeTable where id=" & ID
Set rs = conn.execute (SQL)
Response.Write "ID #" & ID & "=" & RS (0)
Rs.close:set rs = Nothing
Conn.close:set conn = Nothing
%$#@62;
For SQL Server, there are more efficient methods. For example, design two stored procedures. I'm just clarifying some ideas here, and I hope this idea can be used in both access and SQL Server.



Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.