Should I trust Martin Green's website
Strasser
He seems to be a gifted teacher (so I recommend the site for some
things like using SQL in the recordSource property of a form or using
SQL from within VBA).
I am inclined to send the website $10 for the free materials I've used
via PayPal.
However, I don't trust websites without a recommendation from some
neutral source.
Do you trust the www.fontstuff.com site?
Is there some web site verifier that you know about?
Thanks in advance.
Strasser
Tom van Stiphout
<wsfst...@yahoo.com> wrote:
You could google him and find out more.
-Tom.
Microsoft Access MVP
Bob Phillips
I have met Martin, and I know that this is a genuine site, and it is his.
As you say, he is a gifted teacher, and I am sure he would appreciate your
acknowledgement of his capabilities.
Bob
"Strasser" <wsfst...@yahoo.com> wrote in message
news:78afc7be-b41a-4424...@g1g2000vbr.googlegroups.com...
Strasser
> Strasser,
>
> I have met Martin, and I know that this is a genuine site, and it is his.
>
> As you say, he is a gifted teacher, and I am sure he would appreciate your
> acknowledgement of his capabilities.
>
> Bob
>
>
> news:78afc7be-b41a-4424...@g1g2000vbr.googlegroups.com...
>
> >I have come acrosswww.fontstuff.comby Martin Green.
>
> > He seems to be a gifted teacher (so I recommend the site for some
> > things like using SQL in the recordSource property of a form or using
> > SQL from within VBA).
>
> > I am inclined to send the website $10 for the free materials I've used
> > via PayPal.
>
> > However, I don't trust websites without a recommendation from some
> > neutral source.
>
> > Do you trust thewww.fontstuff.comsite?
>
> > Is there some web site verifier that you know about?
>
> > Thanks in advance.
> > Strasser
Thank you both!
Strasser
![Tony Toews [MVP]'s profile photo](http://lh3.googleusercontent.com/a-/ALV-UjVFNFhAO8kxGtutFBSPYzx5Lbrrty7WRKPHNhjuWtTEHvsoQdk=s40-c)
Tony Toews [MVP]
>I have come across www.fontstuff.com by Martin Green.
I very briefly checked out his page on the NotInList event. While exhaustive he did
one thing in his code that highly irritates me. He used docmd.setwarnings which we
should never, ever use.
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
The problem with DoCmd.RunSQL is that it ignores any errors. Either of the
following will display any error messages received by the query. If using DAO, use
Currentdb.Execute strSQL,dbfailonerror.. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected, adCmdText You
can then remove the docmd.setwarnings lines.
If you're going to use docmd.setwarnings make very sure you put the True statement in
any error handling code as well. Otherwise weird things may happen later on
especially while you are working on the app. For example you will no longer get the
"Do you wish to save your changes" message if you close an object. This may mean
that unwanted changes, deletions or additions will be saved to your MDB.
Also performance can be significantly different between the two methods. One posting
stated currentdb.execute took two seconds while docmd.runsql took eight seconds. As
always YMMV.
Sure, send him the $10. Seems decent enough.
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 free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/
Tony Toews [MVP]
>I very briefly checked out his page on the NotInList event. While exhaustive he did
>one thing in his code that highly irritates me. He used docmd.setwarnings which we
>should never, ever use.
I sent him an email on this topic.
David W. Fenton
news:8c5pf511kb8ojgjnl...@4ax.com:
> Also performance can be significantly different between the two
> methods. One posting stated currentdb.execute took two seconds
> while docmd.runsql took eight seconds. As always YMMV.
Anyone who is using DoCmd.RunSQL can easily replace it with my
SQLRun() function (posted after my sig). It was written for that
purpose, but was an adaptation of code I was already using so that I
didn't have to repeatedly write error handlers in every sub where I
used:
db.Execute strSQL, dbFailOnError
I moved the whole thing to a sub so that any errors would be
handled, and then when I kept seeing people using DoCmd.RunSQL, I
rewrote it and renamed it so that it could replace RunSQL. It also
does a lot more than DoCmd.RunSQL, since it can execute SQL on a
database other than the CurrentDB (without requiring complex IN ''
in the SQL's FROM clause), and it also returns the number of records
affected. It can be used as a sub or as a function.
(the code version below has the transactions commented out because
in the most recent app I've been using, it was causing problems)
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Public Function SQLRun(strSQL As String, Optional db As Database, _
Optional lngRecordsAffected As Long) As Long
On Error GoTo errHandler
If db Is Nothing Then Set db = CurrentDb
'DBEngine.Workspaces(0).BeginTrans
db.Execute strSQL, dbFailOnError
lngRecordsAffected = db.RecordsAffected
'DBEngine.Workspaces(0).CommitTrans
exitRoutine:
SQLRun = lngRecordsAffected
'Debug.Print strSQL
Exit Function
errHandler:
MsgBox "There was an error executing your SQL string: " _
& vbCrLf & vbCrLf & err.Number & ": " _
& err.Description, vbExclamation, "Error in SQLRun()"
Debug.Print "SQL Error: " & strSQL
'DBEngine.Workspaces(0).Rollback
Resume exitRoutine
End Function
Lars Brownies
In this respect, let's say I have a maketable query. With setwarnings set to
False I won't get the message that the table already exists, which in some
cases is what I want. In this case, when using 'Currentdb.Execute
strSQL,dbfailonerror', you do get that error. Do you suggest that I should
trap for that specific error and let Access ignore it?
I've noticed that in an UPDATE INTO query, with 'Currentdb.Execute
strSQL,dbfailonerror', you won't get the question if you want to proceed
with adding x number of records, like with setwarnings set to false. Those
messages do show up if you use docmd.runsql without the setwarnings. Am I
correct?
Last question. Let's say I have a long sub in which I use David function's
more than once. The function doesn't return that the query encountered
errors. If I want to stop the sub when the function encountered an error, I
will have to rewrite this function to be able to use it, right?
Thanks,
Lars
"Tony Toews [MVP]" <tto...@telusplanet.net> schreef in bericht
news:8c5pf511kb8ojgjnl...@4ax.com...
David W. Fenton
news:hh3da2$i0l$1...@textnews.wanadoo.nl:
> In this respect, let's say I have a maketable query. With
> setwarnings set to False I won't get the message that the table
> already exists, which in some cases is what I want. In this case,
> when using 'Currentdb.Execute strSQL,dbfailonerror', you do get
> that error. Do you suggest that I should trap for that specific
> error and let Access ignore it?
No. Handle the error, not ignore it. If you want to replace the
existing table, then delete it before you execute your SQL. In other
words, make sure the error conditions are taken care of before you
run your MakeTable.
At least, that's my philosophy, i.e., don't raise an error you can
anticipate.
> I've noticed that in an UPDATE INTO query, with 'Currentdb.Execute
> strSQL,dbfailonerror', you won't get the question if you want to
> proceed with adding x number of records, like with setwarnings set
> to false. Those messages do show up if you use docmd.runsql
> without the setwarnings. Am I correct?
Yes. If you want to prompt your user, you have to do it yourself
(though you won't know how many records were involved unless you
wrap it in a transaction and prompt before it's committed; that's
actually what is happening with DoCmd.RunSQL with SetWarnings ON).
> Last question. Let's say I have a long sub in which I use David
> function's more than once. The function doesn't return that the
> query encountered errors.
In its unaltered form, my SQLRun() function will handle the error,
telling the user what error occurred, and not completing the SQL.
You'd have to deal with each error with each call to SQLRun().
> If I want to stop the sub when the function encountered an error,
> I will have to rewrite this function to be able to use it, right?
My function will stop with any error at all and pop up a MsgBox
informing the user of the error number and description. It's right
there in the code. If that's not what you want, you'd have to
describe it.
Lars Brownies
> My function will stop with any error at all and pop up a MsgBox
> informing the user of the error number and description. It's right
> there in the code. If that's not what you want, you'd have to
> describe it.
What I meant was that in some occasions, if your function gives an error, I
want the calling sub to stop as well. I think the only way to do that, is to
give a False or True back to the calling sub.
Lars
"David W. Fenton" <XXXu...@dfenton.com.invalid> schreef in bericht
news:Xns9CED9D0E4C05Bf9...@74.209.136.82...
Tony Toews [MVP]
>In this respect, let's say I have a maketable query.
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
David W. Fenton
[quoting me:]
>> My function will stop with any error at all and pop up a MsgBox
>> informing the user of the error number and description. It's
>> right there in the code. If that's not what you want, you'd have
>> to describe it.
>
> What I meant was that in some occasions, if your function gives an
> error, I want the calling sub to stop as well. I think the only
> way to do that, is to give a False or True back to the calling
> sub.
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.
Banana
> 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.
>
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.
David W. Fenton
news:4B399873...@Republic.com:
> 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.
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.
Banana
> 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.
I suppose that if we were to replicate DoCmd.RunSQL exactly, then we
would throw the error back to the calling procedure.
> 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.
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.
> 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.
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.
> 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.
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).
David W. Fenton
news:4B3A89BB...@Republic.com:
> 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).
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.