Application Speed Problem - VERY weird

"Robin Donald" wrote:

Thanks for jumping in and, yes - I forgot that bit.

Each user (4 max) had an mdb front end which he runs from a local copy on
his c: drive. The data is linked with the standard Access "link tables"
facility. This problem occurs even when there is only one user.

Trying to keep it brief: the application, in essence, handles personnel
records. The users view records for people, track record of appointments or
courses, etc. "Drill down" to see successive forms for related records (but
no too deep) allows performance reporting and downloading of selected people
for mailmerging. Nice and powerful but using standard stuff, I would say. No
external clever links or other functionality.

ALSO: before this was upgraded to the Access 2K family, it ran perfectly on
Access 97.

RD

For sake of completeness, as the manager of the system involved, I'd better
add that we have only ever used the database on Access 2K. It worked
perfectly well for several years, then for no apparent reason slowed down!

AN

We experienced a similar abrupt slowdown for our Access front-end,
SQL-Server back-end applications a few years back.

After much head-scratching and (finally) involving the network gurus, we
learned that the network had just been "upgraded" to include a network-wide
anti-virus program. When the internal Access front-ends were explicitly
excluded from the anti-virus app's checking, the apps' response time
returned to normal.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Alan Norris" Alan wrote in message
...


"Robin Donald" wrote:

Thanks for jumping in and, yes - I forgot that bit.

Each user (4 max) had an mdb front end which he runs from a local copy on
his c: drive. The data is linked with the standard Access "link tables"
facility. This problem occurs even when there is only one user.

Trying to keep it brief: the application, in essence, handles personnel
records. The users view records for people, track record of appointments
or
courses, etc. "Drill down" to see successive forms for related records
(but
no too deep) allows performance reporting and downloading of selected
people
for mailmerging. Nice and powerful but using standard stuff, I would say.
No
external clever links or other functionality.

ALSO: before this was upgraded to the Access 2K family, it ran perfectly
on
Access 97.

RD

For sake of completeness, as the manager of the system involved, I'd
better
add that we have only ever used the database on Access 2K. It worked
perfectly well for several years, then for no apparent reason slowed down!

AN

"Jeff Boyce" wrote:

We experienced a similar abrupt slowdown for our Access front-end,
SQL-Server back-end applications a few years back.

After much head-scratching and (finally) involving the network gurus, we
learned that the network had just been "upgraded" to include a network-wide
anti-virus program. When the internal Access front-ends were explicitly
excluded from the anti-virus app's checking, the apps' response time
returned to normal.

Regards

Jeff Boyce
Microsoft Office/Access MVP

The only anti-virus we use is GFI Mail Security which is installed on the
server and only scans ingoing and outgoing e-mail.

AN

Is this the only Access DB you use or are there any other Access DBs with the
same symptoms? It would be nice to know if the problem is tied to just one DB.

Things to try (if you have not already)
(1) MDE front ends not MDB
(2) Compact and repair (front end and back end)
(3) Create a fresh database and import all objects to it

If the DB is reading in a lot of records (like an entire table), I'd change
its operation to just present a selection list based on search criteria. Then
I'd have the DB move thru the records by reading one record from the
selection list at a time. This is a standard technique we use for all our
databases. It may not be the cause but if data transfer is slow, the more
records you are reading, the slower it will be.

Shooting in the dark really.

-Dorian

"Alan Norris" wrote:



"Robin Donald" wrote:

Thanks for jumping in and, yes - I forgot that bit.

Each user (4 max) had an mdb front end which he runs from a local copy on
his c: drive. The data is linked with the standard Access "link tables"
facility. This problem occurs even when there is only one user.

Trying to keep it brief: the application, in essence, handles personnel
records. The users view records for people, track record of appointments or
courses, etc. "Drill down" to see successive forms for related records (but
no too deep) allows performance reporting and downloading of selected people
for mailmerging. Nice and powerful but using standard stuff, I would say. No
external clever links or other functionality.

ALSO: before this was upgraded to the Access 2K family, it ran perfectly on
Access 97.

RD

For sake of completeness, as the manager of the system involved, I'd better
add that we have only ever used the database on Access 2K. It worked
perfectly well for several years, then for no apparent reason slowed down!

AN

