Access 2007 Object Corruption

"Allen Browne" wrote in
:

Part of my point is that there are so many things interacting
here, that it's very hard to pin down what causes the Access
juggler to occasionally drop the ball.

Sure -- I agree with that, but I think solid development practice
includes frequent compiles and saves even when *not* working in
break mode. That's why I add the toolbar buttons to make it easy for
me to hit the compile every time it occurs to me.

I see the question "should I compile now?" as exactly the same as
"should I save my file now" or "should I backup my file now?" -- all
three of those question should never ever be answered with anything
but YES, YES, YES! If you're asking the question, you should JUST DO
IT.

Sounds like you have developed a work practice that you know works
reliably, whereas I'm often trying to work out what somebody did,
so intentionally NOT following a good work practice, so it's not
surprising if I experience corruptions more often than most.

It actually comes from my A97 practices. The major corruption
problems I had with interrelated standalone class modules happened
in A97, and I discovered that the problems came from the fact that
there were too many interrelationships between the
self-instantiating objects (I might consider never using the New
keyword with class module variables, and always instantiating them
explicitly when needed), and so I quickly learned that I needed to
compile and save after almost every code change.

That was also the project where I was weaned off supertype/subtype
table structures (a main table with 2 or more 1:1 child tables for
each subtype), because it was the problem of trying to manage those
that led me into the complex set of interrelated class modules (each
one representing each of the three subtypes in the app, but all
based on a class module based on the main type). It turned out to
be a disaster, both from the standpoint of managing the code, but
also in terms of performance (a list of all the items of the same
supertype had too many outer joins so that it was very slow; a UNION
ALL was better, and I didn't need editability for the list, but it
was still not fast enough; I concluded that the app likely should
have had a SQL Server back end).

Anyway, I digress....

The 'Compile on Demand' setting is another of those interacting
factors, and really important one. CrazyAccessProgrammer, make
sure that you have this option turned off. It is certainly a
factor. You may also get some help from David's suggestion of his
forcing of the compile/save.

I really think that the reason I don't encounter corruption is
entirely due to those two practices, which I follow religiously --
the COMPILE/SAVE process is just so ingrained into my working habits
that it happens without me even thinking about it.

Just as I decompile and compact regularly as part of the development
cycle, I think the COMPILE/SAVE reflex is crucial to maintaining
stability in the project.

And it has worked well for me.

At least, so far!

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Understood. Will do.

"David W. Fenton" wrote:

=?Utf-8?B?Q3JhenlBY2Nlc3NQcm9ncmFtbWVy?=
wrote in
:

The database I'm working on right now is in development only and
not being used by anyone. So I haven't split the tables out yet.
But yes I know about frontend/backend and prior to deployment the
tables will be split to a backend.

Uh, what Allen and I are telling you is that it may also be an issue
DURING DEVELOPMENT, and that you need to eliminate it as a potential
cause of problems.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Excellent. Thank you for that tidbit I will do that right away.

"David W. Fenton" wrote:

=?Utf-8?B?Q3JhenlBY2Nlc3NQcm9ncmFtbWVy?=
wrote in
:

I've
been paying extra special attention to what I'm doing and find
that I am quite often typing away in 'break' mode.

I do it on a daily basis.

Are you compiling and saving after each change made in break mode?
If not, I suggest you try that (and also make sure you have COMPILE
ON DEMAND turned off). I have not had code corruption in about 5
years. Indeed, the last corruption I can actually recall was in
2002, and it was in A97, and involved a whole buttload of
interrelated standalone class modules.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

The 'Compile on Demand' setting is another of those interacting factors, and
really important one. CrazyAccessProgrammer, make sure that you have this
option turned off. It is certainly a factor. You may also get some help from
David's suggestion of his forcing of the compile/save.

I have Compile on Demand turned off per the advice from this thread thank
you =)


FWIW, I don't split an app until right at the end of the dev. process -
mostly because I find it handy to make structural changes (e.g. add another
lookup table) in the current database. But I doubt this has any impact on
the kind of corruption we are describing here (bad VBA binary, inconsistent
pointers to the objects.)

Normally I don't either because it's a bit of a hassle during the
development. But I'm willing to deal with the hassle if it reduces the
frequency of corruption.

I really appreciate the amount of dialog in this thread on two fronts. First,
because it tells me that, 'I'm not alone' in this which in itself helps me
feel less crazy when my database starts doing bizzare things. Secondly,
because I've learned quite a bit of what to do and not do when developing an
application as it relates to corruption.

Right now I'm wrestling Access in two areas as it relates to this topic:

