Any way to turn off warnings when running MakeTable query?

[lj]

I have two reports that depend on MakeTable queries. I would rather my users
not have to respond to the warning that 'tbl_SoAndSo will have to be
deleted' and 'You are about to paste XX new rows into table'.... Is there
any way that I can intercept those warnings and give them the 'yes, go ahead
and do that' without the user ever seeing them?

Help and/or sample code will be greatly appreciated.

lj

 

[Dirk Goldgar]

I have two reports that depend on MakeTable queries. I would rather
my users not have to respond to the warning that 'tbl_SoAndSo will
have to be deleted' and 'You are about to paste XX new rows into
table'.... Is there any way that I can intercept those warnings and
give them the 'yes, go ahead and do that' without the user ever
seeing them?

Help and/or sample code will be greatly appreciated.

lj

There are two ways to suppress those warning when running action
queries. One is to use DoCmd.SetWarnings to turn the warnings off
before executing the queries, and then to turn them on again afterward.
For example:

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM SomeTable"
DoCmd.OpenQuery "MyMakeTableQuery"
DoCmd.SetWarnings True

If you use this method, you should set up error-handling to ensure
there's no way an error could cause your function to terminate without
setting the warnings back on again.

Better, to my mind, is to use the Execute method of the DAO database
object to execute the query. Then the warnings won't be generated in
the first place. Here's an example:

With CurrentDb
.Execute "DELETE * FROM SomeTable", dbFailOnError
.Execute "MyMakeTableQuery", dbFailOnError
End With

The dbFailOnError argument is necessary if you want to be informed of
any error that may occur in executing the query -- and you almost
certainly should. But that's a different thing from the kind of
warnings you're talking about -- those aren't generated at all.

 

[lj]

Thanks for such a prompt reply! One other question: in using the Execute
method, what would be the syntax for spelling out the SQL query? It is not a
named query, as it is part of an option selection and the Where clause has
to be changed, so I have it coded

DoCmd.RunSQL (SELECT.......)

I can see where I would put in the DoCmd actions you suggested, but I do
like the idea of the Execute trapping the other possible errors...

Thanks again, lj

 

[Douglas J. Steele]

The first parameter of the Execute method can be either the name of a query
(as in Dirk's example), or it can be an SQL statement.

 

[Dirk Goldgar]

Thanks for such a prompt reply! One other question: in using the
Execute method, what would be the syntax for spelling out the SQL
query? It is not a named query, as it is part of an option selection
and the Where clause has to be changed, so I have it coded

DoCmd.RunSQL (SELECT.......)

I can see where I would put in the DoCmd actions you suggested, but I
do like the idea of the Execute trapping the other possible errors...

You simply specify the SQL statement as a string literal, variable, or
expression. For example,

Dim strSQL As String
Dim strCriteria As String

strSQL = "SELECT Field1, Field2 INTO MyNewTable " & _
"FROM SomeOtherTable"

Select Case Me!optMyOption
Case 1
" WHERE CustID = " & Me!txtCriterion
Case 2
" WHERE CustName = " & Chr(34) & Me!txtCriterion & Chr(34)
End Select

CurrentDb.Execute StrSQL & StrCriterion, dbFailOnError

 

[lj]

Thank you so much for your help. I will try this syntax. I did try using
..execute ("SELECT...INTO..") but got an error that the table already
existed. I want it to delete the old table and make it again with the new
criteria.

lj

 

[Dirk Goldgar]

Thank you so much for your help. I will try this syntax. I did try
using .execute ("SELECT...INTO..") but got an error that the table
already existed. I want it to delete the old table and make it again
with the new criteria.

Then you need to delete the table first. Unfortunately, that *will*
raise an error if the table doesn't exist, but you can trap and ignore
that error. Here's one simplistic approach that just ignores any error
on the DROP TABLE statement:

On Error GoTo Err_Handler ' establish normal error-handling

Dim db As DAO.Database

Set db = CurrentDb

On Error Resume Next ' ignore error on next statement
db.Execute "DROP TABLE MyOutputTable"
On Error GoTo Err_Handler ' restore normal error-handling

db.Execute _
"SELECT * INTO MyOutputTable FROM MyInputTable", _
dbFailOnError

Set db = Nothing

 

[lj]

Thank you for all of your suggestions. I will try this approach; if all else
fails, the DoCmd method does work...

lj