Why does my query run fast, but opening for form is slooowwwww?

[Kelvin Beaton]

My database is as follows
Access 2003
Access 2000 file format
MS SQL Server 2000 as the backend

I have form with a number of sub forms
To troubleshoot, I'm just opening the one sub form (frmCaseNotes)
It takes about 15 - 17 seconds to open
There are about 1052 records.
The query behind this form opens in a flash.
The main Client form takes 30-90 seconds to load.
This database was running fine till I made what I thought were a few minor
changes and now it's really slow.

I've run compact and repair.
I've created a new MDB file and imported everything into it, still slow...

Is there a different approach I chould be taking to resolve this?

Any help would be appreciated.

Kelvin

 

[Guest]

hi,

Try the Performance Analyzer in MS Access. Choose Tools --> Analyze -->
Performance. Then sellect any queries or tables you want to increase the
speed on. MS Access wil make suggestion as to how to improve efficiency, and
you may be able to check the selection for MS Access to perform these
suggestions.

Hope this helps,
geebee

 

[Kelvin Beaton]

Thanks

Well is sugests that I add "Option Explicit" statement.
I fund this artical
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions that says the
following.
"Unfortunately, the VB Editor is not configured to include Option Explicit
in code modules by default. You can change this behavior for all new code
modules (this does not affect any existing modules) by opening the VB
Editor, then selecting the Tools -> Options menu. Select the Editor tab and
place a check in the "Require Variable Declaration" check box."
I can't see an Editor tab under options in Access 2003.
Am I looking in the wrong place?

Thanks for the help

Kelvin

 

[Kelvin Beaton]

I see it's in the tools/options of the editor not Access...

Sorry about that

Kelvin

 

[Kelvin Beaton]

When the analizer says to "Use an Option Explicit statement" for a report,
where would one add that?
Would I create an on open event and add that statement to the top of it???
Seems a bit odd if this is what it's saying to do.

Thanks

Kelvin

 

[Guest]

hi,

Not sure what other things the Performance Analyzer suggested, but if it
didn't suggest anything other than adding an "Option Explicit", it is
unlikely that adding them will increase query speed, especially if you don't
use variables here. This is because adding them is not a requirement for
your database to run; rather, simply a good practice. Anyway, "Declaring
variables" is the practice of telling the compiler what variables you are
going to use and what type of data types those variables are. This is done
with the "Dim" (short for the archaic term "dimension") statement. For
example,

Dim VariableName As Long

declares a variable named VariableName as assigns a data type of Long to
that variable. Strictly speaking, declaring variables is optional -- if you
don't declare a variable, the compiler will automatically create that
variable the first time it is used in code. However, it is VERY BAD practice
not to declare your variable and there is NO excuse not to do so. None.
You can require variable declaration by putting the following line of code
at the very top of your code module, before and outside of
any procedure:

Option Explicit

This tells the compiler to raise an error if a variable is used but not
declared. You can tell the VBA Editor to automatically include Option
Explicit in all new modules by going to the Tools menu, choosing Options,
selecting the Editor tab, and placing a check next to the "Require Variable
Declaration" option.

Hope this helps,
geebee

 

[Kelvin Beaton]

Hi GeeBee

Thanks for the explination on these items.
I added the statement "Option Explicit" and it didn't make any difference.
I did turn it on it will be on by default in the future.

The only other thing it suggested was using less controlls.
There are a lot of controls, but there's about the same amount as when it
ran fine.....

I'll keep digging.

Thanks again,

Kelvin

The only other thing it said was

 

[Guest]

hi,

Also just curious as to what "few" changes you made before things started
running markedly slower? Did you add any calculated fields in the form? In
the query? Opening another form simultaneously?

geebee

 

[John Spencer]

Since it seems to be the form that is the problem, since you originally said
the query runs quickly.

I would check that Track name AutoCorrect is off
(Tools: Options: General Uncheck all name AutoCorrect features)


Check out the following URLs for a more complete discussion.

http://www.granite.ab.ca/access/performancefaq.htm
http://support.microsoft.com/?id=209126


Or try the following
0. Make a backup copy of the file.
00. Make a backup copy of the file.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why: http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

 

[Naeem]

Since you have said query runs fast, and you have imported all the
database objects into a new database.
you are using SQL-Server so here are a few things to notice:
1- your query runs fast, so there shouldn't be much probelm with the
record source of your from but, do you have any combo boxes or lists in
your form?

are they also opening fast enough?

When you open a form, acess will requery all the combo boxes, list
boxes and subforms, your problem might be in one of these objects.

and if you have modified your tables structure in SQL-Server
(specifically fields with indexes) you can have problems with indexes
defragmentation.