1) Customizing the OnDelete event. This has been a horrid and slow process.
Any runtime error during the execution of the form's OnDelete VBA code
definatley causes corrupton to my database. It's repeatable for me.

Testing my code in this area is a seies of steps that goes like this: Code
- Compile - Compact & Repair - Exit Access - Make a copy of my database
- Load up the copy of my database - Test the code, rinse repeat.

I have to do this because any unforseen error that occurs just wrecks my
database. Interestingly, as I find the errors, and trap them using OnError
procedurs, I get no corruption despite the fact that the errors still occur,
but are just managed by the code.

Thankfully, I'm just about done in this area and I can move on.

2) The second thing I find myself wrestling with as it is related to
corruption is discerning the difference between true corruption and flawed
code because many times symptoms that suggest the database might be corrupted
are the same as when I have made bad code.

For example, in my database I store a UserId as a public variable and it is
set after the user logs into the database.

In circumstances where I have encountered true database corruption, the
value of the UserId variable is lost and becomes null. This causes all sorts
of errors to occur during my form's Opening, Loading, and OnCurrent events
because those events rely on the UserId to carry out various functions during
those events.

But sometimes, the UserId has gone null due to my own bad code. Then I start
seeing the symptoms of it as I stated above and start thinking the database
is corrupted.

Needless to say, as a result of this thread what I believe my experience
with true corruption has dropped off dramatically and I pursue investigation
of my code much more deeply before concluding that I do in fact have
corruption.

=?Utf-8?B?Q3JhenlBY2Nlc3NQcm9ncmFtbWVy?=
wrote in
:

1) Customizing the OnDelete event.

I basically don't use the OnDelete event. I create a command button
for deletion and then do everything behind it. This means I don't
have any code relating to deletes outside the command button's
OnClick() event, except for the BeforeDeleteConfirm() event, which
has Response = acDataErrContinue (in order to surpress the default
Access delete confirmation message, since I'm providing my own).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Responses in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"CrazyAccessProgrammer"
wrote in message ...
Right now I'm wrestling Access in two areas as it relates to this topic:

1) Customizing the OnDelete event. This has been a horrid and slow
process. Any runtime error during the execution of the form's OnDelete
VBA code definatley causes corrupton to my database. It's repeatable
for me.

When a deletion starts, Access begins an implicit transaction. If you break
out of it and leave the transaction dangling, it is possible that things
could go pear-shaped.

However, it is the data (not the form object) that gets wrapped in a
transaction. So I would have expected any corruption due to bugs here to
corrupt the table, not the form. So unless you also modified something in
the form or its module while the transaction is still active, I would not
expect the form to corrupt.


Exiting the application might be the only way to ensure the incomplete
implicit transaction is terminated.

I have to do this because any unforseen error that occurs just wrecks my
database. Interestingly, as I find the errors, and trap them using OnError
procedurs, I get no corruption despite the fact that the errors still
occur,
but are just managed by the code.

That would make sense: any trapped VBA error is handled and control returns
correctly, so it doesn't leave the transaction dangling.

2) The second thing I find myself wrestling with as it is related to
corruption is discerning the difference between true corruption and flawed
code because many times symptoms that suggest the database might be
corrupted are the same as when I have made bad code.


Yes: it can be hard to track where a bug lies.

For example, in my database I store a UserId as a public variable and it
is
set after the user logs into the database.

That would be correct. If you reset your code, you lose your public
variables.

"Allen Browne" wrote in
:

"CrazyAccessProgrammer"
wrote in message
...
Right now I'm wrestling Access in two areas as it relates to this
topic:

1) Customizing the OnDelete event. This has been a horrid and
slow process. Any runtime error during the execution of the
form's OnDelete VBA code definatley causes corrupton to my
database. It's repeatable for me.

When a deletion starts, Access begins an implicit transaction. If
you break out of it and leave the transaction dangling, it is
possible that things could go pear-shaped.

However, it is the data (not the form object) that gets wrapped in
a transaction. So I would have expected any corruption due to bugs
here to corrupt the table, not the form. So unless you also
modified something in the form or its module while the transaction
is still active, I would not expect the form to corrupt.

Well, in an unsplit app, it's at least theoretically possible that
the kind of corruption that happens to a table with that transaction
also has an effect on the code project. In an unsplit app, they are
all just data pages within a single file, and if the transaction
problem corrupts the headers in some way (or data structures
somewhere above the actual data pages), I could see it corrupting
the code project, too.

This is precisely the kind of thing I was thinking of when
suggesting that splitting was a good idea. For what it's worth, I
only develop the most trivial apps unsplit. Anything that's going to
end up split starts out that way.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/