subform row limitation

access 2010.

Does access have a row limitation on returned records for a subform or continuous for,m ?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Not one I've met yet. But you might consider whether it is practical to tie directly into a recordsource of millions of records, and to start downloading all of those records.

But if you are working in the realm of thousands of records you should have nothing to worry about. If you go above that amount, it can be sensible from a performance standpoint to consider ways to limit the number of rows returned.
FordraidersAuthor Commented:
ok, How about a linked table...anyway to get a return of records quicker...
going to last record and then going to first record..??
sometimes it can take up to a minute.or so

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Well how about some more info? How many records do you have? Is the backend SQL server or access, or something else? Is the recordsource a query or a table or a server side view?

Are the tables properly indexed?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

FordraidersAuthor Commented:
sql server
table  = recordsource.
indexed = yes   ID field only
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
So why would you want to directly navigate a recordset of 5 million? Of course pulling data on 5 million records is going to take time, but no user will browse through 5 million records. If they need something you need to make it easy for the user to search out the records.

Having 5 million records is not a problem, but opening a recordset of 5 million records doesn't really make sense. As long as any processing is done serverside (such as summing) then you should be fine.

Make sure you only pull the columns you need. Don't specify all columns if you only need a few. I suggest you check out this article for a good start.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
People who convert their Jet/ACE databases to SQL Server without considering the ramifications of how Access and a RDBMS interact are always stunned to find out that their app is slower than it was when the BE was Jet/ACE.  The DBA's who manage these databases think Access is a pig because the forms that are bound to naked tables or queries without criteria just sit there and suck down row after row after row until the full recordset is finally downloaded.

We all know that no user is actually going to examine millions of rows of data so what EXACTLY will the user be doing?  Do you think he will be trying to apply form filters to slice and dice the recordset?  This methodology which should never be used with a RDBMS, completely eliminates any potential performance enhancement you might achieve by using a RDBMS BE.  The optimal way to use a RDBMS is to let the server do the heavy lifting and minimize the network traffic.  That means that you need to create an interface that allows users to provide selection criteria which can be used in a query.  Access makes every effort to "pass through" all queries (it doesn't always succeed) so that Access sends the query to the server and the server returns only the requested data.  So if you ask for one record out of 20 million by using its primary key, all the server sends back is one row.  You don't flood the LAN with data that no one is ever going to look at.

Would you ever design a web form that retrieved millions of rows when one would do?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can create Views on the server that limit the amount of data returned in an intelligent manner. For example, you could only show data in the past month, or year, or currently active Customers, etc etc. As Pat mentioned, when dealing with server-based data you should let the server do the work, and not the frontend.

If you have users who do need all that data, include a checkbox or something on the form that warns them of the performance hit, and let them make the choice. If they check the box, then don't use the View, instead use a query that retrieves the data they need.
Mark EdwardsChief Technology OfficerCommented:
As Pat mentioned, there are a lot of people who do nothing with their app but move from an Access backend with linked tables to a SQL Server backend with linked tables, and are shocked that performance does not improve, or is worse.

The only thing this approach does is get rid of the 2GB limit on the backend db and give your backend db user-level security, as well as some backend database maintenance perks, but that's about it.

As stated above, the REAL POWER of a RDBMS is in SERVER-SIDE PROCESSING, where the front-end sends the request to the backend which processes it and just sends the result to the front-end.  Without server-side processing, your app is dependent on client machine processing and that involves dragging ALL THE DATA INVOLVED OVER THE NETWORK to the client machine (HUGE performance drag, and God forbid you have to drag the front-end over the network too!!!), PROCESSING IT ON THE CLIENT (low RAM, etc. more performance drag), then dumping the data not needed in the result, and just leaving THE RESULT DATA ON THE CLIENT.  Is it any wonder you're left looking at an hourglass for several minutes when you're trying to process millions of records?  As stated before, the more you can server-side filter the data that has to go to the client, the better.  You can also vastly improve the performance of this kind of app design by getting rid of the network by putting both the fe and be on the same machine and operate it through a remote system like Citrix - that works.  

Using pure Access (both fe and be) is o.k. IF you have a relatively few number of records (< 500K and the fewer the better!) and you are NEVER in danger of hitting the 2 GB database file size limit (4 GB for 64-bit Access).  (BE SURE you compact your db BEFORE it comes close to hitting the size limit or all hell breaks loose!)

I know having to switch from a pure Access app to an Access and SQL Server app with server-side processing can be a b#$%h, but that's the reality, like it or not.  Otherwise, you (and your users) will just have to learn to live with the consequences.
FordraidersAuthor Commented:
Thanks all   !!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.