Access 2002 Performance Issues

[John]

Hi,

I've recently upgraded from Access 97 to Access 2002 & the performance
basically stinks. I have tried the following items listed below but it
has only had a minor impact:

1) Upgraded Jet 4 to Service Pack 7
2) Set Subdatasheet Name to [None] from [Auto]
3) Shortened the file name of the .MDB file
4) Made sure the Track name AutoCorrect info is off

Other than that I'm going to run the Index Tuning Wizard in SQL to see
if that has any impact.

Any other suggestions would be greatly appreciated.

Thanks

John

[Larry Linson]

Re: Access 2002 Performance Issues

Without some information on the environment... what you have and what you
are doing with it... it is likely to be impossible to help. And,
"performanc e basically stinks" doesn't really give us much to go on. Clarify
with some detail on these issues and perhaps someone can help.

In the meanwhile, particularly if it is a split database, multiuser, with
the data on a different machine in the network, visit MVP Tony Toews' site,
http://www.granite.ab.ca/accsmstr.htm. He has the best collection of
information and links on multiuser Access-Jet performance and avoiding
corruption that I know of.

Larry Linson
Microsoft Access MVP

"John" <jflaherty@focu sintegrated.com > wrote in message
news:713ffdc7.0 309251203.2cfd3 9fb@posting.goo gle.com...[color=blue]
> Hi,
>
> I've recently upgraded from Access 97 to Access 2002 & the performance
> basically stinks. I have tried the following items listed below but it
> has only had a minor impact:
>
> 1) Upgraded Jet 4 to Service Pack 7
> 2) Set Subdatasheet Name to [None] from [Auto]
> 3) Shortened the file name of the .MDB file
> 4) Made sure the Track name AutoCorrect info is off
>
> Other than that I'm going to run the Index Tuning Wizard in SQL to see
> if that has any impact.
>
> Any other suggestions would be greatly appreciated.
>
> Thanks
>
> John[/color]

[Tony Toews]

Re: Access 2002 Performance Issues

jflaherty@focus integrated.com (John) wrote:
[color=blue]
>I've recently upgraded from Access 97 to Access 2002 & the performance
>basically stinks. I have tried the following items listed below but it
>has only had a minor impact:
>
>1) Upgraded Jet 4 to Service Pack 7
>2) Set Subdatasheet Name to [None] from [Auto]
>3) Shortened the file name of the .MDB file
>4) Made sure the Track name AutoCorrect info is off[/color]

LDB locking which a persistent recordset connection or an always open bound form
corrects (multiple users)

For more information on these, less likely causes, other tips and links to MS KB
articles visit my Access Performance FAQ page at

http://www.granite.ab.ca/access/performancefaq.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

[Saintor]

Re: Access 2002 Performance Issues

> Any other suggestions would be greatly appreciated.

Probably one that you won't like; go back to Access 97. I did.

I just hope that it will work for decades on upcoming OS.

[John]

Re: Access 2002 Performance Issues

To add some info on our environment...I am running XP with Access 2002
& utilizing linked tables to SQL 2000. To give you an example of
performance we have one form that went from a 20 second response time
(in Access 97) to over 5 minutes in Access 2002.

Hope this helps.

John

"Larry Linson" <bouncer@localh ost.net> wrote in message news:<lLLcb.645 1$FH3.6407@nwrd dc02.gnilink.ne t>...[color=blue]
> Without some information on the environment... what you have and what you
> are doing with it... it is likely to be impossible to help. And,
> "performanc e basically stinks" doesn't really give us much to go on. Clarify
> with some detail on these issues and perhaps someone can help.
>
> In the meanwhile, particularly if it is a split database, multiuser, with
> the data on a different machine in the network, visit MVP Tony Toews' site,
> http://www.granite.ab.ca/accsmstr.htm. He has the best collection of
> information and links on multiuser Access-Jet performance and avoiding
> corruption that I know of.
>
> Larry Linson
> Microsoft Access MVP
>
> "John" <jflaherty@focu sintegrated.com > wrote in message
> news:713ffdc7.0 309251203.2cfd3 9fb@posting.goo gle.com...[color=green]
> > Hi,
> >
> > I've recently upgraded from Access 97 to Access 2002 & the performance
> > basically stinks. I have tried the following items listed below but it
> > has only had a minor impact:
> >
> > 1) Upgraded Jet 4 to Service Pack 7
> > 2) Set Subdatasheet Name to [None] from [Auto]
> > 3) Shortened the file name of the .MDB file
> > 4) Made sure the Track name AutoCorrect info is off
> >
> > Other than that I'm going to run the Index Tuning Wizard in SQL to see
> > if that has any impact.
> >
> > Any other suggestions would be greatly appreciated.
> >
> > Thanks
> >
> > John[/color][/color]

