Run SQL statement without pop uo window

K

KK Chan

I need to use command button click to do two docmd.RunSQL to update data from
two different table. I don't want the pop up window telling user about
updating data come out. User need to click twice to confirm when trigger this
command button, it that any way to let the RunSQL to do its work without
having a notice window pop up? Thanks
 
O

Ofer

The other option is, if you don't want any message only in that specific
time, then you can use the setwarnings

Docmd.Setwarnings False
Docmd.RunSql ""
Docmd.SetWarnings True

' Don't forget to set it back to true
 
J

John Vinson

I need to use command button click to do two docmd.RunSQL to update data from
two different table. I don't want the pop up window telling user about
updating data come out. User need to click twice to confirm when trigger this
command button, it that any way to let the RunSQL to do its work without
having a notice window pop up? Thanks
Click to expand...

As yet another option, in addition to Chris' and Ofer's suggestions,
don't use RunSQL at all. Instead use the (somewhat more cumbersome)
querydef Execute method; it has the advantage that it traps query
errors:

Dim db As DAO.Database
Dim qd As DAO.Querydef
On Error GoTo Proc_Error
Set db = CurrentDb ' reference the database object
' create a nameless query using strSQL
Set qd = db.CreateQuerydef("", strSQL)
' execute it
qd.Execute dbFailOnError
.... <other code> ...

Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbCritical
<do other error handling as needed>
Resume Proc_Exit
End Sub


John W. Vinson[MVP]
 
D

Douglas J. Steele

Or, to offer a fourth alternative, use the Execute method of the Database
object!

On Error GoTo Proc_Error

' Define the SQL string in strSQL

CurrentDb.Execute strSQL, dbFailOnError

.... <other code> ...

Proc_Exit:
Exit Sub

Proc_Error:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbCritical
<do other error handling as needed>
Resume Proc_Exit
End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
K

KK Chan

Thank you guys

Douglas J. Steele said:
Or, to offer a fourth alternative, use the Execute method of the Database
object!

On Error GoTo Proc_Error

' Define the SQL string in strSQL

CurrentDb.Execute strSQL, dbFailOnError

.... <other code> ...

Proc_Exit:
Exit Sub

Proc_Error:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbCritical
<do other error handling as needed>
Resume Proc_Exit
End Sub
Click to expand...
 
T

Tony Toews

KK Chan said:
I need to use command button click to do two docmd.RunSQL to update data from
two different table. I don't want the pop up window telling user about
updating data come out. User need to click twice to confirm when trigger this
command button, it that any way to let the RunSQL to do its work without
having a notice window pop up? Thanks
Click to expand...

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Top