Database opens slowly.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 2003.

This is a front end whose tables are attached to an access or SQL server
back end data container. It is 3 megs large as an MDE. It's autoexec runs a
Main function. The time it takes to get to the main function is about 20
Seconds.

I'm assuming that if it is an MDE it's vba does not need to be compiled. I
have to conclude that the time it takes is simply loading it from the hard
drive.
I tried importing the tables to time the difference. The result is a bit
better by about 10%.

If I could start an unbound form while it reaches the Main function that
would be acceptable but nothing happens until it is done whatever it does to
reach the autoexec macro.

I've tried listing the splash screen form in the display form/page but it
comes up in about the same time as it takes to reach the AutoExec.

Is there a way to bring a form up while it it loading?

Thanks for your help.
 
Thanks for the quick response.

I tried to decompile but it tells me that the VBA project in the database is
corrupt.
I was able to run it again after that. I did a few more things on the list
of recommendation and now it open in half the time.

Still, facing a grey sceen for 10 to 15 seconds depending on the speed of
the computer is not ideal if I can help it.

Any thoughts on showing something other than the blank background of the
empty access database window while it opens.

I dont suppose its possible to change the background without opening a form?
 
Your MDE *is* on the local drive, right?
Thanks for the quick response.

I tried to decompile but it tells me that the VBA project in the database is
corrupt.
I was able to run it again after that. I did a few more things on the list
of recommendation and now it open in half the time.

Still, facing a grey sceen for 10 to 15 seconds depending on the speed of
the computer is not ideal if I can help it.

Any thoughts on showing something other than the blank background of the
empty access database window while it opens.

I dont suppose its possible to change the background without opening a form?
See if any of the suggestions in this link help:
http://www.granite.ab.ca/access/performancefaq.htm
Click to expand...
[quoted text clipped - 22 lines]
Click to expand...
 
Yes it is.

So is the Access data container for the purpose of this test. Putting it on
a mapped network drive does not affect the opening time and it has a minor
effect on data access generally.

--
RobGMiller


ruralguy via AccessMonster.com said:
Your MDE *is* on the local drive, right?
Thanks for the quick response.

I tried to decompile but it tells me that the VBA project in the database is
corrupt.
I was able to run it again after that. I did a few more things on the list
of recommendation and now it open in half the time.

Still, facing a grey sceen for 10 to 15 seconds depending on the speed of
the computer is not ideal if I can help it.

Any thoughts on showing something other than the blank background of the
empty access database window while it opens.

I dont suppose its possible to change the background without opening a form?
See if any of the suggestions in this link help:
http://www.granite.ab.ca/access/performancefaq.htm
Click to expand...
[quoted text clipped - 22 lines]
Thanks for your help.
Click to expand...
Click to expand...
Click to expand...
 
You should be able to open a Splash form (that has no RecordSource) quickly
and then open the other form invisible and when loaded, turn on the
visibility and close the Splash form.
Yes it is.

So is the Access data container for the purpose of this test. Putting it on
a mapped network drive does not affect the opening time and it has a minor
effect on data access generally.
Your MDE *is* on the local drive, right?
Click to expand...
[quoted text clipped - 18 lines]
Click to expand...
 
RobGMiller said:
I tried to decompile but it tells me that the VBA project in the database is
corrupt.
Click to expand...

The decompile is what I would do first in your situation but you got a
corrupt VBA message. I'd suggest importing the database into a new
MDB and then trying the decompile.

Note that orphaned compiled code, which decompile will strip out, gets
copied across when you create an MDE.

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/
 
I've tried to open an unbound splash form as soon as the Autoexec is run or
by making it the primary display form. Either methods takes about as
objectionably long to reach the code that opens the form.

I think there is something wrong other than the size of it. Do you know of a
place where the startup process prior to the Autoexec or Opening the primary
display form might be found.

I did get an error when trying to decompile. However, I don't know how to
get around it. Incidentally, the decompile, compact and recompile method used
to clean up the database and speed it up a bit did corrupt it to the point
where I had to go back to the backup.

I might have to remove so code one bit at a time to see which code the
decompile process objects to.

--
RobGMiller


ruralguy via AccessMonster.com said:
You should be able to open a Splash form (that has no RecordSource) quickly
and then open the other form invisible and when loaded, turn on the
visibility and close the Splash form.
Yes it is.

So is the Access data container for the purpose of this test. Putting it on
a mapped network drive does not affect the opening time and it has a minor
effect on data access generally.
Your MDE *is* on the local drive, right?
Click to expand...
[quoted text clipped - 18 lines]
Thanks for your help.
Click to expand...
Click to expand...

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
Click to expand...
 
Did you try Tony's suggestion of copying importing everything into a new,
fresh mdb?
I've tried to open an unbound splash form as soon as the Autoexec is run or
by making it the primary display form. Either methods takes about as
objectionably long to reach the code that opens the form.

I think there is something wrong other than the size of it. Do you know of a
place where the startup process prior to the Autoexec or Opening the primary
display form might be found.

I did get an error when trying to decompile. However, I don't know how to
get around it. Incidentally, the decompile, compact and recompile method used
to clean up the database and speed it up a bit did corrupt it to the point
where I had to go back to the backup.

I might have to remove so code one bit at a time to see which code the
decompile process objects to.
You should be able to open a Splash form (that has no RecordSource) quickly
and then open the other form invisible and when loaded, turn on the
Click to expand...
[quoted text clipped - 11 lines]
Click to expand...
 
Yes, it did speed it up a bit but it still takes about 15 seconds before
either the autoexec runs or the assigned display form comes up.
--
RobGMiller


