Forms Mortally Slow

[Ray S.]

I'm using Access 2003 on Windows 2000 Pro machine. My forms are running
mortally slow. Don't know if it's anything I've done. They used to run
quickly, but now I have to wait several minutes for a single form to even
open. What's up with this crap. Will I have to give up using forms altogether?

 

[John W. Vinson]

I'm using Access 2003 on Windows 2000 Pro machine. My forms are running
mortally slow. Don't know if it's anything I've done. They used to run
quickly, but now I have to wait several minutes for a single form to even
open. What's up with this crap. Will I have to give up using forms altogether?

Check some of the suggestions at
http://www.granite.ab.ca/access/performancefaq.htm

In particular, add a constantly-open connection to your backend; make sure the
forms don't return any more records than needed; if you have "heavy" forms
(with multiple subforms), try to set the subforms' recordsources only when
they are needed.

 

[BruceM]

All forms, or in one database only? Is anything else slow (retrieveing a
file from the network, etc.)? Is it a split database? Did this happen
gradually? Do you have a copy from when it ran OK? Are you approaching the
2GB limit? Are your tables properly indexed? Is it only forms, or is it
tables, queries, and reports too? Is there any code when the form opens?

 

[Ray S.]

Basically, all forms except the simplest...no problem retrieving files from
network...not split...what is the 2GB limit?...I'm talking databases about
30,000KB in size.

 

[Ray S.]

I read all the stuff following your link. I checked my version of msjet and
updated to latest version...I'm not using a backend...frankly, some of the
suggestions just seem to lead one to think MSAccess has become useless.

 

[BruceM]

I will be turning off my computer in less than a half hour, and probably
will not return to it until after the weekend, but here are a few thoughts.

First of all, I saw your reply to John Vinson in another part of this
thread. Many people are using Access with very good results, so the problem
is not the program as such. Perhaps there is something wrong with your
particular Access installation. Have you installed all updates?

2 GB is the maximum size of an Access 2003 database file, so that's not it.

I asked if this problem affects all databases, and whether the problem came
on gradually or all at once. If you have the Northwinds database that ships
with Access, try opening that, or download one of the database templates
from the Microsoft web site. The idea is to determine whether there is a
specific file that is causing problems, or if it is any Access file.

I also asked if opening tables, queries, and reports is similarly slow, and
I asked about indexing. I can't get into indexing very much right now, but
essentially in table design view you would apply an index to fields that are
used for sorting, grouping, and searching.

I also asked if there is any code running. If so, try disabling it. Same
for any macros that run when you open the file(s).

There is information here about how to attempt recovery from database
corruption, if the problem is with a specific database:
http://allenbrowne.com/recover.html

This link contains other links to helpful information.

 

[John W. Vinson]

I'm using Access 2003 on Windows 2000 Pro machine. My forms are running
mortally slow. Don't know if it's anything I've done. They used to run
quickly, but now I have to wait several minutes for a single form to even
open. What's up with this crap. Will I have to give up using forms altogether?

Another thing to check is your antivirus software. Some AV apps meddle with
Office applications such as Access. Try seeing if your AV is doing so and tell
it to leave your databases alone...

 

[boblarson]

Also, go to TOOLS > OPTIONS > GENERAL and turn off Name Auto Correct.

 

[Tony Toews [MVP]]

I read all the stuff following your link. I checked my version of msjet and
updated to latest version...

Hmm, that's interesting because msjet40.dll is updated by Windows
Update as it is part of the OS.

I'm not using a backend...

You should consider splitting.

frankly, some of the
suggestions just seem to lead one to think MSAccess has become useless.

The first suggestion is the most important one. The others are
typically significantly rarer but have been added as I've come across
the postings in the newsgroups.

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/

 

[Tony Toews [MVP]]

Another thing to check is your antivirus software. Some AV apps meddle with
Office applications such as Access. Try seeing if your AV is doing so and tell
it to leave your databases alone...

To be a bit more specific than meddling they do antivirus scanning of
the MDB/MDE extension when they shouldn't be.

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/

 

[David W. Fenton]

I read all the stuff following your link. I checked my version of
msjet and updated to latest version...I'm not using a
backend...frankly, some of the suggestions just seem to lead one
to think MSAccess has become useless.

Then why don't you just stop using Access, then?

Access apps don't run slowly unless you're doing something wrong.
There are a host of ways to do things wrong. Most experienced
developers long ago learned best practices. If you don't want to
spend the time learning how to develop in Access properly, then get
the hell out of Dodge now -- no one here is going to be interested
in hearing any whining about how terrible Access is, anymore than a
group of carpenters will have any patience for one of their members
complaining about his hammer.

 

[David W. Fenton]

To be a bit more specific than meddling they do antivirus scanning
of the MDB/MDE extension when they shouldn't be.

....because there's no such thing as an MDB/MDE virus.

 

[a a r o n . k e m p f]

