Forms load slowly (Calculating. . . .) after database split

[Larry Kahm]

I've been developing a new application for the past few weeks, and now that
it is almost time to deploy it, I split the database and commenced final
testing.

To split the database I made two copies of existing mdb. In the back-end,
I deleted everything except the tables; in the front-end I deleted the
tables and using File, Get External Data, I linked them to the back-end mdb.
I then issued "Compact and Repair" on both files.

Now, when I pull up my forms - and most of them have tab controls, the
response time is horribly slow and the "Calculating. . . " message appears
in the Status bar for between 10 and 15 seconds. Now this is running
locally on my workstation! I don't want to think about what is going to
happen when I put it on my client's network.

Where should I start looking to narrow down the possible causes and
potential solutions?

Thanks!

Larry

 

[Larry Kahm]

Additional information follow-up:

The database experiencing this problem is in A2000 format - I am running
A2003.

I am going to convert the FE mdb to A2003 format to make an mde and see if
that speeds things up at all.

 

[Larry Kahm]

One more follow-up! Found the following site:
http://www.granite.ab.ca/access/performancefaq.htm and I am slowly working
through the issues.

Having created two new folders, one near the root for the BE and another
folder for the FE, I've relinked the tables. Now, not only are the forms
extremely slow to load, but I'm getting the message that the recordset is
not updateable....

Sheesh!

 

[Tom Wickerath]

Hi Larry,

Now, when I pull up my forms - and most of them have tab controls, the
response time is horribly slow and the "Calculating. . . " message appears
in the Status bar for between 10 and 15 seconds.

Try using the Tab On Demand technique.
Have you disabled Name Autocorrect in both the FE and the BE databases? Have
you set all Table SubDatasheets to [None]? Have you established a persistent
connection? Are you attempting to open bound forms to hundreds of records, or
do you have combo / list boxes with hundreds of records? Have you ever used
JET Show Plan to ensure proper indexing is is place? All of these topics, and
more, are introduced in this document:

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm

Also, have you tried decompiling any VBA code in the FE application, then
compact, and follow with a re-compile? Is your system fully patched?

Best Practices
How to keep a Jet 4.0 database in top working condition
http://support.microsoft.com/?id=303528

In particular, follow the three links:

Verify that the latest operating system service pack is installed
Verify that the latest Microsoft Jet service pack is installed and
Verify that the latest service pack for your version of Office is
installed

Also recommended: Install the latest version of MDAC (Microsoft Data Access
Components
http://www.microsoft.com/downloads/...e3-c795-4b7d-b037-185d0506396c&DisplayLang=en


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Larry Kahm]

Tom,

Thanks for a thorough response. Here we go:

The "tab on demand" concep will require significant recoding, but I will
consider it.

Name Autocorrect is off in both dbs; SubDataSheets is set to None for all
tables (VBA code checked that); persistent connections have been established
during FE start-up.

The funny thing: the continuous forms that show dozens of records are quick
to respond. However, when I click on an individual record to pull up the
details, the specific record's data form - which has the tabs and drop-down
lists - is slow to come up.

I will research the Jet ShowPlan item - and then try to find a good document
about how/where to put indexes.

Both the FE and BE databases have been decompiled.

All of >my< software is up-to-date - I have no idea what's going to happen
at my client's site. More about that later this afternoon.

I truly appreciate your help!

Larry

Hi Larry,

Now, when I pull up my forms - and most of them have tab controls, the
response time is horribly slow and the "Calculating. . . " message
appears
in the Status bar for between 10 and 15 seconds.

Try using the Tab On Demand technique.
Have you disabled Name Autocorrect in both the FE and the BE databases?
Have
you set all Table SubDatasheets to [None]? Have you established a
persistent
connection? Are you attempting to open bound forms to hundreds of records,
or
do you have combo / list boxes with hundreds of records? Have you ever
used
JET Show Plan to ensure proper indexing is is place? All of these topics,
and
more, are introduced in this document:

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm

Also, have you tried decompiling any VBA code in the FE application, then
compact, and follow with a re-compile? Is your system fully patched?

Best Practices
How to keep a Jet 4.0 database in top working condition
http://support.microsoft.com/?id=303528

