Loading a form in ADP slower than with MDB

[Owen Cooper]

I am using Access 2003 on Windows XP connecting via VPN to SQL Server 2000.



My ADP executes a stored procedure in a remote server which populates a
temporary table. When it inserts 1,000 records, it takes 6 seconds for the
newly-populated table to be returned to my access project.



Now I noticed that an MDB linked to the same table will appear to populate a
similar form in a flash. By monitoring network traffic, it is clear that
the form bound to a linked table on the MDB will retrieve records from the
server as needed.



In contrast to the MDB, a similar form in an ADP bound to the same SQL
Server table will retrieve all the data before the first record is
displayed.



Is there a way to get similar results with an ADP, so the user does not have
to wait for the entire recordset to download before using the form?



Owen

 

[Sapphire]

Hi,

I had a similar problem and I still do with reports.

I fixed the problem with the form by Setting
the "MaxRecords" property to 1.

This way it shows the form as soon as the 1st record is
retrieved. But you will have to requery everytime user
wants to view the next records. Even though this is
inefficient, it gives the user a perception of speed.

Thanks

 

[Owen Cooper]

Good idea, Sapphire, but I don't think I can do that and satisfy a
requirement that I did not have the foresight to mention: The ability to
search, sort and filter the records behind the form.

Thanks, Owen

 

[SFAxess]

Perhaps you can set the form's Recordset property to an
ADO recordset and manipulate the recordset that way,
giving you the ability to search, sort and filter-
returning only the records that the user truly needs.
You can also use an unbound form similarly, but it would
take more programming.

 

[Owen Cooper]

I tired setting the Recordset property of the form to the ADO recordset in
the On Load procedure. That works fine, but the form still won't open until
the recordset is downloaded all the way.

The current user has an MDB and is used to having as over 10,000 records
behind a sortable, searchable form. The form I available in a jiffy. Can't
rightly tell them they no longer need that. With the new SQL Server
back-end, they expect performance to improve.

I wonder if I just need to switch to an MDB front-end?

Thanks, Owen

 

[Steve Jorgensen]

With a server back-end, that was a bad idea in an MDB too, even though
response was faster. While the user was viewing the first n records, Access
was working in the background retrieving the primary keys for all the records
in the table, even if there are 100,000 of them. If the user ever hit the
button to go to the last record, Access would then read all of those keys as
fast as it can across the network. It's bad for network performance and bad
for server performance.

You really should have some sort of mandatory search criteria or aggregation
that are sufficiently limiting to produce a reasonably sized result set for a
client/server UI.

 

[Owen Cooper]

Yes! Steve, the users do indeed have the ability to limit their search.
Thank you for an under-the-hood view of what was going on.

Owen

 

[Owen Cooper]

Steve, your input has generated a lot of interest among my colleagues. I
would like to do more research on how these recordsets work. Is there a term
that I can search on for this background primary key manipulation that DAO
recordsets do? Also, am I correct in inferring that ADO does not do this
kind of manipulation of keys, instead it always returns all fields for each
record in the recordset?

Thanks again, Owen