×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

ADP Table doesn't display all records

ADP Table doesn't display all records

ADP Table doesn't display all records

(OP)
I've inherited an Access 2003 MDB -> Access 2007 ADP conversion project. I'm a SQL, VFP, and .NET developer, not an Access developer.

The main form on this project opens a table (not a filter/query/view - just a simple SQL table) which has 9751 records on the SQL server. The form toolbar at the bottom shows records 1 through some random number of records from 3200 - 9751 when you open the form. The toolbar is set with max records = 0 (it originally had max records = 10000 which should have worked anyway). It is not pulling all the records.

If you click on the toolbar and change the max number of records option, it will then apparently requery the table and lo and behold you will see the full 9751 records on the toolbar. Save the project and open it again and you'll again get some random number of records (not 9751) on the toolbar. This process is absolutely useless for the client.

What gives (I realize it is Access but SOMETHING should work properly in the product..?)??

If I select * from the table in SQL Management Studio I get the entire table (9751 records) in a flash. If I connect from VFP and select * from the table into a cursor it takes less than a second (and I get 9751 records).

Bill Kuhn - MCSE
bkuhn@kuhngroup.com
The Kuhn Group, Inc.
http://www.kuhngroup.com

RE: ADP Table doesn't display all records

Try the following:

In the .adp goto Tools - Options - Advanced.  There's a box titled client-server settings & a figure for 'Default max records' I can't remember the default figure but try setting that to something like 999999.



RE: ADP Table doesn't display all records

(OP)
In access 2007 apparently this is done by clicking on the idiotic round button, selecting Access Options, Advanced, and setting Default Max Records under the Advanced section to 0 (according to Help). I've done that. I've also set it to various numbers above 10000 which is the default. It has not made any difference.

I'm not sure if I was clear in my original post by the way - My recordsource for the form is the table itself (in this case 'WM-TRACKING') as an updatable snapshot if that makes any difference. I have also tried using a view which was simply 'Select * from WM-TRACING', but the results were the same. You never know when you open the form how many records it will allow you to see.

Also, if it only shows for example 5100 of the 9000+ physical records, if you enter a number greater than 5100 in the status bar it will tell you "You can't go to the specified record. You may be at the end of a recordset".
Again, if you click on the 'set the maximum record count' button on the toolbar and click OK on the resulting dialog box (you don't have to change it), access will suddenly show and allow you to select all 9751 records on the toolbar.

Bill Kuhn - MCSE
bkuhn@kuhngroup.com
The Kuhn Group, Inc.
http://www.kuhngroup.com

RE: ADP Table doesn't display all records

Sorry, hadn't spotted you are using Access 2007, which we haven't yet moved to so can't help further.  

RE: ADP Table doesn't display all records

(OP)
Well I've managed a workaround to that Access 2007 bug.

Apparently using a table directly as the recordsource of a form (setting the recordsource in the property sheet)is 'unreliable' and the form may or may not see all records.

However if you set the recordsource of the form to the table in the load event of the form, it becomes 'reliable', and the form sees all the records.

Nice, eh?



Bill Kuhn - MCSE
bkuhn@kuhngroup.com
The Kuhn Group, Inc.
http://www.kuhngroup.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close