In particular, follow the three links:

Verify that the latest operating system service pack is installed
Verify that the latest Microsoft Jet service pack is installed and
Verify that the latest service pack for your version of Office is
installed

Also recommended: Install the latest version of MDAC (Microsoft Data
Access
Components)
http://www.microsoft.com/downloads/...e3-c795-4b7d-b037-185d0506396c&DisplayLang=en


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I've been developing a new application for the past few weeks, and now
that
it is almost time to deploy it, I split the database and commenced final
testing.

To split the database I made two copies of existing mdb. In the
back-end,
I deleted everything except the tables; in the front-end I deleted the
tables and using File, Get External Data, I linked them to the back-end
mdb.
I then issued "Compact and Repair" on both files.

Now, when I pull up my forms - and most of them have tab controls, the
response time is horribly slow and the "Calculating. . . " message
appears
in the Status bar for between 10 and 15 seconds. Now this is running
locally on my workstation! I don't want to think about what is going to
happen when I put it on my client's network.

Where should I start looking to narrow down the possible causes and
potential solutions?

Thanks!

Larry

 

[Larry Kahm]

Follow-up (and this one's a doozy!) - the forms load up just fine at the
client's site while running in an MDE. No status bar messages, no unusual
delay.

Now, as I stated earlier, I'll take some of the suggestions under advisement
and will be reading the Jet ShowPlan document this afternoon.

For the next few months it will be fun to have very slow response in a
development environment and fast response in the the production environment.

Larry

 

[BruceM]

Don't know if it applies to this situation, but I often link either my
startup form, if it remains open the whole time the database is open, or a
hidden form to a small back end table. In the runtime environment this
keeps the connection to the BE, speeding response time. In development,
however, I may not have that form open, so I create a form that exists
solely to maintain a connection to the BE file. I open that form minimized
before I start work on changes to the FE. It makes it much quicker.

 

[Larry Kahm]

Bruce,

I'm using FMS's Access Admin tool in the application, and it opens two
tables in the back-end during application start-up. I had to muck with the
delivered code to get to a second back-end database (other thread with
Albert) open also.

I have to reiterate that these are A2000 databases and I'm using A2003 for
development. Despite what I told Tom W earlier, apparently all of the
tables have subdatasheet set to [Auto] and there doesn't seem to be anything
I can do to keep them set to [None]. I'm almost certain that slows things
down quite a bit.

In the meanwhile, the Jet ShowPlan output for a small set of tests has
taught me that the Staff table needs an index on the CurrentlyEmployed
field - and no one has caught that in more than 5 years - so I'm going to
spend more time with that.

Larry

 

[Tom Wickerath]

Hi Larry,

It sounds like you are making some progress with using JET Show Plan, and
indexing.

I have to reiterate that these are A2000 databases and I'm using A2003 for
development.

I do most all of my development work using Access 2003 with the Access 2000
file format. I only convert to the 2002-2003 file format, just prior to
creating a .mde file. The reason I prefer to do all of my dev. work in the
2000 file format is shown in this KB article:

Database bloat is not stopped by compacting database with Access 2002
format
http://support.microsoft.com/?id=810415

Despite what I told Tom W earlier, apparently all of the
tables have subdatasheet set to [Auto] and there doesn't seem to be anything
I can do to keep them set to [None].

Make absolutely certain that the Name Autocorrupt option has not been
re-enabled. This means that if you are using Access 2003, you cannot use the
Object Dependencies feature, since this only works by first re-enabling Name
Autocorrect and updating the "name maps". With Name Autocorrect disabled, you
should be able to get the Subdatasheet setting of [None] to stick. If, for
whatever reason, you find that you simply cannot get it to stick, then try
rebuilding your BE database. Use the directions that I show on page 3 in a
Word document that I call Access Links.doc. You are welcome to download a
zipped copy from here:

http://www.accessmvp.com/TWickerath/downloads/accesslinks.zip

The applicable section has a title of:
"My standard advice for attempting to fix minor corruptions:"


Try temporarily disabling your antivirus software, just to make sure that it
is not implicated in the slow performance. Delete all files in your Temp
folder as well. Consider doing a Defragment operation on your hard drive
(with all applications closed, and screen saver disabled, after clearing the
temp folder).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Bruce,

I'm using FMS's Access Admin tool in the application, and it opens two
tables in the back-end during application start-up. I had to muck with the
delivered code to get to a second back-end database (other thread with
Albert) open also.

I have to reiterate that these are A2000 databases and I'm using A2003 for
development. Despite what I told Tom W earlier, apparently all of the
tables have subdatasheet set to [Auto] and there doesn't seem to be anything
I can do to keep them set to [None]. I'm almost certain that slows things
down quite a bit.

In the meanwhile, the Jet ShowPlan output for a small set of tests has
taught me that the Staff table needs an index on the CurrentlyEmployed
field - and no one has caught that in more than 5 years - so I'm going to
spend more time with that.

Larry

 

[Larry Kahm]

Ah, just what I needed, more reading material....


Seriously, that document is worth it's weight in - hmmm, there is nothing of
any value in the financial markets any more - so how about "KUDOS!!!"

Thank you. I'll attempt some of the activities later this afternoon and
over the weekend.

Larry

Hi Larry,

It sounds like you are making some progress with using JET Show Plan, and
indexing.

I have to reiterate that these are A2000 databases and I'm using A2003
for
development.

I do most all of my development work using Access 2003 with the Access
2000
file format. I only convert to the 2002-2003 file format, just prior to
creating a .mde file. The reason I prefer to do all of my dev. work in the
2000 file format is shown in this KB article:

Database bloat is not stopped by compacting database with Access 2002
format
http://support.microsoft.com/?id=810415

Despite what I told Tom W earlier, apparently all of the
tables have subdatasheet set to [Auto] and there doesn't seem to be
anything
I can do to keep them set to [None].

Make absolutely certain that the Name Autocorrupt option has not been
re-enabled. This means that if you are using Access 2003, you cannot use
the
Object Dependencies feature, since this only works by first re-enabling
Name
Autocorrect and updating the "name maps". With Name Autocorrect disabled,
you
should be able to get the Subdatasheet setting of [None] to stick. If, for
whatever reason, you find that you simply cannot get it to stick, then try
rebuilding your BE database. Use the directions that I show on page 3 in a
Word document that I call Access Links.doc. You are welcome to download a
zipped copy from here:

http://www.accessmvp.com/TWickerath/downloads/accesslinks.zip

The applicable section has a title of:
"My standard advice for attempting to fix minor corruptions:"


Try temporarily disabling your antivirus software, just to make sure that
it
is not implicated in the slow performance. Delete all files in your Temp
folder as well. Consider doing a Defragment operation on your hard drive
(with all applications closed, and screen saver disabled, after clearing
the
temp folder).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Bruce,

I'm using FMS's Access Admin tool in the application, and it opens two
tables in the back-end during application start-up. I had to muck with
the
delivered code to get to a second back-end database (other thread with
Albert) open also.

I have to reiterate that these are A2000 databases and I'm using A2003
for
development. Despite what I told Tom W earlier, apparently all of the
tables have subdatasheet set to [Auto] and there doesn't seem to be
anything
I can do to keep them set to [None]. I'm almost certain that slows
things
down quite a bit.

In the meanwhile, the Jet ShowPlan output for a small set of tests has
taught me that the Staff table needs an index on the CurrentlyEmployed
field - and no one has caught that in more than 5 years - so I'm going to
spend more time with that.

Larry

 

[Tom Wickerath]

Hi Larry,

Kudos works for me. I hope you find lots of good "nuggets" of information in
this document. For the immediate issue that deals with the SubDatasheets that
keep reappearing, you only need read ~ 1/2 page--the section on page 3 about
attempting to fix minor corruptions.

I have another zipped Word document that you might be interested in, so I
just posted it to make it available for download. It is called "Taking Out
The Trash.doc". It was written for Windows 2000, and has not been updated
since, but I think you will find it helpful for Windows XP as well. This one
helps identify all the various temp folders, including folders that the
operating system can hide from you, for clearing out junk prior to initiating
a defragment procedure:

http://www.accessmvp.com/TWickerath/downloads/TakingOutTheTrash.zip


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________