UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How Can I Suppress This SQL Message?, Any Versions    
 
   
RiverKing
post Dec 13 2017, 05:56 PM
Post#1



Posts: 134
Joined: 5-August 13
From: North Texas (DFW)


My VBA code confirms the user's desire to delete selected SQL records before issuing the command:

CODE
DoCmd.RunSQL "DELETE * FROM " & "MessageLog" & " WHERE (Ndx <= " & Str(Ndx1) & ");"


but SQL insists on generating the message, "You are about to delete n row(s) from the specified table. ..." with Show Help, Yes, and No buttons. How can I suppress this message from SQL?






--------------------
Chuck
Go to the top of the page
 
GroverParkGeorge
post Dec 13 2017, 06:49 PM
Post#2


UA Admin
Posts: 31,234
Joined: 20-June 02
From: Newcastle, WA


Most experienced developers prefer to use:

CurrentDB.Execute "DELETE * FROM " & "MessageLog" & " WHERE (Ndx <= " & Str(Ndx1) & ");", dbFailOnError

rather than the DoCmd.RunSQL option.

It executes without the warning message. The dbFailOnError argument, on the other had, does raise a trappable error if something goes wrong.

--------------------
Go to the top of the page
 
datAdrenaline
post Yesterday, 12:54 PM
Post#3


UtterAccess Editor
Posts: 17,941
Joined: 4-December 03
From: Northern Virginia, USA


I agree with George ...

However ... if you insist on using .RunSQL, you can use DoCmd.SetWarnings False to turn OFF warnings. This is a STATIC setting by the way ... you set it, it sticks for the duration, and it affects the UI experience as well. So when using .RunSQL and you suppress the warnings, you are better off to turn the warnings back on.

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


... But ... if .RunSQL fails, and crashes your code ... your warnings are off -- unless of course you add more code to trap errors and then turn them back on ...

---

Also, something to keep in mind --- DoCmd.RunSQL is generally slower than CurrentDb.Execute. The reason is that DoCmd.RunSQL is a User Interface command. In other words it essentially emulates you dbl-clicking on a Query object with a SQL View that looks like the SQL you pass to .RunSQL, sometimes there is a perceived advantage to that, but I digress ... So the path of execution is UI -> a command interpreter -> DAO -> the database engine performs the query -> then the return path to the UI. Where as with CurrentDb.Execute ... its DAO -> the database engine performs the query -> then the return path to the caller. Note that those paths may not be perfectly precise, but they are pretty close to illustrate the concept.

---

The long and short of it ... when in VBA, use the .Execute method of a DAO database object (or the .Execute method of an ADODB Connection object). thumbup.gif

--------------------
Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 
GroverParkGeorge
post Yesterday, 01:32 PM
Post#4


UA Admin
Posts: 31,234
Joined: 20-June 02
From: Newcastle, WA


And now you know the rest of the story.... wink.gif

--------------------
Go to the top of the page
 
RiverKing
post Yesterday, 11:02 PM
Post#5



Posts: 134
Joined: 5-August 13
From: North Texas (DFW)


This is the first time I've had a chance to try your recommended CurrentDB.Execute and, of course, it worked perfectly. I thank you very much.

I only tried this on one DELETE command but intend to change all DELETE commands in this app. Would you recommend that I also change other DoCmd.RunSQL commands to CurrentDB.Execute? The shorter instruction path appeals to my sense of saving time where I can so I can use more time when I must (if that makes any sense). I'm very old school to the point that I can't break my 30-year old habit of worrying about how much storage I'm using (my first programs (in COBOL) were limited to 64K).



--------------------
Chuck
Go to the top of the page
 
GroverParkGeorge
post Today, 07:10 AM
Post#6


UA Admin
Posts: 31,234
Joined: 20-June 02
From: Newcastle, WA


I believe you will probably benefit from judicious conversion to the DAO approach.

I don't know that the primary advantage is in writing the code, though. It's primarily, IMO, in the greater efficiency in executing it, plus it's less prone to the kind of hidden problem Brent laid out.

--------------------
Go to the top of the page
 
RiverKing
post Today, 11:39 AM
Post#7



Posts: 134
Joined: 5-August 13
From: North Texas (DFW)


In total agreement with your idea of greater execution efficiency, I'll be changing all of the DoCmd.RunSQL commands. Thank goodness for Find, Copy, and Paste.

"judicious" conversion? What is this? I'm sure many people who know me would tell you that I don't know the meaning of the word judicious. "Audace! Audace!" wink.gif

Thanks again.



--------------------
Chuck
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    15th December 2017 - 10:21 PM