[Lyle Fairfield]

Re: Access 2002 Performance Issues

jflaherty@focus integrated.com (John) wrote in
news:713ffdc7.0 309291513.34741 16a@posting.goo gle.com:
[color=blue]
> To add some info on our environment...I am running XP with Access 2002
> & utilizing linked tables to SQL 2000. To give you an example of
> performance we have one form that went from a 20 second response time
> (in Access 97) to over 5 minutes in Access 2002.[/color]

New versions of many applications are often less tolerant of error and
inefficiency than those they replace. When performance "basically stinks",
it's likely to be the result of poor design, poor script or poor code.
My XP applications don't run as fast as 97 applications. XP is bigger and
does more, so this isn't so strange. But the difference in speed is almost
unnoticeable. Of course one might think that the things that XP does that 97
can't do are infinitely faster (or infinitely slower, depending upon one's
point of view).

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Steve]

Re: Access 2002 Performance Issues

On 29 Sep 2003 23:26:14 GMT, Lyle Fairfield
<MissingAddress @Invalid.Com> wrote:

<snip>
[color=blue]
>
> Of course one might think that the things that XP does that 97
>can't do are infinitely faster (or infinitely slower, depending upon one's
>point of view).
>[/color]

For a database desktop developer, what are some of the things in XP
that you use which are not provided in 97?

Steven

[Lyle Fairfield]

Re: Access 2002 Performance Issues

steve@nospam.co m (Steve) wrote in news:3f7a0cfb.8 4228243@news.we stnet.com:
[color=blue]
> On 29 Sep 2003 23:26:14 GMT, Lyle Fairfield
><MissingAddres s@Invalid.Com> wrote:
>
><snip>
>[color=green]
>>
>> Of course one might think that the things that XP does that 97
>>can't do are infinitely faster (or infinitely slower, depending upon
>>one's point of view).
>>[/color]
>
> For a database desktop developer, what are some of the things in XP
> that you use which are not provided in 97?
>
> Steven[/color]

PivotChart and PivotTable Views
XML Support
Extended Property Support with Microsoft SQL Server 2000
Round-tripping
Multiple Undo and Redo
Batch Updates in Access Projects using Microsoft SQL Server
Updateable Off-line Data Access Pages
Conversion Error Logging
Subforms/Subreports that Live in Design view
Password Security in an Access Project
The Linked Table Wizard
Improved Support for International Complex-scripts
Improved Accessibility Features
Access 2002 now provides even greater ease in working with forms and
reports.
Pressing F8 in form or report Design will now display the field list.
Pressing ENTER after selecting a field in the field list in form or
report Design view will automatically add the field to the form
or
report design surface. Pressing CTRL+TAB will move the focus
from
a form or report section to a subsection. Two additional powers
(1000% and 500%) have been added to the Zoom option in print
preview.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Larry Linson]

Re: Access 2002 Performance Issues

Have you tried Office 2003 yet, Lyle?

The Beta 2 with the update seems pretty solid to me. I've only used Access,
Word, and PowerPoint to any extent, and Excel once. I haven't used FrontPage
at all (but did hear a caution that it has a nasty bug that didn't get fixed
even in the release version).

Most of the enhancements tend to be for collaboration and enterprise stuff,
however. So, I'm not sure it's going to be a compelling update for everyone.

[Lyle Fairfield]

Re: Access 2002 Performance Issues

"Larry Linson" <bouncer@localh ost.net> wrote in
news:wNqeb.3415 7$FH3.19109@nwr ddc02.gnilink.n et:
[color=blue]
> Have you tried Office 2003 yet, Lyle?[/color]

Not yet ... I keep waiting for everything to be in sync ... new versions of
Windows ... Office ... and .Net ... then I'll buy a ball-buster developement
machine and be up-to-date for a day or two.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Hank Reed]

Re: Access 2002 Performance Issues

Hello,
I have been following this thread and was most interested in
the MS support page named: "How to Speed Up Iterative Processes in
VBA"
Located at: http://support.microsoft.com/?kbid=210408

I copied the slow and fast example functions, created a table
in my backend DB with the example fields, added 1000 records, and
called each function five times from a loop. The difference in time
was minimal: 28 seconds for the slow function and 27 seconds for the
fast function.

This seems kind of insignificant.
Would there be a better way to benchmark this?

Thanks,
Hank Reed

[Saintor]

Re: Access 2002 Performance Issues

