Positioning your RecordSet at a Specific Record

[kimosavi]

Hi,

I think problem is this is kind common but I can't seem to find an easy solutions.

I have a TEXT file linked as a table (SOURCE) to my DB.
I have a Query (SOURCE_WLU) that uses the TEXT file plus is linked to several others tables in the DB.

i am trying to get a 5 random values out SOURCE_WLU.

so here is my code:

'Note: ALL FIELDS are TEXT
set rs = currentdb.openrecordset("SELECT TOP 5 FIELD1 FROM SOURCE_WLU") 
msg = ""
do until rs.eof
  msg = msg & rs.fields(0).value 
  rs.movenext
loop

this gives me the top 5, but most of the time they are the same so I modified the code to :

set rs = currentdb.openrecordset("SELECT FIELD1 FROM SOURCE_WLU") 
msg = ""
if not rs.eof then 
  rs.movelast
  rs.PercentPosition = 20
  msg = msg & rs.fields(0).value 
  rs.PercentPosition = 40
  msg = msg & rs.fields(0).value 
  '... and so on
  'also could be used with AbsolutePosition if you know your RecordCount
end if

when i use my second code the rs.movelast TAKES FOR EVER! more than 30m. This is due to the linked tables on the SOURCE_WLU.

if i run any of these codes on SOURCE the code is executed in seconds. But the problem is that i need the linked tables.

I cannot convert to a Table at this moment since it will generate a larger than 2GB file, so I need the user to select some fields and limit the records before doing so.

I know before hand how many records there are on SOURCE_WLU, so i don't need to run rs.movelast, but rs.percentpostion and rs.absoluteposition NEED rs.movelast to get populated or generated.

my problem is with rs.movelast. if there was a way for me to use rs.percentposition without calling this method will be great.

if anyone has an idea on how i can do this with uptimum performance I will appreciate it!

Thanks!

Kimo.

 

[Banana]

.Movelast requires that you load the recordset completely, so it's quite expensive and wasteful.

I'd probably look at other solutions that didn't require an accurate recordcount.

Few ideas:

1) Do a separate "SELECT COUNT(*) FROM SOURCE_WLU;" query first and use the result to calculate how many records you want to visit. Count will be faster than doing a Movelast

2) Decide on an arbitrary number- say 20, then move as many records and continue until you hit EOF. That won't require a Movelast and recordcount though I don't know if you need records to be in a certain position or just a sampling of so many records

 

[kimosavi]

Thanks Banana,

I already counted the records, so I know where it ends.

I need to pick samples from random position. I decided to go 20%, 40%, 60%, 80% and 100%.

doing the movenext from 0% to 100% will cause the same or similar effect as movelast.

the rs doesn't seem to go to a specific location (from 0 to 5000) but goes row by row, loading into memory.

no ideas on how to JUMP from 0 to 5000?

 

[boblarson]

You wouldn't use a recordset and move through like that. You can use RND to pull a random number of records (by the way the position of the records is really just an illusion). Just remember to use RANDOMIZE prior to using the RND function so that you get a true random seeding.

Do a search here for RND or RANDOMIZE. I don't have time right now to be able to put together a sample or SQL string to use it.