We help IT Professionals succeed at work.

Number of records returned on a form

Anton Greffrath
on
122 Views
Last Modified: 2017-03-19
I have recently converted my Client's MS Access database to an SQL Database. After the conversion I have the following issue:

I have a Form, based on a Query as the record source. When a subset of the records in the table (that the query is based on) is selected, the number of records returned in the subset is not displayed at the bottom of the form.

When MS Access the information was available. At the bottom of the form where it states: Records 1 of nn where nn is the number of records. after the conversion the nn is not there.

Any one that can assist to get the nn value showing again?
Comment
Watch Question

Senior Application Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
If your BE is a RDBMS, it is far better to severely limit the number of rows returned as the recordset of a form.  You loose any speed advantage you might gain from SQL Server if you bring unfiltered recordsets across the network in order to filter it locally and the larger the recordset, the larger the cost of doing it.

Typically, Access only retrieves enough records to populate the visible rows of a form.  It then requests additional rows in the background or as you scroll through the recordset.  By having to see the n value from Record 1 of n, you are forcing Access to bring down all the records in the recordset whether the user ever will actually need to view them or not.

Many people who convert Access applications from Jet to SQL Server are flabbergasted when the app is sluggish and slower than it ever was with the Jet BE.  Access is tuned to work with Jet/ACE.  You have to take some control to get that same kind of performance against linked SQL Server tables.  And the best place to start is to change your method of filtering.  Let the user enter selection criteria to bring down as few rows as possible.  Then change the selection criteria to go back to the server to get a different set of data.

Author

Commented:
Thanks Paul Cook-Giles, I implemented your solution and it worked 100%

Author

Commented:
Export Exchange Rocks!

Author

Commented:
Thanks Paul. . . .

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions