I am off to a client in as couple of hours to fix a problem that
came up in code that was written in late 1997 or 1998 that has been
working without any failures until this week.
The cause of the failure?
Conversion from A97 to A2K3.
The code that failed was the running of a series of append and
update and delete queries that were executed with DoCmd.OpenQuery,
with DoCmd.SetWarnings set to FALSE. The reason the code failed was
that an append query that worked forever in A97 completely fails
under A2K3.
The failure is in a query that draws values from an unbound form,
and any controls on the unbound form that were null were failing.
What I figured out was that these Null controls were being resolved
in A2K3 as zero-length strings, and ZLS was prohibited in the target
fields. I fixed it in two steps:
1. add explicit parameters for all the form control references.
2. return all the control references that can be Null through a
function that converts any ZLS to Null (I have one called
varZLSToNull).
Now, the reason I'm posting is not to explain how to fix this
problem (though I would advise that anyone who is converting from
A97 to A2K3 to check any append queries that draw values from
controls on unbound forms), but to retract a recommendation that
I've been making for years and years and years. That is:
Don't dink with working code.
(that's Larry Linson's version of it, which I like -- others would
say "if it ain't broke, don't fix it")
The problem here is that my original code reflected my inexperience
in coding in Access -- at the time the code was written, I barely
knew how to do it any other way (I did a lot of DAO Executes in code
written for the app later in 1998, though). Nowadays, I'd never use
DoCmd.OpenQuery to execute an action query, nor DoCmd.RunSQL, but
would always use a DAO Execute statement with dbFailOnError and
error trapping (and the code in question would be in a transaction,
which it presently is not). And the reason I use Execute now is
because of the danger of losing an error when you turn SetWarnings
OFF.
For the same reason, I don't use On Error GoTo Next -- what if the
error that happens is not the one I know about and have chosen to
ignore?
So, the lesson is:
For old code, do a periodic review and fix bad things like this that
could lead to errors. All you have to do is refactor the code to
avoid the error and nothing else. In this case, I'm going to rewrite
the event procedure for one command button's OnClick event. I'm also
going to resolve the control references in dynamic SQL, so that
there is no longer any Null problem.
The point is not the solution to my particular problem, just the
general approach to old code:
Sometimes, working code is broken.
Or, put another way:
Sometimes when it ain't broke, it is.
--
David W. Fenton
http://www.dfenton.com/
usenet at dfenton dot com
http://www.dfenton.com/DFA/
Similar Threads