Have not tried an MDE rather than an MDB - but we will clutch at any straw
and will try this.

But if I had a penny for every time .....
We have rebuilt both the front and back ends from scratch on numberous
occasions and the back end is reorganised frequently - typically weekly.
A master copy (compacted) is held on a server and each morning each user
runs a "reload" batch program to pull a fresh copy of the master front end
onto his C: drive. This ensures that any "bloat", which can happen to a front
end (even a local one) after repeated use, is eliminated.

Scanning through records would, I am afraid, be vastly impractical. This is
already what we do for some complicated search operations but I cannot see
how to do this for the simple operation of opening a form (with subforms) to
browse through to find individual records and edit/add records.

ALSO - for completeness. If we pull a copy of the back end onto the C: drive
and link a front end to that (i.e. stand alone - no server) it is lightening
fast - as would be expected.

But all suggestions gratefully accepted, thank you.

RD

"mscertified" wrote:

Is this the only Access DB you use or are there any other Access DBs with the
same symptoms? It would be nice to know if the problem is tied to just one DB.

Things to try (if you have not already)
(1) MDE front ends not MDB
(2) Compact and repair (front end and back end)
(3) Create a fresh database and import all objects to it

If the DB is reading in a lot of records (like an entire table), I'd change
its operation to just present a selection list based on search criteria. Then
I'd have the DB move thru the records by reading one record from the
selection list at a time. This is a standard technique we use for all our
databases. It may not be the cause but if data transfer is slow, the more
records you are reading, the slower it will be.

Shooting in the dark really.

-Dorian

"Alan Norris" wrote:



"Robin Donald" wrote:

Thanks for jumping in and, yes - I forgot that bit.

Each user (4 max) had an mdb front end which he runs from a local copy on
his c: drive. The data is linked with the standard Access "link tables"
facility. This problem occurs even when there is only one user.

Trying to keep it brief: the application, in essence, handles personnel
records. The users view records for people, track record of appointments or
courses, etc. "Drill down" to see successive forms for related records (but
no too deep) allows performance reporting and downloading of selected people
for mailmerging. Nice and powerful but using standard stuff, I would say. No
external clever links or other functionality.

ALSO: before this was upgraded to the Access 2K family, it ran perfectly on
Access 97.

RD

For sake of completeness, as the manager of the system involved, I'd better
add that we have only ever used the database on Access 2K. It worked
perfectly well for several years, then for no apparent reason slowed down!

AN

Try shortening the path to the back end. Move it as high up in the
directory tree as you can.

"Robin Donald" wrote in message
...
Have not tried an MDE rather than an MDB - but we will clutch at any straw
and will try this.

But if I had a penny for every time .....
We have rebuilt both the front and back ends from scratch on numberous
occasions and the back end is reorganised frequently - typically weekly.
A master copy (compacted) is held on a server and each morning each user
runs a "reload" batch program to pull a fresh copy of the master front end
onto his C: drive. This ensures that any "bloat", which can happen to a
front
end (even a local one) after repeated use, is eliminated.

Scanning through records would, I am afraid, be vastly impractical. This
is
already what we do for some complicated search operations but I cannot see
how to do this for the simple operation of opening a form (with subforms)
to
browse through to find individual records and edit/add records.

ALSO - for completeness. If we pull a copy of the back end onto the C:
drive
and link a front end to that (i.e. stand alone - no server) it is
lightening
fast - as would be expected.

But all suggestions gratefully accepted, thank you.

RD

"mscertified" wrote:

Is this the only Access DB you use or are there any other Access DBs with
the
same symptoms? It would be nice to know if the problem is tied to just
one DB.

Things to try (if you have not already)
(1) MDE front ends not MDB
(2) Compact and repair (front end and back end)
(3) Create a fresh database and import all objects to it

If the DB is reading in a lot of records (like an entire table), I'd
change
its operation to just present a selection list based on search criteria.
Then
I'd have the DB move thru the records by reading one record from the
selection list at a time. This is a standard technique we use for all our
databases. It may not be the cause but if data transfer is slow, the more
records you are reading, the slower it will be.

Shooting in the dark really.

-Dorian

"Alan Norris" wrote:



"Robin Donald" wrote:

Thanks for jumping in and, yes - I forgot that bit.

