Without confirming msgbox

Topic

New Reply

Using Access 2000, is it possible to bypass the “You are about to delete records, etc” messages when running action queries by the VBA docmd.openquery or when using the docmd.runSQL method? I have used Sendkeys “Y”, but is there a better way?

Reply | Quote

Replies

Use Setwarnings:

DoCmd.SetWarnings False
DoCmd.RunSQL
DoCmd.SetWarnings True

Note: YOU MUST turn the warnings back on, or a lot of problems may result. I strongly recommend turning them off perform the action, then turn them on. Also, I would not group actions in between, perform one action per off / on. ( A bit more overhead, but less chance of missing important errors)

Reply | Quote

The other way, which is faster but sometimes chokes on a single action query for no apparent reason, is to use the Execute method on the database or querydef object. Here’s an example:

 Dim dbs as DAO.Database
  Set dbs = CurrentDb
  dbs.Execute strSQL  
  ...
  Set dbs = nothing

The Execute method doesn’t pop up the confirmation message at all, so you don’t need to set warnings off and on.

Reply | Quote

I agree with you Charlotte, but the execute method will not give you any message at all! Normally you still want to raise an error if th SQL is not correct. You have to add a switch to be sure an error is raised:

Dim dbs as DAO.Database
Set dbs = CurrentDb
dbs.Execute strSQL, dbFailOnError
…
Set dbs = nothing

Reply | Quote

You’re absolutely right, and I intended to mention error handling in my post, but I was doing several other things at the same time and obviously slipped up on that one. Thanks for pointing it out!

Reply | Quote