Very well explained. Reading this, I was looking for what I really miss
(!). There is only one feature I miss from ACC200X
[color=blue]
>
> PivotChart and PivotTable Views
> XML Support[/color]

I think that there is some wizards to add XML support to ACC97, isn't it?
[color=blue]
> Extended Property Support with Microsoft SQL Server 2000[/color]

Alas, ACC97 will never be a true client/server application. Did anybody
ever see an ADP project significantly quicker than DAO with ODBC? Most
developers I know who did this were disappointed because there was no gain
in speed.
[color=blue]
> Round-tripping[/color]

I don't understand this one.
[color=blue]
> Multiple Undo and Redo
> Batch Updates in Access Projects using Microsoft SQL Server
> Updateable Off-line Data Access Pages
> Conversion Error Logging
> Subforms/Subreports that Live in Design view
> Password Security in an Access Project
> The Linked Table Wizard[/color]

There is one in ACC97 (?)
[color=blue]
> Improved Support for International Complex-scripts[/color]

Unicode?
[color=blue]
> Improved Accessibility Features
> Access 2002 now provides even greater ease in working with forms and
> reports.[/color]

This is what I miss most; jumping quickly from an object-code to another.
But I had serious corruption with code in ACC2000.
[color=blue]
> Pressing F8 in form or report Design will now display the field list.
> Pressing ENTER after selecting a field in the field list in form or
> report Design view will automatically add the field to the form
> or
> report design surface. Pressing CTRL+TAB will move the focus
> from
> a form or report section to a subsection. Two additional powers
> (1000% and 500%) have been added to the Zoom option in print
> preview.[/color]

Also; automatic compact & repair when exiting a db. No need for
msldbusr.dll to get the list of users attached to the current db.

[Steve Jorgensen]

Re: Access 2002 Performance Issues

OK, that helps. In newer versions of something, I'm not sure if it's the
SQL Server ODBC driver or Windows 2000 (did you also upgrade from Windows
98 to 2000 or XP?), the name resolution now tries TCP/IP name lookup first,
then Windows name lookup. This can cause problems if your computers point
to a DNS server that is not aware of the systems on your network.

Basically, what happens is this.
1. Ask the Primary DNS server for the address of the SQL Server ... time
out after 30 seconds.
2. Ask the Secondary DNS server for the address of the SQL Server ... time
out after 30 seconds.
3. Ask Windows networking (NetBIOS or WINS) for the address of the server,
and succeed immediately.

If this is your problem, there are only 2 possible solution I'm aware of.
1. Set up an internal DNS server on your network that -is- aware of your
internal machines, and make that the default DNS server for the comptuers
on the LAN.
2. Add an explicit entry to the HOSTS file on each client machine to
resolve the address of the SQL Server.

On 29 Sep 2003 16:13:07 -0700, jflaherty@focus integrated.com (John) wrote:
[color=blue]
>To add some info on our environment...I am running XP with Access 2002
>& utilizing linked tables to SQL 2000. To give you an example of
>performance we have one form that went from a 20 second response time
>(in Access 97) to over 5 minutes in Access 2002.
>
>Hope this helps.
>
>John
>
>"Larry Linson" <bouncer@localh ost.net> wrote in message news:<lLLcb.645 1$FH3.6407@nwrd dc02.gnilink.ne t>...[color=green]
>> Without some information on the environment... what you have and what you
>> are doing with it... it is likely to be impossible to help. And,
>> "performanc e basically stinks" doesn't really give us much to go on. Clarify
>> with some detail on these issues and perhaps someone can help.
>>
>> In the meanwhile, particularly if it is a split database, multiuser, with
>> the data on a different machine in the network, visit MVP Tony Toews' site,
>> http://www.granite.ab.ca/accsmstr.htm. He has the best collection of
>> information and links on multiuser Access-Jet performance and avoiding
>> corruption that I know of.
>>
>> Larry Linson
>> Microsoft Access MVP
>>
>> "John" <jflaherty@focu sintegrated.com > wrote in message
>> news:713ffdc7.0 309251203.2cfd3 9fb@posting.goo gle.com...[color=darkred]
>> > Hi,
>> >
>> > I've recently upgraded from Access 97 to Access 2002 & the performance
>> > basically stinks. I have tried the following items listed below but it
>> > has only had a minor impact:
>> >
>> > 1) Upgraded Jet 4 to Service Pack 7
>> > 2) Set Subdatasheet Name to [None] from [Auto]
>> > 3) Shortened the file name of the .MDB file
>> > 4) Made sure the Track name AutoCorrect info is off
>> >
>> > Other than that I'm going to run the Index Tuning Wizard in SQL to see
>> > if that has any impact.
>> >
>> > Any other suggestions would be greatly appreciated.
>> >
>> > Thanks
>> >
>> > John[/color][/color][/color]

