"Lars Brownies" <Lars (AT) Browniew (DOT) com> wrote:

No idea. I can't recall the last time I've run a maketable query. I clear records
out and append records. Or I create the table using VBA code. For example see the
TempTables.MDB page at my website which illustrates how to use a temporary MDB in
your app. http://www.granite.ab.ca/access/temptables.htm

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

"Lars Brownies" <Lars (AT) Browniew (DOT) com> wrote in
news:hh6403$1icl$1 (AT) textnews (DOT) wanadoo.nl:

[quoting me:]
Well, you could alter my function so that instead of returning the
recordsaffected, it would return an error object. Since there's
already an optional ByRef parameter for the recordsaffected, you
would not lose any functionality. I often call it like this, though:

Debug.Print SQLRun(strSQL) & " records added."

....and that would no longer work.

So, you'd change the error handler to replace my MsgBox for the CASE
ELSE and have it populate the error object you're returning.

I'd be interested in hearing discussion of whether or not folks
think this would be a better approach.

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

David W. Fenton wrote:
I think the answer will depend somehow on for whom/in what context it is
intended. If it's intended that we be able to catch errors in
development & testing, then i would say the Msgbox is good enough. But
if it's for a production where it is possible that something could go
wrong with the queries (to be honest, I'm struggling to think of a
possible error with query that couldn't be anticipated at all and needs
to be handled at runtime and doesn't indicate a problem with database
design) then we would need something else.

A follow up to the production-error is whether we need to merely
acknowledge an error occurred or require more customized response to
different kinds of errors.

If all we really need is to know an error occurred, a simple fix would
be to have the error handling return a -1 as return value for the
records affected value. This way it shouldn't break code, provided we
don't have a implicit If/Then condition testing for boolean True rather
than explicitly >0 (or >=0 if we don't want to consider zero-records an
error).

For more customized response, it almost seem to me that the answer is to
either remove the error handling from the function or have the function
raise the error using Err.Raise method. In either cases, the burden of
handling errors is shifted to every and each calling procedure, which
immediately increase the discipline required to write the code as well
as complicating the maintainability of the code. In all cases where I've
actually used Err.Raise, it was for my custom class modules to highlight
design limitations or requirements for the other developers' benefit
(and hence should not occur in the production -- I suppose it's a form
of documenting the code and building in a reminder system). If we
intended that error be raised as part of development/testing, then that
would be what I would probably do- One thing I like about Err.Raise is
that I can then customize the source & message so to remind myself and
other developer who use the code that the error came from user-defined
code, not from built-in code and point to the original class/procedure,
and thus prefer this over not having the error handler and letting it
bubble up to the calling procedure.

But for the production use, (and I remind that I struggle to think of a
good example of query erroring out in runtime that isn't indicative of a
bad design) I'm more likely to just settle on getting a return value of
failure (e.g. -1 in the recordsaffected) than getting a custom Err
object so I don't need to write error handling every time I call the
function and can wrap it in a simple If/Then condition.

That's my $0.02 cents.

Banana <Banana (AT) Republic (DOT) com> wrote in
news:4B399873.4010802 (AT) Republic (DOT) com:

Well, given that the purpose of the function is to replace
DoCmd.RunSQL, I'm loathe to make it harder to use. DoCmd.RunSQL
doesn't return anything, and if you pass it bad SQL it will give you
an error.

Right now, it's a piece of code that's in a gray area for me -- I
originally wrote it for other people to use because I got tired of
explaining that if you used CurrentDB.Execute, you needed to use the
dbFailOnError switch, and that meant you needed an error handler.

But once I'd written it, I realized I was writing error handlers
around .Execute statements, so I started using it myself. I wrote it
to work the way I wanted it to work, and I'm happy with it returning
the MsgBox when it errors out, since, as you say, at runtime it's
going to be an indication of a bug that has to be fixed. If it
doesn't inform the user of the error, then I have to handle it where
I call it, and then I'm back to where I was before I started using
it, i.e., writing an error handler for each instance.

I'm going to leave it as it is in my own production code, but others
are welcome to alter it for their own use. I'd like to see the
results, since it might convince me to alter my own version.

