subform row limitation

Fordraiders used Ask the Experts™
access 2010.

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

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.


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

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?
Ensure you’re charging the right price for your IT

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


sql server
table  = recordsource.
indexed = yes   ID field only
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.
Distinguished Expert 2017
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?
Most Valuable Expert 2012
Top Expert 2014

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 Officer
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.


Thanks all   !!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial