Is it bad to turn off errors in leiu of if statements? (1 Viewer)

[April15Hater]

Sometimes 'tblPeoplesoft' will be missing, sometimes 'import' will be missing.

DoCmd.SetWarnings False
On Error Resume Next
CurrentDb.Execute "DROP TABLE tblPeoplesoft"
CurrentDb.Execute "DROP TABLE Import"
On Error GoTo 0
DoCmd.SetWarnings False

Either way, I just said screw the errors, just run it, if it's there it'll drop it. Something tells me this shortcut will bite me later (especially since my weak point is error handling). Any reason not to do this?

 

[vbaInet]

You should have proper error handling in place. On Error GoTo 0 is the same as not having any error handling in place.

Check if the tables exist in MSysObjects before trying to DROP it. But why dropping and creating tables anyway? It's less bloating leaving them in there than constantly creating and deleting.

 

[jdraw]

Here's a small function to see if a table exists

Public Function TableExists(db As DAO.Database, sTable As String) As Boolean

Dim tbl As DAO.TableDef
On Error GoTo TableExists_Error

TableExists = False

For Each tbl In db.TableDefs
If tbl.name = sTable Then
TableExists = True
Exit Function
End If
Next tbl

Set tbl = Nothing

On Error GoTo 0
Exit Function

TableExists_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure TableExists"
End Function

I'm sure there are others, but this one works for me.

As for error handling, as vbaInet says, you should use it. (it will reduce those future bites). My recommendation is to download the free MZTools utility. It has many features, one of which is to add Error handler code in a procedure at the click of a button.
see http://www.mztools.com/v3/download.aspx

Another recommendation is to use the dbFailOnError parameter of the db.execute

CurrentDb.Execute "DROP TABLE Import",dbfailOnError

 

[vbaInet]

I'm sure there are others, but this one works for me.

Re what I said about MSysObjects and since you're just looking for the existence of two tables:

If Nz(DCount("*", "MSysObjects", "[Type] = 1 AND [Name] = 'tblPeopleSoft'"),0) <> 0 Then
    Currentdb.Execute ...
End If

I'm sure there are others, but this one works for me.

Another recommendation is to use the dbFailOnError parameter of the db.execute

Good point from jdraw. With this you still need error handling because it will throw a DAO error.

 

[RainLover]

I would not recommend the use of the following.

DoCmd.SetWarnings False

 

[vbaInet]

Nothing wrong with turning it off as long as you don't do it often and remember to turn it back on even after your code errors. A lot of people forget to turn it back on in the Error Handler.

 

[RainLover]

There is a lot wrong.

With it Off, how do you know you had a problem.

I once did a search and replace to get rid of that line. It was a large Database. It took me three days to sort out the errors it caused.

 

[gemma-the-husky]

it depends what you want to do

take your example

docmd.setwarnings false
On Error Resume Next
CurrentDb.Execute "DROP TABLE tblPeoplesoft"
On Error Resume Next
CurrentDb.Execute "DROP TABLE Import"
docmd.setwarnings TRUE

in this instance because you are using currentdb.execute, the setwarnings just doesn't matter. however, at the end you should set them back to TRUE.

note that you need an on error resume next BEFORE each statement. If both tables are missing, the first statement will "use" your error handler, and then there is no error handler for the second statement.

Whether to set warnings to false or not just depends on what you want to do. Doing so will suppress the results of an action query, which may or may not be what you want to do.

[edited - consider for example on error resume next - this in itself is something to use with caution - in this instance, when trying to delete a table that may or not exist, it makes sense to use on error resume next - but often you just cannot ignore errors, and carry on regardless]

 

[April15Hater]

I should have mentioned this is a dummyproof thing. This is among the beginnings of a daily import and I want to make sure the old stuff is gone. What better way than to delete the table?

I guess I just don't want to make a big deal coding something that might not ever happen.

As for the error handling, it's one of those things that I really just need to bite the bullet and learn. Any suggestions on a starting point? I'll try that software when I get home, but the admins are keeping me from installing anything on my local machine at work.

I also meant to leave out the:

docmd.setwarnings false

Not needed with currentdb.execute.

Thanks for all the input!

 

[vbaInet]

There is a lot wrong.

With it Off, how do you know you had a problem.

I once did a search and replace to get rid of that line. It was a large Database. It took me three days to sort out the errors it caused.

I know it's not the best way of doing things but my point was it doesn't cause problems if handled properly, i.e. Turn Off just before an action and turn back On right after the action + turn on when an error occurs (like gemma-the-husky also mentioned). In the OP's case it wasn't necessary (which he later realised).

Yes, you won't be able to tell how many records were affected but that's the main downside. I personally don't turn it off nor do I use RunSQL

Here's a good link on this:

http://allenbrowne.com/ser-60.html

 

[RainLover]

This will not create a Warning/Error.

Private Sub cmdRun_Click()
On Error GoTo Err_cmdRun_Click
DoCmd.SetWarnings False
On Error Resume Next

CurrentDb.Execute "QryTest"
On Error Resume Next

CurrentDb.Execute "DROP TABLE Import"
DoCmd.SetWarnings True
Exit_cmdRun_Click:
Exit Sub
Err_cmdRun_Click:
MsgBox Err.Description
Resume Exit_cmdRun_Click

End Sub

This will create a Warning/Error