But I'm having a hard time imagining how what you've suggested would
make it easier to use in a production app. It would mean writing
more code around each call to it, and the whole point of writing it
is to streamline code. Perhaps an optional parameter could control
what is returned, e.g., and that, in turn, control whether the
MsgBox kicks in or the code runs in "silent" mode and passes back an
error number. But too many optional parameters then makes it harder
to use, and the point is ease of use as a replacement for the
inadequate DoCmd.RunSQL.

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

David W. Fenton wrote:
I suppose that if we were to replicate DoCmd.RunSQL exactly, then we
would throw the error back to the calling procedure.

While I've never used DoCmd.RunSQL (being fortunate enough to read about
Execute very early when learning VBA), I would expect that error
handling is still necessary even if using DoCmd.RunSQL. Wrapping it in
DoCmd.SetWarnings is almost like slapping on an On Error Resume Next
which just hides the error and almost certainly will introduce bugs
which would still spill back into the calling procedure and thus require
handling.

Right. To be honest, I am struggling over whether it really makes sense
to push the error back to the calling procedure because if we were using
DoCmd.RunSQL or CurrentDb.Execute ... dbFailOnError, error handling is
expected. There's an appeal in having the error being handled completely
in the function, and returning -1 to indicate an error can still be used
in your Debug.Print statement without needing the If/Then statement. I
suppose the answer would be whether it is reasonable to expect an error
in production code solely from running a query. Maybe for example, we're
doing a query upon linked table and the link got broken?? And in such
cases, does such error merit handling it locally (in which it should be
expected that error is of a general nature... e.g. it could happen for
any queries), or sending it back to the calling procedure for more
customized response (e.g. the error is cause specifically by SQL being
used). In case of a query referencing a broken linked table, I would be
leaning toward handling it in the calling procedure because it wouldn't
be appropriate for calls where queries does not reference a linked table
at all. OTOH, an error arising from the file being full is something
that could happen to any queries regardless of its intended operation.

I will have to think about that some more. FWIW, I would just have left
your function unaltered because as stated earlier, runtime errors
arising from queries itself is usually indicative of a bug that must be
fixed and I can't remember any class of errors arising from running
queries that were otherwise legitimate and couldn't be predicted at
design time.

Well, you still can do a Debug.Print ... and see if you got a -1 in
return (or just use Debug.Assert), so the If/Then isn't strictly
necessary and you could omit it entirely for cases where you didn't care
whether it succeeded or failed. Modifying the function to return -1 in
event of error for records affected is probably the least invasive
change though we will lose any information about the actual error
(unless it wrote to some kind of persistent placeholder).

Banana <Banana (AT) Republic (DOT) com> wrote in
news:4B3A89BB.3020206 (AT) Republic (DOT) com:

I'm not going to do that. If it's not going to handle the error,
then I need to know the error, since otherwise, how can I debug it?
I just fixed an error reported to me last week whose source was the
MsgBox in this function (because I'd neglected to consider a
particular case of double quotes when using it). If it just returned
-1 I'd have never know what the exact problem was (though I do have
it set up to print the SQL statement to the immediate window, so I'd
at least have had that), since it was data-related and not
logic-based. I expect that most errors that it will throw will, in
fact, be data-releated. For example, I recently forgot to reset the
Autonumber seed on a table where I'd appended some lost data which
had filled in some missing Autonumber values (and consequently reset
the seed value), and the error that was caused was caught by the
error handler in this function. Again, with the -1, I'd have had to
look at the SQL, but running against my testbed data would not have
told me what the error was.

In the case of unhandled double quotes, the SQL string would allow
me to find the error, but in the case of the Autonumber seed reset,
it wouldn't have illuminated the problem. I guess I could print the
error number and error message to the immediate window, but then I'd
have to train the user to copy that information and paste it into
their error report. Then I get into the issue of general error
logging, which would certainly solve the problem overall, but would
not be a generalizable solution (i.e., to use my function, you'd
have to use my error logging routines).

I think I'll leave it simple, as is, and let others change it to
suit. I do rather like the idea of adding an option parameter of
type ErrObject and if it's missing, behave as it's written now, but
if an ErrObject is passed in, populate the ErrObject and silently
exit the function. Then the ErrObject can be examined in the calling
code, but the function would continue to work the way it always has
otherwise. In other words, where you didn't care about the MsgBox,
use it as is, and where you wanted to handle a specific case, pass
in an error object and then deal with it the specific way you'd like
to in that instance.

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