ADP Table doesn't display all records

[BillKuhn]

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

 

[ajrimmer]

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.

 

[BillKuhn]

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

 

[ajrimmer]

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

 

[BillKuhn]

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