How to Set Access Form RowSource to ## of records in VBA

[Snoday]

I am currently working with large datasets, running multiple queries against 12 tables, each table contains 700,000+ records. I am looking at a way to limit the number of records returned in queries to increase performance. I have been to a lot of web pages where you can look at records 1-25 then 26-50, then 51-75... Is it possible to replicate this type of behaviour utilizing an access form or query? I have been taking random stabs at this and can not figure out how to return recordsets that are extracted from a long list, but just a subset in the middle and a specified number of records long. Any chance a guru can point me in the right direction? Thank you for you patience and insight.

[June7]

Check out Allen Browne's

http://allenbrowne.com/ser-32.html

and

http://allenbrowne.com/AppFindAsUTypeCode.html

[Snoday]

The material you recommend i have reviewed and although it provides interesting ways to subset your data based on user inputs and make your forms more responsive by using a dynamic range for your row source on large record combo box. I still am unable to figure out an adaptation that would return specific record subsets of a recordset based on a screen combo field to display 10, 25, 50, 100 records at a time on the form. how do you get a recordset to pull the 51st to the 75th record to display for a rocord source of the form?

[June7]

The thread title referenced RowSource which is for a combobox/listbox, not the RecordSource for a form. Never encountered this before and don't know what a solution could be.

[orange]

Just noticed this post. I'm not sure of an answer, but can you tell us more about the tables? Is there any field that identifies a specific record?

If there is a field you could try something like
Select........
where thefield between xxxx and xxx+25

There is no rownumber with access like there is with other dbms, but may be you have a field that could be used similarly.

[Snoday]

Thank you for your responses. I think Orange hit the nail on the head with comment access doesn't have a native row number feature. I had not thought about adding a rownumber field and repopulating based on the sequencing needed. I think it would allow me to replicate the pulling of records in a manner similar to the a web page drop box of show me ## records in user selected increments like 1-25 then 26-50, then 51-75. I will look at adding a rownumber field to the parent table and I will check the performance hit of renumbering the row for different sort orders and data subsets. The guys running using this database regularly had simply dedicated one terminal to running these queries and if they needed multiple data subsets would start them running on their computers prior to leaving for the evening. This project had been pushed way down my priority listing as the program provided the required functionality, just at a very slow pace. This came back on my plate for a minor change. While I’m making changes this time I'll add the rownumber and see whether renumbering the field based on filter and sort requirements will outweigh the ability to return a lot fewer records per screen view or if the query is simply slow because of the scope of the relationships between the large datasets.

[ssanfu]

If you are not DELETING records, you might check out the "AbsolutePostition" property in VBA help. Open a recordset as a snapshot (so the positions won't change) and you could select "groups" of records.


Just an idea........