-----------------------------------------------------------------------------------------------------------------------------------------------------------
"Microsoft Jet is a file-sharing database system. A file-sharing
database is one in which all the processing of the file
takes place at the client. When a file-sharing database, such as
Microsoft Jet, is used in a multiuser environment,
multiple client processes are using file read, write, and locking
operations on the same shared file across a network. If,
for any reason, a process cannot be completed, the file can be left in
an incomplete or a corrupted state. Two
examples of when a process may not be completed is when a client is
terminated unexpectedly or when a network
connection to a server is dropped.

Microsoft Jet is not intended to be used with high-stress, high-
concurrency, 24x7 server applications, such as Web,
commerce, transactional, and messaging servers. For these type of
applications, the best solution is to switch to a true
client/server-based database system such as Microsoft Data Engine
(MSDE) or Microsoft SQL Server. When you use
Microsoft Jet in high-stress applications such as Microsoft Internet
Information Server (IIS), customers have reported
database corruption, stability issues such as IIS crashing or locking
up, and also a sudden and persistent failure of the
driver to connect to a valid database that requires re-starting the
IIS service."

 

[Gina Whipp]

The title to which Aaron refers to is:

How to keep a Jet 4.0 database in top working condition in Access 2000

Link: http://support.microsoft.com/kb/300216

Maybe you could find something more recent and pertinent?


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
message
-----------------------------------------------------------------------------------------------------------------------------------------------------------
"Microsoft Jet is a file-sharing database system. A file-sharing
database is one in which all the processing of the file
takes place at the client. When a file-sharing database, such as
Microsoft Jet, is used in a multiuser environment,
multiple client processes are using file read, write, and locking
operations on the same shared file across a network. If,
for any reason, a process cannot be completed, the file can be left in
an incomplete or a corrupted state. Two
examples of when a process may not be completed is when a client is
terminated unexpectedly or when a network
connection to a server is dropped.

Microsoft Jet is not intended to be used with high-stress, high-
concurrency, 24x7 server applications, such as Web,
commerce, transactional, and messaging servers. For these type of
applications, the best solution is to switch to a true
client/server-based database system such as Microsoft Data Engine
(MSDE) or Microsoft SQL Server. When you use
Microsoft Jet in high-stress applications such as Microsoft Internet
Information Server (IIS), customers have reported
database corruption, stability issues such as IIS crashing or locking
up, and also a sudden and persistent failure of the
driver to connect to a valid database that requires re-starting the
IIS service."

 

[Tony Toews [MVP]]

I'm using Access 2003 on Windows 2000 Pro machine. My forms are running
mortally slow. Don't know if it's anything I've done. They used to run
quickly, but now I have to wait several minutes for a single form to even
open. What's up with this crap. Will I have to give up using forms altogether?

Please ignore Aaron Kempf's posting as it has no relevance to your
problem.

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/

 

[Ray S.]

Thanks to most of you. To the person who suggested I get the hell out of
dodge, I'm here precisely because I do want to learn. This group is almost
always very useful, except for people who don't offer anything constructive.
No, I'm not using Access in a multiuser environment. This slowness is in
running the forms on my local machine. My forms used to run just fine in '97
and later in 2000; but when I got 2003, opening the simplest forms became
mortally slow. This is at work, so I can't do much about the antivirus they
run. It's McAffee, but that hasn't changed since when the forms ran fine. I
turned off the Name Auto Correct feature. I updated msjet. I did some reading
in Microsoft's Knowledge Base, and they are aware of forms slowing. Every
"fix" offered by them has not worked for me. I have no problem retrieving
files from network drives. Tables, queries and reports are not affected ...
just forms. I can't tell you how many times I've compacted and repaired the
database. I don't know if my forms are "heavy." Only one has a subform, but
they do display the results of various queries at one time and have hidden
data integrity checks. Anyway, why would they have worked fine in '97 and
2000, but not in 2003? Thanks to all who added something helpful and
constructive.

 

[BruceM]

I would not have suggested you get the hell out of Dodge or anywhere else,
and I think the person who posted that should have thought about it before
pressing the Send button, but he was commenting on your speculation that
"MSAccess has become useless". People who are trying to help may get the
sense they first need to break down a wall of negativity, which is a
difficult way to go.

I posted a link that includes a recovery sequence for databases that are not
performing correctly. Here it is again:
http://allenbrowne.com/recover.html
It is worth doing, to rule out corruption if for no other reason.

Another thing you may as well check are library references:
http://allenbrowne.com/ser-38.html
I don't recall if this suggestion was made, but I don't think I saw it in
this thread.

I can't suggest why there is such a performance difference with Access 2003,
but it should be possible to track down the problem. One approach is that
if a query opens properly but a form based on the query does not, try either
removing all code and macros from the form, or create a new form based on
the query (Auto Form will do for the test).

 

[Ray S.]

I tried creating an invisible form that is always open as per the
suggestions, but I can't even do that. When I try to save the form Access
just hangs forever "not responding..."

 

[BruceM]

Please see my suggestion in another part of this thread a bit earlier today.

 

[a a r o n . k e m p f]

what a stupid workaround.

Move to SQL Server