Avoiding VBA corruption; best practices?

[robert.waters]

Hello,

I have been experiencing crashes and code corruption in my project
(vbe6.dll; a decompile fixes the corruption); for the life of me I
cannot figure out why, and I can't pin down the offending code because
each crash immediately shuts down the database and forces me to
recompile in order to open it again. I am following proper coding
procedures, e.g. creating and destroying objects properly (AFAIK),
closing adodb recordsets before reusing them, etc.. Are there any
guidelines for avoiding code corruption, or is it just a crap-shoot?

I thought perhaps techniques I am using are not proper, such as
executing all my in-code queries via 'recordset = connection.Execute
SQL text' (instead of calling a precompiled query in the queries
collection - even complex queries, with multiple joins); re-using
recordset objects; using err.Raise extensively; reDimming arrays who's
size I don't know at compile time.
I've also created a class to implement a key/value hash, which I pass
to one function via a parameter array (I don't know how many hashes I
need at compile time).

Does anyone have any insight/guidelines regarding how to prevent code
corruption?

Thanks you immensely.
-Robert Waters

[ruralguy via AccessMonster.com]

Maybe some of MVP Allen Browne's suggestions will be helpful.
Preventing corruption: http://allenbrowne.com/ser-25.html

ro***********@gmail.com wrote:

>Hello,

I have been experiencing crashes and code corruption in my project
(vbe6.dll; a decompile fixes the corruption); for the life of me I
cannot figure out why, and I can't pin down the offending code because
each crash immediately shuts down the database and forces me to
recompile in order to open it again. I am following proper coding
procedures, e.g. creating and destroying objects properly (AFAIK),
closing adodb recordsets before reusing them, etc.. Are there any
guidelines for avoiding code corruption, or is it just a crap-shoot?

I thought perhaps techniques I am using are not proper, such as
executing all my in-code queries via 'recordset = connection.Execute
SQL text' (instead of calling a precompiled query in the queries
collection - even complex queries, with multiple joins); re-using
recordset objects; using err.Raise extensively; reDimming arrays who's
size I don't know at compile time.
I've also created a class to implement a key/value hash, which I pass
to one function via a parameter array (I don't know how many hashes I
need at compile time).

Does anyone have any insight/guidelines regarding how to prevent code
corruption?

Thanks you immensely.
-Robert Waters

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200609/1

[robert.waters]

For the sake of posterity, I'd like to post what I've found to be a
useful resource on this subject:

http://www.vba-programmer.com/Snippe...Stability.html

It advises the conservative use of recordsets, and then only
adOpenForwardOnly and adLockReadOnly.

To quote the author:
"The primary reason for database corruption (in a multi-user setting)
is an
interrupted write operation. When the JET database begins to write
a record,
a flag is set, if the operation is interrupted before the flag is
reset -- the
database is considerred corrupt. New Users cannot log in, and the
current Users
must be logged out so the database can be compacted/repaired."

Also, there were some excellent topics on the experts-exchange website
regarding Access DB corruption.

And thanks for your help, ruralguy.

-Robert

[Larry Linson]

"robert.waters" wrote

For the sake of posterity, I'd like to post
what I've found to be a useful resource
on this subject:

http://www.vba-programmer.com/Snippe...Stability.html

>
It advises the conservative use of recordsets, and then only
adOpenForwardOnly and adLockReadOnly.

<SNIP>

I don't know who vba-programmer may be, but that particular advice is not
very good.

By extension, the most stable Access database would be one that did nothing
and was never opened. Of course, that would defeat the purpose of using
Access in the first place. Limiting use is not the answer to "interrupted
writes."

And, of course, if you limit use of recordsets, you'll have to do things
with Forms, and Queries, and such, and they, too, are subject to corruption
from "interrupted writes" which the writer of this advice apparently did not
consider.

The answer is to prevent interruption of the write operation:

(1) stabilize the network, so users do not experience
dropped connections that interrupt the writes.
(2) use a honkin' big uninterruptible power supply to
power servers, network, and user's machines, so
power outages don't interrupt the writes, and
(3) train users to shut down "gracefully," that is, to exit
the Access database application in the proper way
(usually a "Quit" button) and to exit Windows with
the software shutdown, not by turning off power
to their computer.

These, by observation of a good many Access users and developers, are the
three most common provable causes of corruption. But they are not the only
ones.

For other good information, see MVP Tony Toews' site,
http://www.granite.ab.ca/accsmstr.htm. At least, there, you know up front
who collected the information.

Larry Linson
Microsoft Access MVP

[David W. Fenton]

"robert.waters" <ro***********@gmail.comwrote in
news:11**********************@e3g2000cwe.googlegro ups.com:

For the sake of posterity, I'd like to post what I've found to be
a useful resource on this subject:

http://www.vba-programmer.com/Snippe...mber_One_Secre
t_for_Stability.html

It advises the conservative use of recordsets, and then only
adOpenForwardOnly and adLockReadOnly.

To quote the author:
"The primary reason for database corruption (in a multi-user
setting) is an
interrupted write operation. When the JET database begins to
write
a record,
a flag is set, if the operation is interrupted before the flag
is
reset -- the
database is considerred corrupt. New Users cannot log in, and
the
current Users
must be logged out so the database can be compacted/repaired."

That's about *data* corruption, not VBA project corruption.

So, it has zilch to do with what *I* thought the topic of this
thread was.

And, of course, it's silly advice, as no one with any brains woudl
use a recordset update when a SQL update can do the same thing. The
only time you edit data in a recordset is when it's not possible to
do it with straight SQL.

So, I'd say whoever created that site is an idiot.

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

[David W. Fenton]

"robert.waters" <ro***********@gmail.comwrote in
news:11**********************@e3g2000cwe.googlegro ups.com:

Also, there were some excellent topics on the experts-exchange
website regarding Access DB corruption.

Is the topic database corruption or VBA project corruption? The
issues are completely separate, since a front end won't have any
data and a back end won't have any VBA project.

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