AutoExec failure at startup

[robert d via AccessMonster.com]

I have an AutoExec macro that opens my StartAppForm upon loading of my
application. I generally develop my app in Access 2002 (default file format
Access 2000) When I'm on the road, I do development in Access 2000. My
client still has Access 2000.

After developing in Access 2000 for awhile I always seem to reach a point
where I get a failure on my AutoExec. It is an "Action Failed" message that
is shown, with the following:

Macro Name : AutoExec
Condition: True
Action Name: OpenForm
Arguments: StartAppForm, Form,,,,Hidden

I have to click on the "Halt" button to proceed.

If I open up this copy of my app in Access 2002, then the problem usually
goes away. I have never run into this problem with Access 2002 (A2002 has
its own problems, though)

The only thing the AutoExec does is to open the StartAppForm. So I have
tried to double-click on the StartAppForm in the database window, after
placing a code break in the code. When I step through the code, I can't find
an error and if I then hit F5 to continue, the app opens normally??!

So does anyone have any suggestions as to how I can try to locate the problem?
Maybe it is just a form of corruption that will go away, but I don't know
that for sure.

 

[Allen Browne]

Robert, the symptoms you describe suggest that the mdb is corrupting.

Access 2002 uses a different binary for the compiled code than Access 2000
does, even though the file format is the same. So when you take the mdb you
have been developing in A2002 and open it in A2000, Access has to read the
text version of the code and generate the 2000 binary so it can execute the
code. In a perfect world, that would happen transparently. In practice, you
get all kinds of weird problems with code that works in one version but not
in the other.

The workaround is to explicitly decompile the code with A2002, and then
compact the database before switching back to A2000. To decompile, enter
something like this at the command prompt while Access is not running. It is
all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

Note that this fix is actually aimed at problems in code rather than macro.

The other common corrupter is the Name AutoCorrect options, under:
Tools | Options | General
Make sure those boxes are unchecked.
For a short list of some of the problems they produce, see:
Failures caused by Name Auto-Correct
at:
http://allenbrowne.com/bug-03.html

The other major issue to check will be that you have the same version of
msjet40.dll on both computers. Find the file (typically in
windows\system32). Right-click and choose Properties. On the version tab,
you should see:
4.0.8xxx.0
The xxx digits don't matter, but if you do not see the 8, download SP8 for
JET 4 from:
http://support.microsoft.com/gp/sp
Make sure you have SP3 for Office 2000 and SP3 for Office XP as well.

For other general suggestions on avoiding corrupted databases, see:
Preventing Corruption
at:
http://allenbrowne.com/ser-25.html

 

[robert d via AccessMonster.com]

Allen:

Thanks for the response. I already have a hyperlink added to my favorites
for the information you've provided on corruption. It's very good stuff.

I've never tried the decompile switch. Will this work even if my app is
secured? Or do I have to work it in with the existing shortcut to the app
which includes the security information.

I'd like to pass on to you experiences I have with corruption in A2002. I've
noticed that when my app (opened in A2002) opens in a corrupt fashion; i.e. I
get the dreaded "we're sorry for the inconvenience" before the switchboard
opens, and then the subsequent crash of the app, I can't resurrect the app in
any way. Using the MS recommendation of compacting does not work at all
(which I believe you recommend NOT TO DO on your website). I can't open the
app in any way to even try to stop code from executing. That copy of the app
is essentially lost.

EXCEPT: if I open that corrupt copy of the app in A2000 it will open with no
errors and work just fine. I can then reopen it in A2002 and it will work
fine until the next corruption event. This technique has saved me every time
and has worked without fail. I want to credit Danny Seager for suggesting
this to me. I don't know why this works, maybe you do.

Anyway, I thought I would pass it on to you. This could be something that
people could try if all else fails.

Robert, the symptoms you describe suggest that the mdb is corrupting.

Access 2002 uses a different binary for the compiled code than Access 2000
does, even though the file format is the same. So when you take the mdb you
have been developing in A2002 and open it in A2000, Access has to read the
text version of the code and generate the 2000 binary so it can execute the
code. In a perfect world, that would happen transparently. In practice, you
get all kinds of weird problems with code that works in one version but not
in the other.

