Warn me Not

[Guest]

First, I would like to say I have the setwarnings to false on the line before
the code and I am still getting a warning. It is kind of an unusual
situation. I am taking a table in one database and, using a make table query,
putting a copy, with a new name into the backup database. The SQL to do this
is working and is not in question. However, if there is a file with the same
name, (tbl August 2007) already in the backup db, I get a run time error
3010, table 'tbl August 2007' already exists. Since the error message is
generated by the other database I went in there and unchecked the Action
Queries, but I discovered that this change works for all of Access, not just
the one database so I had to turn it back on. And Action Queries and Document
Deletions did not seem to eliminate the warning. Do you have to restart
Access when these options are changed? I did not.

I have learned with Access that if I run into a wall, the wall is there for
a good reason and maybe I have to find another way to accomplish my task. So
if you have a way to turn this warning off great, however, I will gladly
accept another way to accomplish my task. I can make the table with the new
name in the current database, how then can I move or (copy/delete) it to
another database? As always, any help is greatly appreciated.

 

[Allen Browne]

Use Dir() to test if the target file exists.

If so, Kill it before executing your query.

 

['69 Camaro]

Hi, Michael.

First, I would like to say I have the setwarnings to false on the line
before
the code and I am still getting a warning.

You should never need to use SetWarnings in your code. As a matter of fact,
it's a practice that introduces problems when SetWarnings doesn't get turned
back on when it's supposed to, such as not being prompted to save a form
after changes are made, thereby saving unwanted changes.

However, if there is a file with the same
name, (tbl August 2007) already in the backup db, I get a run time error
3010, table 'tbl August 2007' already exists.

Perhaps you mean "if there is a table with the same name."

Do you have to restart
Access when these options are changed?

Not for that option.

I have learned with Access that if I run into a wall, the wall is there
for
a good reason

Wait until you get more experienced with Access. You'll change your mind
about that. ;-)

maybe I have to find another way to accomplish my task.

Use the Execute method with the dbFailOnError parameter, not RunSQL, to
avoid the messages unless something fails -- which you want to know about.
Try the following example:


Private Sub ExecQryBtn_Click()

On Error GoTo ErrHandler

Dim qry As QueryDef

Set qry = CurrentDb().QueryDefs("qryMakeTblNew")
CurrentDb().Execute qry.SQL, dbFailOnError

CleanUp:

Set qry = Nothing

Exit Sub

ErrHandler:

'------------------------------------------------------------------
' Drop the table before executing the make table query.
'------------------------------------------------------------------

If (Err.Number = 3010) Then
Err.Clear
CurrentDb().Execute "DROP TABLE tblNew;", dbFailOnError
Resume
Else
MsgBox "Error in ExecQryBtn_Click( ) in" & vbCrLf & _
Me.name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
End If

Err.Clear
GoTo CleanUp

End Sub ' ExecQryBtn_Click( )

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.