Handle millions of records ListView or MsFlexGrid

Question

I have a situation that needs to handle millions of rows in Ms Access.
The data come from a SQL server and in normal cases a continuous form would be just right but the rows its the only issue as i have table with many columns.
So i started examining alternatives like ListView or MsFlexGrid...what troubles me is that i would like to have something lazy loading....the first 1000 rows load and then the user as scrolls down/up the next set of data are loaded.
I have tested this functionality some years with DGV on a small .NET application but i haven't seen anything similar in Access + ActiveX control, something to add is the necessity for the control to display the total rows that are eligible for retrieval...eg. 100,000 rows but only loading sets of 1000... I also need this to be a free solution .. Any ideas....
I am afraid that documentation for such kind of extended Controls is quite old and i stumble on dead sites...so a good direction would be great.
Last but not least...no editing would be necessary..simply for viewing.

Answer 1

There is really zero reason to pull or have a form scroll past 1000’s of records. Such a process is sheer torture to users.

For what possible reason would you want users to scroll past 1 million rows in a form? I don't think software developer skills are required here that such a approach REALLY needs to be avoided.

The solution is to simply ask, or get some search criteria BEFORE you fill the form with results.

This form works great on tables with 1 million rows. You type in the first name, and then perhaps a bit of the last name. You then fill the form with JUST the results.

Even on tables with huge numbers of rows, it performs instant.

The form can look like this access example:

The code behind a search button looks like this:

Dim strSQL       As String

strSQL = "select * from tblcustomers where LastName like " & Me.LASTNAME & "*'" And _
 "FirstName like " & Me.FIRSTNAME & "*'"

Me.RecordSource = strSQL

The above will performance near instant – even if the form is based on a linked table to sql server.

Floating the idea to try and pull and page 1 million rows makes ZERO sense in Access, .net, or any web based application. To avoid sheer torture to users, simply add some kind of filter or search criteria.

Access as a client to SQL server will ONLY pull records down the network pipe based on the criteria – the whole table is not pulled, and SHOULD not be pulled nor should some messy paging approach be attempted here.

I should also point out that the above example is a simple Access continues form - no activeX or grid control is required - the feature set you required is built right into Access.