Set Warnings false is misbehaving

[Aceware]

Access 2003 FE, SQL 2005 express BE

Generic code :

docmd.setwarnings false
docmd.openquery "sql pass thru query 1"
docmd.openquery "sql pass thru query 2"
etc
docmd.setwarnings true

Just started happening today (nothing has changed in my environment)
Intermittently will happily run the 1st pass thru query without comment but
next query will prompt:
"You are about to run a pass-through query that may modify data in your
table etc"

9 times out of ten code will run without any warning.

I have checked error log of SQL and cannot see anything significant.
I know I could turn off all action query warnings in options but usually I
do not need to.
I have tried importing all the database objects in to a completely new
virgin mdb but has not helped.

Any suggestions gratefully received.
Many TIA
Tony

 

[John W. Vinson]

Access 2003 FE, SQL 2005 express BE

Generic code :

docmd.setwarnings false
docmd.openquery "sql pass thru query 1"
docmd.openquery "sql pass thru query 2"
etc
docmd.setwarnings true

Just started happening today (nothing has changed in my environment)
Intermittently will happily run the 1st pass thru query without comment but
next query will prompt:
"You are about to run a pass-through query that may modify data in your
table etc"

9 times out of ten code will run without any warning.

I have checked error log of SQL and cannot see anything significant.
I know I could turn off all action query warnings in options but usually I
do not need to.
I have tried importing all the database objects in to a completely new
virgin mdb but has not helped.

Any suggestions gratefully received.
Many TIA
Tony

I'd suggest not using the openquery method at all; instead use the Querydef
Execute method:

On Error GoTo Proc_Error
Application.Execute "sql pass thru query 1", dbFailOnError
<etc>
Proc_Exit:
Exit Sub
Proc_Error:
<display and deal with error conditions>
Resume Proc_Exit
End Sub

 

[David W. Fenton]

Don't you mean (Access 2003):

CurrentDb.QueryDefs("sql pass thru query 1").Execute
dbFailOnError

Er, why not use CurrentDB.Execute directly and pass it the query
name instead of a SQL string? The only reason I can think of to use
QueryDefs.Execute is if you want to execute a parameter query, where
you'd initialize a QueryDef variable, set the parameters and then
execute it with the parameter values assigned.

A near drop-in replacement for DoCmd.RunSQL and calling
CurrentDB.Execute "[SQL string]", dbFailOnError that saves you
writing an error handler every time you use it follows my .sig.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Public Function SQLRun(strSQL As String) As Long
On Error GoTo errHandler

CurrentDB.Execute strSQL, dbFailOnError
SQLRun = CurrentDB.RecordsAffected

exitRoutine:
Exit Function

errHandler:
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in SQLRun()"
Resume exitRoutine
End Function