The workaround is to explicitly decompile the code with A2002, and then
compact the database before switching back to A2000. To decompile, enter
something like this at the command prompt while Access is not running. It is
all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

Note that this fix is actually aimed at problems in code rather than macro.

The other common corrupter is the Name AutoCorrect options, under:
Tools | Options | General
Make sure those boxes are unchecked.
For a short list of some of the problems they produce, see:
Failures caused by Name Auto-Correct
at:
http://allenbrowne.com/bug-03.html

The other major issue to check will be that you have the same version of
msjet40.dll on both computers. Find the file (typically in
windows\system32). Right-click and choose Properties. On the version tab,
you should see:
4.0.8xxx.0
The xxx digits don't matter, but if you do not see the 8, download SP8 for
JET 4 from:
http://support.microsoft.com/gp/sp
Make sure you have SP3 for Office 2000 and SP3 for Office XP as well.

For other general suggestions on avoiding corrupted databases, see:
Preventing Corruption
at:
http://allenbrowne.com/ser-25.html

I have an AutoExec macro that opens my StartAppForm upon loading of my
application. I generally develop my app in Access 2002 (default file

[quoted text clipped - 28 lines]

Maybe it is just a form of corruption that will go away, but I don't know
that for sure.

 

[Allen Browne]

The decompile is probably the piece of the puzzle that you need. You can use
it at the command line, provided you also supply the user name and password
as command line switches.

The bit about being able to open it in the older version and resurrect it is
interesting. That information reinforces my suspicion that it is the
compiled code binaries that are causing the problem for you.

To explain decompile, Access keeps 2 copies of the code in the mdb:
- the text version (what you read and edit);
- the compiled version (what actually runs.)
Sometimes these 2 get out of sync, so that what is running does not match
what you see. A decompile commands Access to discard the compiled version.
It then recreates the binary for itself out of the text version, and so the
sync problem is gone. If it was in fact the text version that had gone bad,
a decompile can cause it to make things worse in the short term. That's
fairly unusual (perhaps 1 in 100 times), but it is always worth making a
backup of the mdb before decompiling.

Things that seem to contribute to this kind of corruption:
1. Editing the code in a form's module while the form is running.
Typically this happens when an error occurs, and Access takes you to the
problem code. If you now start editing without first switching the form to
design view, this can trigger the corruption.

When you begin editing a form, Access makes a temp copy (so you can revert
to the saved copy), so it is now juggling *4* copies of the code (original
an actual binary and compiled.) I suspect it gets confused about which
copies are which if you edit (perhaps even creating the temp copies) in
break mode.

2. Editing an MDB under multiple versions.
Every version of Access uses a different binary. They are forwards
compatible, but not backwards. You can demonstrate this by taking an MDE
(which contains only the compiled binary, not the text version.) An MDE
created in Access 2003 does not work in A2002, even though they are using
the same file format, because the binary is different. However, an MDE
created in A2002 does work in A2002. Likewise an MDE created in A2000 works
in A2002 and 2003.

Hence, the previous suggestion to explicitly decompile (discard the binary)
before switching back to edit the file in A2000.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

Thanks for the response. I already have a hyperlink added to my favorites
for the information you've provided on corruption. It's very good stuff.

I've never tried the decompile switch. Will this work even if my app is
secured? Or do I have to work it in with the existing shortcut to the app
which includes the security information.

I'd like to pass on to you experiences I have with corruption in A2002.
I've
noticed that when my app (opened in A2002) opens in a corrupt fashion;
i.e. I
get the dreaded "we're sorry for the inconvenience" before the switchboard
opens, and then the subsequent crash of the app, I can't resurrect the app
in
any way. Using the MS recommendation of compacting does not work at all
(which I believe you recommend NOT TO DO on your website). I can't open
the
app in any way to even try to stop code from executing. That copy of the
app
is essentially lost.

EXCEPT: if I open that corrupt copy of the app in A2000 it will open with
no
errors and work just fine. I can then reopen it in A2002 and it will work
fine until the next corruption event. This technique has saved me every
time
and has worked without fail. I want to credit Danny Seager for suggesting
this to me. I don't know why this works, maybe you do.

Anyway, I thought I would pass it on to you. This could be something that
people could try if all else fails.

Robert, the symptoms you describe suggest that the mdb is corrupting.

Access 2002 uses a different binary for the compiled code than Access 2000
does, even though the file format is the same. So when you take the mdb
you
have been developing in A2002 and open it in A2000, Access has to read the
text version of the code and generate the 2000 binary so it can execute
the
code. In a perfect world, that would happen transparently. In practice,
you
get all kinds of weird problems with code that works in one version but
not
in the other.

The workaround is to explicitly decompile the code with A2002, and then
compact the database before switching back to A2000. To decompile, enter
something like this at the command prompt while Access is not running. It
is
all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

Note that this fix is actually aimed at problems in code rather than
macro.

The other common corrupter is the Name AutoCorrect options, under:
Tools | Options | General
Make sure those boxes are unchecked.
For a short list of some of the problems they produce, see:
Failures caused by Name Auto-Correct
at:
http://allenbrowne.com/bug-03.html

The other major issue to check will be that you have the same version of
msjet40.dll on both computers. Find the file (typically in
windows\system32). Right-click and choose Properties. On the version tab,
you should see:
4.0.8xxx.0
The xxx digits don't matter, but if you do not see the 8, download SP8 for
JET 4 from:
http://support.microsoft.com/gp/sp
Make sure you have SP3 for Office 2000 and SP3 for Office XP as well.

For other general suggestions on avoiding corrupted databases, see:
Preventing Corruption
at:
http://allenbrowne.com/ser-25.html

I have an AutoExec macro that opens my StartAppForm upon loading of my
application. I generally develop my app in Access 2002 (default file

[quoted text clipped - 28 lines]

Maybe it is just a form of corruption that will go away, but I don't
know
that for sure.

 

[robert d via AccessMonster.com]

I have as separate little mdb that I use to logon to my secure database, so I
added a "decompile" checkbox to this form which now should make it easy for
me to do the decompile.

I decompiled and have noticed an improvement a small increase in overall
speed. After the first compact, the db went from 18 MB down to 10. Then I
compiled and compacted again, and the db when back up to 14. Not sure why
that happened? Compacting again leaves it at 14.

Anyway, how do you verify that the decompile actually occurred? The only way
I could think of was to check whether or not the "Compile" command in the VB
editor was grayed out or not. It seemed to verify that my DB was decompiled.

Is there another way?

Also, would you recommend that I import my dB into a new database.

Thanks.

The decompile is probably the piece of the puzzle that you need. You can use
it at the command line, provided you also supply the user name and password
as command line switches.

The bit about being able to open it in the older version and resurrect it is
interesting. That information reinforces my suspicion that it is the
compiled code binaries that are causing the problem for you.

To explain decompile, Access keeps 2 copies of the code in the mdb:
- the text version (what you read and edit);
- the compiled version (what actually runs.)
Sometimes these 2 get out of sync, so that what is running does not match
what you see. A decompile commands Access to discard the compiled version.
It then recreates the binary for itself out of the text version, and so the
sync problem is gone. If it was in fact the text version that had gone bad,
a decompile can cause it to make things worse in the short term. That's
fairly unusual (perhaps 1 in 100 times), but it is always worth making a
backup of the mdb before decompiling.

Things that seem to contribute to this kind of corruption:
1. Editing the code in a form's module while the form is running.
Typically this happens when an error occurs, and Access takes you to the
problem code. If you now start editing without first switching the form to
design view, this can trigger the corruption.

When you begin editing a form, Access makes a temp copy (so you can revert
to the saved copy), so it is now juggling *4* copies of the code (original
an actual binary and compiled.) I suspect it gets confused about which
copies are which if you edit (perhaps even creating the temp copies) in
break mode.

2. Editing an MDB under multiple versions.
Every version of Access uses a different binary. They are forwards
compatible, but not backwards. You can demonstrate this by taking an MDE
(which contains only the compiled binary, not the text version.) An MDE
created in Access 2003 does not work in A2002, even though they are using
the same file format, because the binary is different. However, an MDE
created in A2002 does work in A2002. Likewise an MDE created in A2000 works
in A2002 and 2003.

Hence, the previous suggestion to explicitly decompile (discard the binary)
before switching back to edit the file in A2000.

Allen:

[quoted text clipped - 84 lines]

 

[Allen Browne]

Decompile works silently.
The decrease in size is an indicator that something was removed.
The increase again after compile is normal, as the compiled binary is
recreated.

Importing into a new database is a good idea if you have other reasons to
suggest a possible corruption, but the decompile is still recommended before
import - especially in A2000 and later due to the change in format
(monolithic save.)

 

[robert d via AccessMonster.com]

Allen:

As I noted in an earlier post, I've added the decompile option to my logon
form, which constructs the shortcut for opening my application.

Today, again, I got the dreaded "We're sorry for the inconvenience........."
upon opening my application (Access 2002 but A2K file format). As I
mentioned in earlier posts, in the past, I've been able to recover the file
by opening it in Access 2000. But instead today I tried just to reopen the
"corrupt" file with the decompile switch set. It opened in A2002.

However, I'm surprised that I got this message. I did not do any development
on my laptop (which has A2K) since decompiling the app as part of actions
taken resulting from this post.

I have to say that all of this makes me a little nervous about deploying my
app. I sincerely hope that once I create the .mde version, that this whole
"corruption" problem will go away.

Thanks

Decompile works silently.
The decrease in size is an indicator that something was removed.
The increase again after compile is normal, as the compiled binary is
recreated.

Importing into a new database is a good idea if you have other reasons to
suggest a possible corruption, but the decompile is still recommended before
import - especially in A2000 and later due to the change in format
(monolithic save.)

I have as separate little mdb that I use to logon to my secure database, so
I

[quoted text clipped - 70 lines]

 

[Allen Browne]

You created a shortcut that decompiles the mdb every time you start it?
That's a bit scary. I suggest you use /decompile only when needed, not every
time the application is launched.

IME, corruption of the database objects is fairly common while you are
developing the database, i.e. creating and modifying them. However, once the
database objects are static and only the data is being modified, a
well-designed database on a stable network with reliable hardware and power
will go for years without a corruption.

Using all the tricks I know in Access 2003, I am down a corruption every
couple of weeks during development. The techniques are those listed in the
article linked earlier in this thread, i.e.:
http://allenbrowne.com/ser-25.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

As I noted in an earlier post, I've added the decompile option to my logon
form, which constructs the shortcut for opening my application.

Today, again, I got the dreaded "We're sorry for the
inconvenience........."
upon opening my application (Access 2002 but A2K file format). As I
mentioned in earlier posts, in the past, I've been able to recover the
file
by opening it in Access 2000. But instead today I tried just to reopen
the
"corrupt" file with the decompile switch set. It opened in A2002.

However, I'm surprised that I got this message. I did not do any
development
on my laptop (which has A2K) since decompiling the app as part of actions
taken resulting from this post.

I have to say that all of this makes me a little nervous about deploying
my
app. I sincerely hope that once I create the .mde version, that this
whole
"corruption" problem will go away.

Thanks

Decompile works silently.
The decrease in size is an indicator that something was removed.
The increase again after compile is normal, as the compiled binary is
recreated.

Importing into a new database is a good idea if you have other reasons to
suggest a possible corruption, but the decompile is still recommended
before
import - especially in A2000 and later due to the change in format
(monolithic save.)

I have as separate little mdb that I use to logon to my secure database,
so
I

[quoted text clipped - 70 lines]

binary)
before switching back to edit the file in A2000.

 

[robert d via AccessMonster.com]

Okay, good to know that even the very experienced have this problem.

No, on my logon form I have a checkbox for decompiling. When I check the box,
the / decompile gets added to the shortcut. I've only checked this box
twice since I started this thread.

Thanks again for all of the good advice and help.

You created a shortcut that decompiles the mdb every time you start it?
That's a bit scary. I suggest you use /decompile only when needed, not every
time the application is launched.

IME, corruption of the database objects is fairly common while you are
developing the database, i.e. creating and modifying them. However, once the
database objects are static and only the data is being modified, a
well-designed database on a stable network with reliable hardware and power
will go for years without a corruption.

Using all the tricks I know in Access 2003, I am down a corruption every
couple of weeks during development. The techniques are those listed in the
article linked earlier in this thread, i.e.:
http://allenbrowne.com/ser-25.html

Allen:

[quoted text clipped - 40 lines]