filling dataset with first N records of query

  • Thread starter Thread starter VMI
  • Start date Start date
V

VMI

I need to display Access data in a datagrid but the Access table has over 2
million records. Since I can't fill a datatable with all those records but
the user needs to see all of them, how can I fill the datatable with a
subset of the initial query result? In the windows Form, under the datagrid,
I was thinking of adding a "Next" button that would display the next N
records from the initial query, and a "Previous" that would display the
previous N records. Is that possible?

Thanks..
 
Hello!

Yes, this is possible - and quite easy. Two million records in an Access
database sounds like an awful lot of records for Access, but I guess you
already know this.

It's been some time since I worked with Access, but I will try to outline
two solutions. As always, it's desired to retrieve as few records as
necessary from the datastore - preferable those records matching the page
you want to display. Check links [1] and [2] from a quick Google search.

Solution 1. Create a SQL query that uses a nested query to return the top of
those records you'd want to retrieve.

Calculate the size of your current page. e.g. Page nr. 5 with 25 records
each. I haven't tested this particular query, but it should get you going in
the right direction.

SELECT TOP 25 FROM Table WHERE TableID IN (
SELECT TOP (5 x 25) TableID FROM Table ORDER BY Date DESC
)
ORDER BY Date

Solution 2.
Create a regular SQL SELECT query, but make sure you're getting a firehose
cursor when executing the SQL query. Then move to the desired page position.
I haven't tested this in the .NET environment yet, but I have seen this
working in ASP 3.0 with an Access DB and close to 1 mio. records - so it
might work here too.
I need to display Access data in a datagrid but the Access table has over 2
million records. Since I can't fill a datatable with all those records but
the user needs to see all of them, how can I fill the datatable with a
subset of the initial query result? In the windows Form, under the datagrid,
I was thinking of adding a "Next" button that would display the next N
records from the initial query, and a "Previous" that would display the
previous N records. Is that possible?
Click to expand...

[1] : http://www.winnetmag.com/SQLServer/Article/ArticleID/40505/40505.html
[2] :
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21135918.html
 
Back
Top