DoCmd.RunSQL Warning... Can I turn it off? (1 Viewer)

jlathem

Registered User.
Local time
Yesterday, 19:07
Joined
Jul 25, 2010
Messages
201
Hey Guys,

Is there any way that I can turn off the record update notification that pops up when I run “DoCmd.RunSQL”? There will be just too many of them once the users start updating files all day.

James
 

JANR

Registered User.
Local time
Today, 01:07
Joined
Jan 21, 2009
Messages
1,623
If you run these SQL's from VBA you can use Execute metode instead.

CurrentDb.Execute "Insert Into....", dbFailOnError

Or turn the warnings off before you run your SQL

DoCmd.SetWarnings False
DoCmd.RunSQL "......"
DoCmd.SetWarnings True

If you use the latter make sure that warnings is set back to True after the SQL run or important access messages will be supressed.

The execute metode dosen't require you to supress warnings.

JR
 

jlathem

Registered User.
Local time
Yesterday, 19:07
Joined
Jul 25, 2010
Messages
201
If you run these SQL's from VBA you can use Execute metode instead.

CurrentDb.Execute "Insert Into....", dbFailOnError

Or turn the warnings off before you run your SQL

DoCmd.SetWarnings False
DoCmd.RunSQL "......"
DoCmd.SetWarnings True

If you use the latter make sure that warnings is set back to True after the SQL run or important access messages will be supressed.

The execute metode dosen't require you to supress warnings.

JR


Hi JR,

Thanks for your reply and help.

I am very new to VBA and have never seen the Execute method of running an Insert. Are there any pitfalls in using it over the DoCmd method?

Jms
 

JANR

Registered User.
Local time
Today, 01:07
Joined
Jan 21, 2009
Messages
1,623
No pitfalls since it dosen't tamper with warnings.

You can use it to execute sqlstring you construct or execute stored queries:

CurrentDb.Execute ("NameOfQuery"), dbFailOnError

Always add the parameter "dbFailOnError", so if the query fails the execute command terminates.

JR
 

jlathem

Registered User.
Local time
Yesterday, 19:07
Joined
Jul 25, 2010
Messages
201
No pitfalls since it dosen't tamper with warnings.

You can use it to execute sqlstring you construct or execute stored queries:

CurrentDb.Execute ("NameOfQuery"), dbFailOnError

Always add the parameter "dbFailOnError", so if the query fails the execute command terminates.

JR


Thank you JR.

That will come in handy in my next piece of code I write later today.

Have a GREAT day, or night where ever you may be!

Jms
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 00:07
Joined
Sep 12, 2006
Messages
14,823
jlathem

the choice of docmd.openquery compared with currentdb.execute depends on your needs to some extent. they are slightly different

if you suppress warnings the docmd.openquery can (may) partially or even completely fail without notfying you at all. in some cases the warings/confirmations may actually be desirable

wheres with currentdb.execute, you never get any warnings as such, at all - although you can suppress the whole query execution in the event of any failures.

it really depends whether you expect any failures, and ho you want to react if there are any.
 

jlathem

Registered User.
Local time
Yesterday, 19:07
Joined
Jul 25, 2010
Messages
201
jlathem

the choice of docmd.openquery compared with currentdb.execute depends on your needs to some extent. they are slightly different

if you suppress warnings the docmd.openquery can (may) partially or even completely fail without notfying you at all. in some cases the warings/confirmations may actually be desirable

wheres with currentdb.execute, you never get any warnings as such, at all - although you can suppress the whole query execution in the event of any failures.

it really depends whether you expect any failures, and ho you want to react if there are any.


Thanks Dave, I’ll have to give that a little thought.

I appreciate the information and as always thank you or all your help.

James
 

boblarson

Smeghead
Local time
Yesterday, 16:07
Joined
Jan 12, 2001
Messages
32,067
Also, one more piece of information.

If you use

SetWarnings False

in any procedure, be sure to add

SetWarnings True

in the exit point for your procedure (which means your error handler should exit through that same exit point) so that you don't wind up with warnings turned off if something errors out.
 

jlathem

Registered User.
Local time
Yesterday, 19:07
Joined
Jul 25, 2010
Messages
201
Also, one more piece of information.

If you use

SetWarnings False

in any procedure, be sure to add

SetWarnings True

in the exit point for your procedure (which means your error handler should exit through that same exit point) so that you don't wind up with warnings turned off if something errors out.


Ah, great thing to remember!

Thanks for the info.

James
 

Users who are viewing this thread

Top Bottom