Each user (4 max) had an mdb front end which he runs from a local
copy on
his c: drive. The data is linked with the standard Access "link
tables"
facility. This problem occurs even when there is only one user.

Trying to keep it brief: the application, in essence, handles
personnel
records. The users view records for people, track record of
appointments or
courses, etc. "Drill down" to see successive forms for related
records (but
no too deep) allows performance reporting and downloading of selected
people
for mailmerging. Nice and powerful but using standard stuff, I would
say. No
external clever links or other functionality.

ALSO: before this was upgraded to the Access 2K family, it ran
perfectly on
Access 97.

RD

For sake of completeness, as the manager of the system involved, I'd
better
add that we have only ever used the database on Access 2K. It worked
perfectly well for several years, then for no apparent reason slowed
down!

AN

Its possible it's a network problem.

If you have a form bound to a table, you are effectively reading in the
entire table.
It's much more efficient to have the user specify what records they want to
see upfront, then present a selection list of those records, then a mechanism
to page thru the records one at a time.

There are some other optimizing techniques for Access 2000.

Tables:

Set the Subdatasheet Name property on each table in the back-end database to
[NONE].

Database:

Under Options_General:
Make sure Track name AutoCorrect info under Name AutoCorrect is not selected.

Other:

Before creating .MDE file:
“Compile� queries by opening each in datasheet view and closing without
saving.

Tip:

By default, queries are dynaset recordset types, which allow changes.
However, dynasets take longer to display. If you're using a query to fill a
control or as the form's data source, and you don't need to update the
underlying data, try changing the query's recordset type to Snapshot. To do
so, open the query in Design view, right-click on the background, choose
Properties from the resulting shortcut menu, and change the Recordset Type to
Snapshot.


-Dorian



"Robin Donald" wrote:

Have not tried an MDE rather than an MDB - but we will clutch at any straw
and will try this.

But if I had a penny for every time .....
We have rebuilt both the front and back ends from scratch on numberous
occasions and the back end is reorganised frequently - typically weekly.
A master copy (compacted) is held on a server and each morning each user
runs a "reload" batch program to pull a fresh copy of the master front end
onto his C: drive. This ensures that any "bloat", which can happen to a front
end (even a local one) after repeated use, is eliminated.

Scanning through records would, I am afraid, be vastly impractical. This is
already what we do for some complicated search operations but I cannot see
how to do this for the simple operation of opening a form (with subforms) to
browse through to find individual records and edit/add records.

ALSO - for completeness. If we pull a copy of the back end onto the C: drive
and link a front end to that (i.e. stand alone - no server) it is lightening
fast - as would be expected.

But all suggestions gratefully accepted, thank you.

RD

"mscertified" wrote:

Is this the only Access DB you use or are there any other Access DBs with the
same symptoms? It would be nice to know if the problem is tied to just one DB.

Things to try (if you have not already)
(1) MDE front ends not MDB
(2) Compact and repair (front end and back end)
(3) Create a fresh database and import all objects to it

If the DB is reading in a lot of records (like an entire table), I'd change
its operation to just present a selection list based on search criteria. Then
I'd have the DB move thru the records by reading one record from the
selection list at a time. This is a standard technique we use for all our
databases. It may not be the cause but if data transfer is slow, the more
records you are reading, the slower it will be.

Shooting in the dark really.

-Dorian

"Alan Norris" wrote:



"Robin Donald" wrote:

Thanks for jumping in and, yes - I forgot that bit.

Each user (4 max) had an mdb front end which he runs from a local copy on
his c: drive. The data is linked with the standard Access "link tables"
facility. This problem occurs even when there is only one user.

Trying to keep it brief: the application, in essence, handles personnel
records. The users view records for people, track record of appointments or
courses, etc. "Drill down" to see successive forms for related records (but
no too deep) allows performance reporting and downloading of selected people
for mailmerging. Nice and powerful but using standard stuff, I would say. No
external clever links or other functionality.

ALSO: before this was upgraded to the Access 2K family, it ran perfectly on
Access 97.

RD

For sake of completeness, as the manager of the system involved, I'd better
add that we have only ever used the database on Access 2K. It worked
perfectly well for several years, then for no apparent reason slowed down!

AN

mscertified wrote:

There are some other optimizing techniques for Access 2000.

Also see 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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/