ae***********@nospam.comcast.net (Colleyville Alan) wrote in
<IOHOb.84405$nt4.128688@attbi_s51>:
I am now using the Execute command to run SQL for action queries.
When I had them as saved queries, I would use DoCmd.SetWarnings
False to allow the queries to overwrite existing tables. when I
use this code:
CurrentDb().Execute strMySql', dbFailOnError
I still get warnings that the table already exists.
Is there another command that I am supposed to use?
You need to handle the errors that can occur if there is an error.
Another way to fix this is to remove dbFailOnError, but that means
that you never know for certain if the SQL you wanted executed
really succeeded or not. For instance, if some of the records were
not updated, you'll never know.
If your SQL is a MakeTable, then you need to delete the table
before running your SQL. However, then you need to be sure the
table exists before you try to delete it. A discussion of various
ways of coding a TableExists() function and issues of error
handling and the values of True/False are discussed in this thread:
http://groups.google.com/groups?selm...%40news.tpi.pl
I recommend the implementation in this Microsoft Knowlege Base
article:
http://support.microsoft.com/default...b;en-us;210598
It works for all Access objects and works on the error handler
principle. Actually, I've implemented it differently than that
article does it -- my code is at the end of my post, after my
signature.
A non-error-based approach (which might be faster) is to use SQL to
look up the table name in MSysObjects, a Jet system table. Since
it's SQL, it's probably very fast, though perhaps the time it takes
to open the recordset might offset the time it takes to raise an
error in the function below.
Basically, though, I'd strongly recommend *against* the use of On
Error Resume Next in any context, as I once had an application
where I used it and it didn't go out of scope properly, resulting
in lost errors. As I said in the Google thread cited above, I never
use i except one line at a time, like this:
On Error Resume Next
[whatever you're doing that might raise an error]
On Error Resume 0
I'm also philosophically opposed to using it, in general, because
it assumes that you know exacly which errors your line of code can
raise and you want to ignore absolutely all such errors. I'm not
that omniscient so I prefer to write code that avoids raising any
foreseen errors and explicitly handles any unforeseen ones.
--
David W. Fenton
http://www.bway.net/~dfenton
dfenton at bway dot net
http://www.bway.net/~dfassoc
Function DoesObjectExist(strObjectType As String, _
strObjectName As String, Optional db As Database) As Boolean
' Microsoft
' based on Microsoft KB article 210598
On Error GoTo errHandler
Dim strFindObject As String
Dim cnt As Container
Dim strMsg As String
If db Is Nothing Then Set db = CurrentDb()
If strObjectType = "Macros" Then strObjectType = "Scripts"
Select Case strObjectType
Case "Tables"
strFindObject = db.TableDefs(strObjectName).Name
Case "Queries"
strFindObject = db.QueryDefs(strObjectName).Name
Case "Forms", "Modules", "Reports", "Scripts"
Set cnt = db.Containers(strObjectType)
strFindObject = cnt.Documents(strObjectName).Name
Set cnt = Nothing
Case Else
strMsg = "Object Name """ & strObjectType & _
""" is an invalid "
strMsg = strMsg & " argument to function DoesObjectExist!"
MsgBox strMsg, vbCritical, "DoesObjectExist"
End Select
DoesObjectExist = Len(strFindObject) > 0
exitRoutine:
Exit Function
errHandler:
Select Case Err.Number
Case 3265
' object does not exist
Case Else
MsgBox Err.Number & ": " & Err.Description, vbCritical, _
"Error in function DoesObjectExist()"
End Select
Resume exitRoutine
End Function