Private Sub cmdRunQueryOff_Click()
On Error GoTo Err_cmdRunQueryOff_Click
CurrentDb.Execute "QryTest"

CurrentDb.Execute "DROP TABLE Import"
Exit_cmdRunQueryOff_Click:
Exit Sub
Err_cmdRunQueryOff_Click:
MsgBox Err.Description
Resume Exit_cmdRunQueryOff_Click
End Sub

IMHO I need the error to let me know that there is a problem.

 

[Galaxiom]

With [warnings] Off, how do you know you had a problem.

I once did a search and replace to get rid of that line. It was a large Database. It took me three days to sort out the errors it caused.

Three days to the fix the problems that line caused or three days to impliment alternatives such as Execute to avoid warnings about affecting the data?

The change to Execute means having to concatenate values into SQL strings, delimit string values, format date values, handle Nulls, escape quotes and apsotrophies in values etc. Yes a substantial amount of extra work but not caused by setting warnings off.

Aside from these which are not caused by setting warnings off, what were the problems that line really caused per se?

I see nothing particularly wrong with turning off warnings so long as the implications are fully understood and they are turned back on promptly and reliably.

RunSQL and OpenQuery are far easier to deal with than Execute for new developers because they allow simple direct references to controls via the Forms Collection, entirely avoiding all the complexities mentioned above.

New developers already have enough to deal with and I would still recommend it for them.

 

[RainLover]

Not Three days to the fix the problems that line caused.

Rather 3 Days to fix the Queries so that they would run.

I don't see the sence for anyone to have a Query not return an error when there is in fact something wrong.

I completely fail to see why turning off warnings is a good thing. Especially for a new developer.

New developers already have enough to deal with and I would still recommend it for them.

A new developer will have even more to deal with when they think the query ran when in fact it caused an error which would normally be picked up if warnings are on.

Turning Warnings OFF is not the cause of any error. It merely hides the error.

 

[Galaxiom]

Like everything in Access, different techniques have their place but it is essential to understand the limitations.

Of course one can get a "duplicate index" error on an insert and we have to be aware of such issues. This situation is normally avoided by validations before the command is run.

However most of the errors during SQL commands are actually caused by not understanding the complexities of concatenating control values into an SQL command string and which "gotchas" needed to be avoided.

I would certainly not recommend using RunSQL on anything other than a literal command string that directly referenced controls as objects. I agree it would dangerous to attempt RunSQL in a production database on a complex concatenated command with warnings off. There are just too many things that can go wrong.

Moreover it would be pointless since the one advange of RunSQL is the ability to avoid concatenation.

I still believe that the ability to run a saved query (or its SQL) from code is a very useful technique that allows the less experienced user to make some headway without being driven to despair.

Of course one does not turn off warnings until the application has been tested.

 

[SteveH2508]

This will not create a Warning/Error.

Private Sub cmdRun_Click()
On Error GoTo Err_cmdRun_Click
DoCmd.SetWarnings False
On Error Resume Next

CurrentDb.Execute "QryTest"
On Error Resume Next

CurrentDb.Execute "DROP TABLE Import"
DoCmd.SetWarnings True
Exit_cmdRun_Click:
Exit Sub
Err_cmdRun_Click:
MsgBox Err.Description
Resume Exit_cmdRun_Click

End Sub

<snip>

Personally, I would put the DoCmd SetWarnings True after Exit_cmdRun_Click so that warnings are always turned back on, even if your error-handling block runs.

Just my two penn'orth.

 

[RainLover]

Personally I would not use Set Warnings at all.

You are correct that it is good practice to Turn Warnings on in the Exit Procedure. That is if you use Warnings, which I don't.

 

[spikepl]

Re #8

docmd.setwarnings false
On Error Resume Next
CurrentDb.Execute "DROP TABLE tblPeoplesoft"
On Error Resume Next
CurrentDb.Execute "DROP TABLE Import"
docmd.setwarnings TRUE
...

note that you need an on error resume next BEFORE each statement. If both tables are missing, the first statement will "use" your error handler, and then there is no error handler for the second statement.

I am curious as to the underlined part in red. The following code works just fine with just one Resume Next as I would expect it to.

Private Sub Command72_Click()
Dim a As Double
On Error Resume Next
a = 1 / 0
a = 1 / 0
End Sub

 

[vbaInet]

Personally, I would put the DoCmd SetWarnings True after Exit_cmdRun_Click so that warnings are always turned back on, even if your error-handling block runs.

Just my two penn'orth.

Yep, I even mentioned that when I initially said there was nothing wrong with turning off the Warnings (post #6) and kept saying it. I don't think RainLover noticed

 

[RainLover]

I did notice but do not feel that this is the real issue.

The issue is that Warnings Off hides errors which we should know about.

I don't think you noticed that in my posts.

 

[gemma-the-husky]

there are certainly circumstances in which errors can be ignored, and setwarnings false is useful.

say you have a csv datafile with 1000 items in, of which 700 have alreaady been transferred to a new table. if you append ALL the items, the append will fail for the 700 and succeed for the 300 new items.

so just run the append query, and ignore warnings, and you get what you want. yes, you could delete the pre-existing items, and make the process more complex, but you don't have to.

and in this case, you probably don't want currentdb.execute appendquery, becuase this WILL fail.


There are lots of features in access that we rarely use - eg mouse events, key press events - it doesn't mean they are worthless.