Access 2002 ADP performance

[Vlad]

Hy everyone,
DB is SQL server 2000 sp3, 3,5 GB

Access 2000 ADP used to need a few seconds to open my SQL
server DB. Now with Access 2002 ADP as a front end to the
same SQL back end i need about 10 minutes before i can
browse a table or a view in datasheet view ( can see any
object but cannot browse anything). Access appears to
hang, but it does not. In fact it's performing a lot of
select queries on all the objects of the back end ( i've
traced the activity on the server). Is it a known bug ?
I've searched the knowledge but found nothing.

Any comment would be appreciated

bye

 

[Mary Chipman]

Not a bug, that's just the way it works. Every query has a cost, and
running a whole bunch of them at once against large tables is going to
be quite expensive. You need to architect your app to fetch only
needed data, not the whole shooting match at once.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

 

[Vadim Rapp]

V> In fact it's performing a lot of select
V> queries on all the objects of the back end ( i've
V> traced the activity on the server). Is it a known
V> bug ? I've searched the knowledge but found
V> nothing.

In production, use forms. During the design, it's expected - Access is
retrieving the info about all relationships in the database to assist you.

Vadim

 

[BJ Freeman]

try this. disable any start up forms.
click on the a section that is not related to SQL server, like macro, forms,
modules.
save the aDP.
open the ADP again. it should open fast.
now go the start up code and see what it is doing. if it is a form it may
have a refresh loop in it.

 

[Vlad]

ok thanks that works fine. In fact i use it as a tool for
data maintenance purposes, opening a rec with a form is
easy and quick. Anyway, one doubt remains to me...why did
access 2000 adp open the same views and tables so quickly,
maybe 2002 is working at startup in a different way
loading other informations ?
I think also access 2000 was doing the job to retrieve
relationships info or am i wrong?
Thanks in advance.

 

[BJ Freeman]

It also my be that the indexing and primary keys you had setup have changed
on the server, or the way the ADP2002 interacts with them.
Since I don't use AC2002 I am going from experience I have run into using
AC2000 and watching the profiler a lot.. LOL.
I am making some educated guesses.

 

[Vadim Rapp]

V>> ok thanks that works fine. In fact i use it as a
V>> tool for
V> data maintenance purposes, opening a rec with a
V> form is easy and quick. Anyway, one doubt remains
V> to me...why did access 2000 adp open the same views
V> and tables so quickly, maybe 2002 is working at
V> startup in a different way loading other
V> informations ?

2002 is doing this extra job in order to better assist you. It's like in the
past in order to print an image, you could double-click on a .bmp , open it
in paintbrush, and click once again to print. Now when you double-click, you
have a helpful multi-step wizard, including full scanning of all your
documents and offering you to print them all, so you have to uncheck each
one individually; then refuse to resize it; then probably refuse to mail it
to all your friends from all your address books found on your computer
during another full scan; etc. etc. So it's all there to help you and to
make things easier for you.

Vadim