ruralguy via AccessMonster.com said:
Did you try Tony's suggestion of copying importing everything into a new,
fresh mdb?
I've tried to open an unbound splash form as soon as the Autoexec is run or
by making it the primary display form. Either methods takes about as
objectionably long to reach the code that opens the form.

I think there is something wrong other than the size of it. Do you know of a
place where the startup process prior to the Autoexec or Opening the primary
display form might be found.

I did get an error when trying to decompile. However, I don't know how to
get around it. Incidentally, the decompile, compact and recompile method used
to clean up the database and speed it up a bit did corrupt it to the point
where I had to go back to the backup.

I might have to remove so code one bit at a time to see which code the
decompile process objects to.
You should be able to open a Splash form (that has no RecordSource) quickly
and then open the other form invisible and when loaded, turn on the
Click to expand...
[quoted text clipped - 11 lines]
Thanks for your help.
Click to expand...
Click to expand...
Click to expand...
 
RobGMiller said:
Yes, it did speed it up a bit but it still takes about 15 seconds before
either the autoexec runs or the assigned display form comes up.
Click to expand...

Ok, now try the decompile on the new MDB. After making a copy of it.

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/
 
Hi Tony,

Yes, I did that as well. I just did it again to be sure because there is the
issue of the "Visual Basic for Applications project in the database is
corrupt" that keeps comming up. After reading about it, I gather that the
message is eroneous and probably has something to do with the fact that this
is a 2003.

In any case, I used a blank database and decompiled first. I got the same
error message. That proves, I suppose, that the message is eroneous. Then I
Imported all the objects, they I decompiled it, then I compacted it. They I
linked all the tables.

Now its a bit faster to open but still takes about 15 seconds before the
autoexec runs or a display form shows but there are all kinds of the types of
errors that are not obvious and difficult to debug.

So I go back to the original and wonder if I can make that splash screen
come up any sooner somehow.

I gather that there is nothing I can do other than try to figure out what
objects break when the above technique is used to speed it up and why it
really doesn't speed it up enough.

If the application compiles without error and does work well then what kinds
of things can keep it from loading fast and what kinds of things can cause a
decompile to break the objects to the point where they wont work any longer.
 
Now its a bit faster to open but still takes about 15 seconds
before the autoexec runs or a display form shows but there are all
kinds of the types of errors that are not obvious and difficult to
debug.
Click to expand...

How do you know it's pausing before running the Autoexec? Maybe it's
running it and it's just taking 15 seconds from the start of the
Autoexec before you see anything onscreen.

Does your Autoexec open any forms bound to tables in your back end?
If so, perhaps the slowdown is there. I don't know if there's any
way to insert a DoEvents in a macro, but perhaps you could move
everything the macro does into code, and put DoEvents after each
step in turn and see if that helps.

I've found that just showing something onscreen makes the startup
*feel* faster, even if the total time is the same.
 
I've tried listing the unbound splashscreen in the Startup display
form/report and opening it from the autoexec. In both cases they open at
about the same time which is roghly 15 seconds on the development computer.

It does open a bit faster on faster systems but my feeling is that there is
something that it gest stuck on temporarily as it opens.

Ideally, I'd like to determine if it is possible to open the splash screen
before much of the processing that happens as the database opens. Ultimately,
I'd like to get rid of whatever stops it from opening faster.

I know there is something wrong because I've produced other applications
that were much larger in terms of the number of attached tables, queries,
forms and VBA modules without this issue.

I've never had to ask myself this question before but I imagine that Access
would not have to load the entire database file before it displays some of
the application.

The question is, what does it have to load before it has enough code to
display an unbound form?

I've tried using a blank databases, importing the splashscreen and listing
it in the Startup.Display Form/report pulldown. The odd thing is that when I
first create that shell it is 9 megs and 2 megs after it is compacted.

It shows the splash screen a bit faster but still unexpectedly slow at 10
seconds.
 
RobGMiller said:
Yes, I did that as well. I just did it again to be sure because there is the
issue of the "Visual Basic for Applications project in the database is
corrupt" that keeps comming up. After reading about it, I gather that the
message is eroneous and probably has something to do with the fact that this
is a 2003.

In any case, I used a blank database and decompiled first. I got the same
error message. That proves, I suppose, that the message is eroneous.
Click to expand...

No, you still haven't been able to decompile the code. And that
orphaned compiled code is what is causing your lengthy 15 second delay
before anything is executed.
Now its a bit faster to open but still takes about 15 seconds before the
autoexec runs or a display form shows but there are all kinds of the types of
errors that are not obvious and difficult to debug.
Click to expand...

What kind of errors?
If the application compiles without error and does work well then what kinds
of things can keep it from loading fast
Click to expand...

The only thing that I know of that causes a delay before the very
first line of your startup code is executed is orphaned, compiled code
which a decompile is supposed to fix. But, in your case, isn't.
and what kinds of things can cause a
decompile to break the objects to the point where they wont work any longer.
Click to expand...

Now that I don't know. This problem doesn't happen often.

Then I'd suggest the little documented SaveAsText and LoadFromText may
help if an object is corrupted or otherwise behaving weirdly.
At the debug/immediate window type:
Application.SaveAsText acForm,"MyForm","c:\form.txt
You can load the file into a new MDB.
Application.LoadFromText acForm,"MyForm","c:\from.txt"

Sample code at http://www.datastrat.com/Code/DocDatabase.txt for
saving all objects in an MDB.

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/
 
RobGMiller said:
I've tried listing the unbound splashscreen in the Startup display
form/report and opening it from the autoexec. In both cases they open at
about the same time which is roghly 15 seconds on the development computer.
Click to expand...

This delay, in my opinion, is caused by the orphaned, compiled code
which a decompile is supposed to clear out. But isn't in your case.

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/
 
Back
Top