How to put 10th - 20th records into the Recordset

[markask]

Hi,

After connected a .mdb, how to put 10th - 20th records from a table into the Recordset? I don't think pagesize-absolutepage can do this.

Thank you in advance.

 

[johnwm]

Have a look at the GetRows Method

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[markask]

Thank you.

However, what I need is that there are only 11 (10th-20th) records in the recordset.
When use GetRows, we put all records into the recordset and then get some rowd from it.

 

[Sheco]

Do you really care what records go into the recordset or do you just need an way to quickly move to a specific record ?

 

[Sheco]

I mean if the recordset would normally have like a gazillion records and you can't really narrow it down with your WHERE clause .... AND you only need 10 of the records ... then it would be a waste of resources to pull all gazillion over into the recordset only to use a few.

But if you are only talking about a few records in the first place then that changes things.

 

[markask]

Thank you.
I used 11 records as an example. Let we say, how to find the nth record and the following m records and then put such m records into the recordset.

 

[fredericofonseca]

depending on your database you may use "select top x from ...", but you would still need to load the top 20 into your recordset to get the 10th to 20th records.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Sheco]

So you need to control the total contents of the recordset ... not just be able to read m records starting at record n ???

And also not just the TOP n records ....

One solution, ugly as it may be, is to write a stored procedure. Inside the stored procedure you would select the records that you want into a temporary table with an incremental identity column that starts at 1. Then return records using a select from the temp table based on the identity column.

 

[markask]

fredericofonseca,

Thank you.
I am using Access2000.....Can we use Select Top for that?

----------------------
Sheco,
Yes, I need to use the Recordset which contains the m records I need.
And, wow, to create another table is a big task.

 

[fredericofonseca]

hit your F1 key and search for "select top" and you will find your answer

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Sheco]

Nevermind about stored procedures if you are using Access.

Select TOP will work but you'll still have to get the top n + m and then move to record n and read until EOF... so if n is 1000 and m is 10 then you'll still need 1010 records in your recordset.

 

[markask]

fredericofonseca,

Can you tell me where I should hit my F1 key? In VB, Access or where?
---------------------------------

Sheco,

The problem is I don't want to read the record one by one in the recordset, I want to use the recordset as a whole.

 

[Sheco]

Oh, you could use 2 recordsets, one with all the data and one empty... Then manually copy only the reocrds you need into the empty one. Yuck!

 

[markask]

Sheco,

No....I am using VB+ADO+Access and want to do that in programming.

 

[Sheco]

Yeah I understand. This a recurring issue.

You can use the recordset's AbsolutePosition property to jump to record n and then read that record and then call MoveNext() m times.

And you can set the PageSize property of the recordset to break the thing into groups of m records and then use PageCount to see how many "pages" of records you have... you can even use AbsolutePage to jump to the start of a certain page.

You can get the TOP 20 rows and then ignore the first 10... but you can't just directly get rows 11 thru 20.

 

[Sheco]

You might be able to limit the damage by setting the CacheSize property of the recordset but I dunno if that really matters for an Access db.

 

[fredericofonseca]

the F1 is basically your help system.

Go to Menu, help, microsoft access help and go from there.

I think that after all that has been said here we have reached a point that we need to know WHAT you are trying to accomplish with retrieving a particular group of records within a recordset, specially with a so broad definition as you have given so far.

Depending on your setup, and on your user level and hardware/server capabilities, you may wish to consider moving from Access to MSDE as database engine (FREE), as the last one will allow you everything you have available with SQL Server, such as stored procedures and DTS packages, and a wider range of SQL syntax.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[DrJavaJoe]

http://support.microsoft.com/default.aspx?scid=kb;en-us;207651

Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[Sheco]

lol ... that link shows some kind of MS Access getto-style pseudo stored procedure

 

[markask]

Thank you for all your helps. I realized that there is no way to do that! I wish Access is an open code so that all of you can modify that.