Form crashes when a design change is made.....HELP

[DJCOX]

I really need some help with understanding this problem. I have a form, which
has a subform linked by a ChildID. When I went to make some changes to this
form, in Design mode, it suddenly crashed Access (and then Access wanted to
send an error report to MS). It didn't matter what I did, I could delete the
subform however, as soon as I tried to save the changes to the main form, it
crashed. Did the same with a back-up copy. The only thing I could do was to
re-create the form from scratch.

So, I'm guessing the form must have been corrupt, but how and why? It was a
very normal form, just recorded information about people and the subform was
a linked address table. No fancy code or anything. Why would it sudden become
corrupt and cause Access to crash? I have had similar problems when I tried
to use the getUser() function posted on various posts here, but that was in a
different form, and thought the error had something to do with the security
settings on my work computer. But this is a completely different form, with
no code. Is this indicative of a bigger problem? And is there thing (like
utility etc) that I can use to check my application for corruption?

 

[Tom Wickerath]

Hi DJ,

When I went to make some changes to this form, in Design mode, it suddenly
crashed Access (and then Access wanted to send an error report to MS).

What does the error signature indicate? You might try having a look at this
KB article to see if anything looks familiar:

Jet 4.0 Database Engine hotfix package
http://support.microsoft.com/kb/943509

The only thing I could do was to re-create the form from scratch.

If you had a backup copy of your database, as you always should have, then
you could have simply imported this form from this backed up copy.

There are lots of possible causes of JET database corruption. Here are two
good web sites that list lots of possibilities:

Preventing Corruption (Allen Browne)
http://allenbrowne.com/ser-25.html

Corrupt Microsoft Access MDBs FAQ (Tony Toews)
http://www.granite.ab.ca/access/corruptmdbs.htm


Are you sharing an entire (unsplit) database? If so, be aware that
Microsoft personnel on the Access Dev. Team have stated that doing so is the
number one cause of JET database corruption. So, if this is a multiuser
application (ie. more than one user accesses it at the same time, then you
definately need to split it).


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[DJCOX]

Thanks Tom.

When I say, All I could do is recreate the form", I mean, even after
importing the form from from a back-up copy, it still crashed when I tried to
apply changes made during design. I can't remember the error exactly, but it
is the one where the crash window asks if you want to send a report to
Microsoft etc.

Also, the DB is split, with a front end and back end, and each user has
their own copy of the front end. It is driving me insane to be honest. I
just tried to add a command botton to a form (which has one command botton
controls on it), and I got a "There is not enough memory etc" type error.
Everytime I open that form, I get the same error.

 

[Tom Wickerath]

Okay, try the following (perhaps you already have?) to see if it helps:

First, make a back-up copy of your database, if you have not already done
so. Try opening your database using the undocumented /decompile option. If
you only have one version of Access installed, you can click on Start | Run,
and enter: msaccess /decompile

The next database that you open will have it's compiled VBA code discarded.
If you have more than one version of Access installed, then create a shortcut
whose target points to the full path of msaccess.exe with this optional
switch included. For example: "C:\Program Files\Microsoft Office
2003\OFFICE11\MSACCESS.EXE" /decompile

Open the suspect database while holding down the Shift key the entire time,
to prevent any startup code from running. After opening the database, do a
compact and repair, again holding down the Shift key. Then open any code
module and compile your VBA code. If this does not fix your database,
continue on with the next paragraphs.

Create a brand new database and immediately disable the NameAutocorrect
feature (see: http://allenbrowne.com/bug-03.html for reasons why you want to
do this). Then import all objects from the suspect database into the new
database, one group at a time. In other words, import all tables (but not
linked tables), then import all queries, then all forms, etc. When importing
tables, it is a good idea to click on the Options >> button and check the
options to import relationships (unless you suspect corruption in a
relationship), along with Menu/Toolbars and Import/Export Specs. While Access
will allow you to import all objects in one operation, the experts at FMS,
Inc. (a Microsoft Partner), have stated that it is best to import objects one
group at a time (Reference:
http://www.fmsinc.com/ubb/Forum12/HTML/000285.html).

Recreate any linked tables from scratch using File | Get External Data |
Link tables...
Access can cache a lot of information about linked tables, which may no
longer be valid, so it's always best to recreate the linked tables from
scratch. When importing local tables, make sure to check the option to import
relationships, menus and toolbars, and import/export specs. If any of the
local tables in the source DB are hidden, you'll need to first unhide them.
You will need to set the checked references to match the source database,
along with any startup options set under Tools > Startup. Going through this
process often times solves corruption problems, because you get a new set of
the hidden system tables (the tables whose names start with "MSYS"). These
system tables are updated appropriately as you import objects.

This may sound like a lot of work, but it really isn't. Creating a new
container DB, disabling NameAutocorrect, importing all objects one group at a
time, re-establishing any linked tables, setting startup options, and setting
references to match the source DB is usually a fairly quick procedure. When
you are in the Visual Basic Editor, in order to check that the references
match the source DB, you should do a Debug > Compile ProjectName as well.


If you have a particular form that still gives trouble, after doing all
this, then post back. I have a few more tricks that might be helpful, but
lets see if this much takes care of it first.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[DJCOX]

Great, thanks Tom. I will go through the complete process of creating a new
container - because this application is far to valuable to my team now for it
to fall over. Just before I do, can please please explain this sentence,
"You will need to set the checked references to match the source database".
I'm not sure what you are referring to.

Thanks again for your wonderful help.

 

[Tom Wickerath]

Open any code module. Then click on Tools > References.

"You will need to set the checked references to match the source database".

Actually, now that I read this again, you may have extra checked references
in the source database that are not required. It is always best to "starve"
the references that are selected. In other words, don't include a checked
reference unless you get a compile error without it. If your code compiles
okay without a given reference, then you do not need it, and should not
include it.

On the form/subform that is giving a problem, I think I would import this
into the new container database directly from your last known backup copy,
instead of importing it from the suspect corrupted copy.

Let us know if this helps!


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________