Instability in Access Front End

[AMO]

I have a large MS Access front end. The mdb seems to be becoming unstable.
When I work in the mdb to debug/modify it frequently crashes and has weird
corruptions such that I have to go back to a previous version and start over
again. One form in particular has a lot going on. Working with this form
seems to be the issue.

I am unable to debug code (within this form especially). The code seems to
be working fine – going into debug was in an attempt to identify specific
data issues. When working with debug using known good data the issues still
exist. The problem seems to change based on where I put in a breakpoint, and
the symptom is that data elements on the form are no longer available. The
form works fine when debug is not entered. I have tried a number of places
of going into debug (setting different break points) – the data elements that
become not available change, but I am still unable to debug.

This leads me to think that we might be running into some sort of size issue
where MS ACCESS is not properly managing memory and memory is being
overwritten when debug code is entered. I have successfully debugged areas
of this form in the past, but we have now added more functionality which has
made it larger.

Could the size of the mdb or size of the form be approaching some limit at
which point MS ACCESS looses control of the memory management – and debug
mode throws it over the edge of the limit?

I am using Microsoft Access 2000 9.0.2720
The Front End mdb is approx 10MB when compacted.

I have tried converting the front end to Access 2003, but still have problems.

What are the MS ACCESS size limitations?
How does hitting these limits show up?

Any help that you can give related to these issues/questions would be
greatly appreciated.

Thanks

 

[Allen Browne]

If you edit the module of a form while it is open (i.e. running, not in
Design view), you are very likely to corrupt your database.

Access keeps 2 copies of the code: a text version (what you see and edit),
and a compiled version. Sometimes something goes wrong and the 2 copies get
out of sync. A decompile solves that issue by discarding the compiled
version, which is created from the text again, and so 2 copies are back in
sync.

At the point when you start modifying a form or its module, Access creates a
copy so you can revert to the saved version. It is now jugling 2 copies of
the form, and presumably 4 copies of the code. If you do this while the form
is open and the code is running, there is a good chance that it will confuse
the various copies. If the app was fully compiled at this point, you may
even see that after your changes the Compiled option on the Debug menu is
still greyed out--visible evidence that this edit has corrupted the
database.

We first noticed this back in the Access 97 days, and as soon as we stopped
editing while the code is running, about 3/4 of our corruptions during
development ceased.

In later versions, if you have the Name AutoCorrect boxes checked under
Tools | Options | General, this also causes major corruptions as Access gets
confused about the names of the objects and your corruptions increase.

Additionally, you *really*, *really* need to apply some service packs to
that A2000 installation, and also to JET 4. They're available from:
http://support.microsoft.com/gp/sp

There is another issue with A2002 and 2003 regarding forms with subforms.
Post back if you want more info about that.

 

[AMO]

Thanks Allen --

I would like the info about the issue with 2002/2003 regarding forms with
subforms -- on my plate right now is to convert this Front End to A2003.
Initial conversion seems to be OK -- the instability has not gone away though
(I was hoping). I get frequent crashes. Also, I noticed creating a new MDB
in A2003 is in 2000 file format -- should we not be going to 2003 format?

Do you know of size limits on a particular form? Or is the issue more
likely related to the form/subform interaction? The form with the issue has
a complex query behind it with a subform with a complex query that is fed
with info from the main form for calculations.

I have edited code in debug mode routinely over that past ?? years (starting
with Access 1.x? ) I had not noticed taht being associated with crashes --
but since using A2000 have noticed more crashes. I'll try to determine if
the problem goes away without editing in debug (that's a challenge!).

AMO

 

[ZRexRider]

RE: There is another issue with A2002 and 2003 regarding forms with
subforms.
Post back if you want more info about that.

I would also like to learn more about these "issues". I going crazy in
a FORM/SUBFORM issue that causes MS Access 2003 (SP1) to crash fatally
when it hits a syntactically correct line (sometimes).

Sometimes trying to write good programs with MS Access is like building
a house on the side of a muddy hill. No matter how pretty your house
is - it ends up in a pile of rubble at the bottom of the hill.

Thanks

 

[Allen Browne]

Okay, I'm not aware of any Microsoft documentation on this, but it seems to
be a flaw in the way Access handles the AccessField type.

Picture a main form for Invoice with InvoiceID (primary key), and a subform
for InvoiceDetail. The subform's table has an InvoiceID foreign key, but you
typically don't show it in the subform. That means the field named in the
LinkChildFields is part of the subform's RecordSource but is not a control
on the subform. LinkChildFields therefore refers to an object of type
AccessField, and this seems to be the cause of crashes in A2002 and 2003.

The workaround is to place a text box in the subform for the field(s) named
in LinkChildFields. Access then treats it as referring to an object of type
TextBox which does not have the bug, and so you avoid the crash.

So, always put a text box in the subform for each field named in
LinkChildFields. Set its Visible property to No if you like.

In practice, there's a bit more to getting Access to recognise the change.
You could delete the LinkMasterFields and LinkChildFields entries, save the
form, and compact. Then assign them again so Access will now associate the
LinkChildFields with the text box in the subform. Having Name AutoCorrect
off during this time would also be highly desirable.

For other suggestions on avoiding crashes in Access, see:
Preventing Corruption
at:
http://allenbrowne.com/ser-25.html

 

[Albert D.Kallal]

A few things:

Do you ever allow more then one user into this front end? (you don't want to
do that...you need to give each user a copy on EACH computer).

Can you create a mde, and, also any reason why a mde is not being deployed
to uses? (this two questions!).

And, just in case you are not deploying that mde file to each computer, you
can read about why this makes sense here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

 

[Albert D.Kallal]

I would also like to learn more about these "issues". I going crazy in

a FORM/SUBFORM issue that causes MS Access 2003 (SP1) to crash fatally
when it hits a syntactically correct line (sometimes).

Does a mde work ok?