[David W. Fenton]

Re: Access 2002 Performance Issues

MissingAddress@ Invalid.Com (Lyle Fairfield) wrote in
<Xns9406D037328 52FFDBA@130.133 .1.4>:
[color=blue]
>steve@nospam.c om (Steve) wrote in
>news:3f7a0cfb. 84228243@news.w estnet.com:
>[color=green]
>> On 29 Sep 2003 23:26:14 GMT, Lyle Fairfield
>><MissingAddre ss@Invalid.Com> wrote:
>>
>><snip>
>>[color=darkred]
>>>
>>> Of course one might think that the things that XP does that 97
>>>can't do are infinitely faster (or infinitely slower, depending
>>>upon one's point of view).
>>>[/color]
>>
>> For a database desktop developer, what are some of the things in
>> XP that you use which are not provided in 97?[/color]
>
>PivotChart and PivotTable Views[/color]

How many applications have you ever developed that would have
benefitted from these? How many required it?
[color=blue]
>XML Support[/color]

Have you ever used this, other than to get around the ADP's lack of
local tables?
[color=blue]
>Extended Property Support with Microsoft SQL Server 2000[/color]

Not much use if you're not using SQL Server 2K.
[color=blue]
>Round-tripping[/color]

???
[color=blue]
>Multiple Undo and Redo[/color]

How is this different from other versions of Access? Yes, the Undo
levels are limited, and some things are not undoable, but they
aren't only one level deep. Or do you mean in data editing?
[color=blue]
>Batch Updates in Access Projects using Microsoft SQL Server[/color]

Wasn't this possible with ADO in A2K?
[color=blue]
>Updateable Off-line Data Access Pages[/color]

How many DAPs have you developed? Do they still have the problems
MichKa adduced after the initial release in A2K?
[color=blue]
>Conversion Error Logging[/color]

Conversion from *what*? Earlier versions of Access? Other data
formats?
[color=blue]
>Subforms/Subreports that Live in Design view[/color]

???

I would like to go back to the A97 method where the
subforms/subreports are not embedded in the form/report, because
there just isn't enough space. At least, I'd like the option to
turn off the new behavior and return to the A97 behavior (which for
me is far, far easier in every way).
[color=blue]
>Password Security in an Access Project[/color]

What kind of password security? And if you're not using ADPs, is
this relevant?
[color=blue]
>The Linked Table Wizard[/color]

What about it? Improved? What improvements?
[color=blue]
>Improved Support for International Complex-scripts[/color]

Improved over A2K? How?
[color=blue]
>Improved Accessibility Features[/color]

Do you have clients that use these? What are those improved
features?
[color=blue]
>Access 2002 now provides even greater ease in working with forms
>and reports.
> Pressing F8 in form or report Design will now display the
> field list. Pressing ENTER after selecting a field in the
> field list in form or
> report Design view will automatically add the field to
> the form
>or
> report design surface. Pressing CTRL+TAB will
> move the focus
>from
> a form or report section to a subsection. Two
> additional powers (1000% and 500%) have been added to the Zoom
> option in print preview.[/color]

While things like these shortcuts in form/report design/view are
nice little additions, none of them really provides functionality
that was not there already, except for the increased magnification
(something I'm not sure is of much benefit). A real benefit would
be a ZOOM in DESIGN view, so that you could deal with closely
spaced and highly formatted reports at the design stage.

Really, Lyle, you're being a shill for Microsoft here, just
repeating the features list that MS has put out. Most of these
things are not "features" that you need personally or use on a
daily basis (if ever).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Re: Access 2002 Performance Issues

saintor1@REMOVE THIShotmail.com (Saintor) wrote in
<WHAeb.72661$tJ 6.1780525@wagne r.videotron.net >:
[color=blue][color=green]
>> Extended Property Support with Microsoft SQL Server 2000[/color]
>
>Alas, ACC97 will never be a true client/server application. . . .[/color]

What? Ever heard of ODBC with passthrough queries?
[color=blue]
> . . . Did
>anybody ever see an ADP project significantly quicker than DAO
>with ODBC? Most developers I know who did this were disappointed
>because there was no gain in speed.[/color]

Imagine that!

[]
[color=blue]
>Also; automatic compact & repair when exiting a db. . . .[/color]

Why is that good?
[color=blue]
> . . . No need for
>msldbusr.dll to get the list of users attached to the current db.[/color]

Using the ADO UserRoster -- the one feature of ADO that makes it
worth using in otherwise DAO-only database (using late binding, of
course).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc