Access 2003 performance on datasheet view of bound form

[rkershberg]

Recently converted the entire office to Access 2003 as a by-product of
upgrading all users to XP sp2 and Office 2003.

My performance on the Access 2003 datasheet view of a bound (to an
underlying SQL Server view) is so slow the application now is unusable.
There must be some additional settings that I am not aware of (I wrote
all of the code on the back end .. and it has been optimized and runs
extremely well).

Current environment is Access 2003 sp2, SQL Server 2000. The datasheet
view is selected for the form which has a view that it is bound to.

Can I get some assistance to bolster performance ?

Thanks,

Rob
(rk********@gmail.com)

[rkershberg]

Additional info:

Project before the conversion was an Access 2000 adp. I have even
converted the new file to an Access2003 only file format (adp also)
with little performance boost.

Thanks,

Rob

[rkershberg]

Additional info:

Project before the conversion was an Access 2000 adp. I have even
converted the new file to an Access2003 only file format (adp also)
with little performance boost.

Thanks,

Rob

[Albert D. Kallal]

It is not clear if this is a now a adp project, or a standard mdb file wit
linked tables to sql server?

if your tables are very tiny...say, only 50,000 to 150,000 records, then on
a network, using just a file share should result in instant response times.
and, if you move the back end to sql sever..then while you likely will not
see any performance gains..the response time again should be instant if you
are hitting a back end with such small tables. (in fact, often moving to sql
server results in a slow down. A jet file share is often 2-400% faster then
sql server).

OF course, we do restrict the records loaded to the form...right?

And, of course, any field use to restrict the records loaded to the form is
indexed..right?

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[rkershberg]

Albert:

I mentioned in my follow up that this is an Access ADP project
(converted from Access 2000 to Access 2003).

The real problem is the refresh of the datasheet view (say for
instance, you try to find a row in your result set .. the row is found
quickly, but the refresh of the data is very very slow. In addition,
if you scroll left or right, the entire page is refresh after each
movement of the scroll bar .. it's very (very) slow.

These problems did not exist in the Access 2000 version.

Of course, the result sets are limited to under 50,000 rows. I've had
a dozen or so users pounding on this particular ADP for a few years,
with no real troubles. ONLY since the conversion to Access 2003 did I
begin to encounter the performance problems.

Open to any and all suggestions.

Rob

[Albert D. Kallal]

>

Of course, the result sets are limited to under 50,000 rows.
Well, actually, you should not need more then a few 100 records.

Some type of "search" screen should be used...such as what I out line here:

http://www.members.shaw.ca/AlbertKal...rch/index.html
I've had
a dozen or so users pounding on this particular ADP for a few years,
with no real troubles. ONLY since the conversion to Access 2003 did I
begin to encounter the performance problems.
gee, that is strange. I not seen a lot of posts (if any) that suggest a
particular performance problem when upgrading....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

Open to any and all suggestions.

Rob

[rkershberg]

Albert:

Your points are well taken.

Let me 1st state that I am an MCSD with 10 yrs + experience in SQL
Server. I'm not quite that experienced in Access.

The bigger issue is why did Access 2000 have no performance issues, but
Access 2003 does ?

The screen refresh on the result set, whether going from one page to
another, or scrolling left to right, is horrifically slow. There's
something seriously wrong with the parameters or options in Access.

We have a result set of approx 17,000 to 20,000 rows of data that our
users are constantly manipulating and using. While it would be nice to
have a complex UI to query on specific values, parameters, etc .. this
was NOT needed in the prior version of Access.

It is very frustrating to go thru a painful upgrade for all
workstations, in the hope of having better performance, increased
security and the like .. to find out that an application that had been
working smoothly for years is now unusable.

Rob

[Albert D. Kallal]

> Let me 1st state that I am an MCSD with 10 yrs + experience in SQL

Server. I'm not quite that experienced in Access.
great....just wanted to state the obvious, that is makes no sense to
download ALL accounts to a teller machine, and THEN ask the user for what
account......(we just wasted 1000's of records of bandwidth..and have done
ZERO useful work).

Pulling 20,000 records into a form is a rather large hit...and THEN the user
starts working....(but, lets just
leave this issue for another day).
The bigger issue is why did Access 2000 have no performance issues, but
Access 2003 does ?

I wonder if some tweaking was done in the original? Check the
tools->options->advanced tab

There is some odbc parms you can look at (I don't think this is your
problem...but do take a look).

However, while we are in the advanced tab, I WOULD check the edit/find
tab.... There, is a box for the max number of records in a list...it
defaults to
1000 (can you check your old settings...on a old machine?).

I wonder what else was changed in the install/upgrade....

And yes....a rather frustrating situation you have right now....

Did you change from using a odbc logon to using windows authentication for
loggoin on to sql server? That might be a issue also?

Somthng is wrong here...just don't know what....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[David W. Fenton]

rk********@gmail.com wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:

The bigger issue is why did Access 2000 have no performance
issues, but Access 2003 does ?

It's an ADP. Every new version of Access introduces new bugs into
ADPs, and reverts bugs fixed in older versions.

This is one of the many reasons why many of us have never considered
using ADPs. It's new technology that's unproven.

I wouldn't be surprised if ADPs are dropped in Access 13 or so
(i.e., the version after the next version).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[rkershberg]

Don't ask me WHY ..... but i went in to the tools/options/forms-reports
and changed selection behavior from partially enclosed to fully
enclosed. I don't even know what this IS, BUTTTT ... it turned the
performance upside down. (I had already checked the
odbc/connection/rows settings .. that was not the issue). Mind telling
me what these new settings are in 2003 ???

Thanks for your help.

Rob

[rkershberg]

Yes .. but the ADP project does serve a purpose .. Access dev against
sql server back up. It is nice to have the stored procs, views, etc
at your disposal in Access when you have power users who you don't want
in Enterprise Manager / Query Manager, but you do want to give them
access (no pun intended) to lower level stored procs, etc. I find the
'idea' good, but the implementation by MS avg at best.

Rob Kershberg

[David W. Fenton]

rk********@gmail.com wrote in
news:11*********************@o13g2000cwo.googlegro ups.com:

Yes .. but the ADP project does serve a purpose .. Access dev
against sql server back up. It is nice to have the stored procs,
views, etc at your disposal in Access when you have power users
who you don't want in Enterprise Manager / Query Manager, but you
do want to give them access (no pun intended) to lower level
stored procs, etc. I find the 'idea' good, but the implementation
by MS avg at best.

If that's what ADPs are good for, why are they part of Access? They
should be part of the SQL Server administration tools.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/