By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,647 Members | 1,178 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,647 IT Pros & Developers. It's quick & easy.

filling dataset with first N records of query

P: n/a
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..
Nov 16 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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?


[1] : http://www.winnetmag.com/SQLServer/A...505/40505.html
[2] :
http://www.experts-exchange.com/Data..._21135918.html

--
venlig hilsen / with regards
anders borum
--
Nov 16 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.