dbs.execute to run action queries (1 Viewer)

[Chrisopia]

I was searching to find a way to turn messages off when running a pre-saved query, and came across dbs.execute

Dim dbs As DAO.Database
Set dbs = CurrentDb
dbs.execute "SOME SQL STATEMENT", SOMEERRORFUNCTION

Not only does it turn off the messages, but also removes the need to Dim, and I might just be seeing things, but I think it's actually more efficient and quicker than both a presaved query and a DoCmd.RunSQL statement.

 

[GinaWhipp]

Well, with what you have above you can use...

Dim strSQL As String
Dim db As DAO.Database
 
strSQL = "Statement here..."
CurrentDb.Execute strSQL, dbFailOnError

..to silence the message or...

DoCmd.SetWarnings False 'Off
DoCmd.RunSQL etc...
DoCmd.SetWarnings True 'On

But you don't actually turn off the message withing the dbs.execute statement. It's the line afterwards. As for which one is faster, never timed it but I prefer the CurrentDb.Execute... less bloating or so it seems, as Action Queries do cause bloat.

 

[namliam]

currentdb.execute "SOME SQL STATEMENT", SOMEERRORFUNCTION

Well actually a presaved SQL is slightly better, since it no longer needs compiling.
You can however also do...
Currentdb.Execute "Presavedqueryname"
Offcourse you may for what ever reason not be able to have a presaved query or not want it for what ever reason.


RunSQL or Execute have no effect what so ever on bloating, bloating is the growing of your database due to the use of temp tables or the deleting/appending/staging records.
Which usually includes action queries yes, however using the RunSQL or Execute statements doesnt make any difference what so ever.

 

[MS$DesignersRCretins]

But you don't actually turn off the message withing the dbs.execute statement. It's the line afterwards.

Hello, I've been enjoying and benefiting from your posts. If you don't mind the intrusion, can you explain what you mean by "the line afterwards?" I think you mean the SetWarnings false; just checking. If so I'm getting from this that .execute doesn't inherently stop prompts; just the normal SetWarnings off does. (The first message had me believing that .execute turned off prompts/warnings) Sounds then like the difference between the two methods are, or rather nearly are, a matter of style, or only subtly different.

Is there an opinion on .execute vs. OpenQuery? I've always used OpenQuery, likely dating back to long ago when I was converting macros to VBA and I ended up with a bunch of them; and until just now, reading above, I didn't know that .execute worked on saved query names as well as SQL.

 

[MS$DesignersRCretins]

Just to share some of my reading, http://www.office-archive.com/16-ms-access/e39742bcf6536a86.htm was pretty interesting about .execute vs. openquery. Dev Ashish - wasn't sure (in 2001 anyway ). Another says, just don't use DoCmd! (For performance.) I gather that there are technical reasons too (e.g. ability to commit and rollback), but for those cases where either works, I wonder if I'm making the most efficient choice.

 

[GinaWhipp]

@MS$DesignersRCretins

Thanks nice of you to say!

Hmm, after re-reading I should have explained that a little better/clearer...

A.

CurrentDb.Execute strSQL, dbFailOnError

a. With this line the query will run with no dialogs so there is no need for SetWarnings. Though if there is an error it will rollback the changes and will give a trappable error. For a better explanation see...
http://allenbrowne.com/ser-60.html

B. IMHO, using (A) is less *bloating* then OpenQuery when performing Action Queries. Now, that said, this debate has been going on since Access and everyone will have their view. Might be fun to start a thread in Watercooler section and see where the scales tip!

 

[MS$DesignersRCretins]

Much clearer now!

I really like that link. Thanks for that too.

 

[GinaWhipp]

You're welcome... there's a lot of GREAT stuff at that site, my *go to* site!

 

[namliam]

B. IMHO, using (A) is less *bloating* then OpenQuery when performing Action Queries. Now, that said, this debate has been going on since Access and everyone will have their view. Might be fun to start a thread in Watercooler section and see where the scales tip!

I dont mind repeating myself, I am used to it....

RunSQL or Execute have no effect what so ever on bloating, bloating is the growing of your database due to the use of temp tables or the deleting/appending/staging records.
Which usually includes action queries yes, however using the RunSQL or Execute statements doesnt make any difference what so ever.

Though I didnt yet include OpenQuery... that includes OpenQuery.

OpenQuery IMNSHO is to open a query, i.e. in table view....
It will also RUN action queries, but action queries cant be "Opened" as such.

I never actually tried to Execute a select statement, might be intresting to try.
I use OpenQuery only to .... open a query...
Use execute to run action queries without confirmation messages.

 

[GinaWhipp]

@namliam

Keyword in my statement = IMHO

 

[namliam]

There is no IMHO about it, there is no difference using one or the other

 

[GinaWhipp]

@namliam

I have sent you a PM so as to not clog up the thread.

 

[MS$DesignersRCretins]

Here's a shell to test the methods if it encourages anyone to experiment. It times measuring tenths of a second.

Sub Timethis()
'time .Execute vs. openquery vs. runsql, and maybe observe bloat
'1. Create a Query1. Maybe start with a select query; maybe a make table query (to check bloating?) However .execute won't like doing a "make table" if the table already exists; you might want to destroy it.
'DoCmd.SetWarnings False - does not appear to work with .Execute - (right, Gina?)
'2. Set variable sSQL
'3. set the CONST and run.

Const NUM_LOOPS = 1
    
    Dim dStart As Double, i As Long, sSQL As String
    
    '**** SET sSQL HERE ****
    sSQL = "SELECT ... " _
               & " INTO NewTable " _
               & " FROM sometable;"
    
    DoCmd.SetWarnings False
    
    Debug.Print "start exec saved query:" & Now: dStart = Now
    For i = 1 To NUM_LOOPS
        CurrentDb.Execute "Query1"
    Next i
    Debug.Print "end exec saved query:" & Now, CInt((Now - dStart) * 60 * 60 * 240#)
    Stop 'optionally stop here to check the bloat, such as from a make table query
    
    Debug.Print "start exec SQL:" & Now: dStart = Now
    For i = 1 To NUM_LOOPS
        CurrentDb.Execute sSQL
    Next i
    Debug.Print "end exec SQL:" & Now, CInt((Now - dStart) * 60 * 60 * 240#)
    Stop 'optionally stop here to check the bloat, such as from a make table query
    
    Debug.Print "start openquery:" & Now: dStart = Now
    For i = 1 To NUM_LOOPS
        DoCmd.OpenQuery "Query1"
    Next i
    Debug.Print "end openquery:" & Now, CInt((Now - dStart) * 60 * 60 * 240#)
    Stop 'optionally stop here to check the bloat, such as from a make table query
    
    Debug.Print "start runsql:" & Now: dStart = Now
    For i = 1 To NUM_LOOPS
        DoCmd.RunSQL sSQL
    Next i
    Debug.Print "end runsql:" & Now, CInt((Now - dStart) * 60 * 60 * 240#)
    Stop 'optionally stop here to check the bloat, such as from a make table query
    
    DoCmd.SetWarnings True
End Sub

Small note, namliam, for some reason ".execute query1" worked one time, but then it gave error 3061 every time and query1 stopped working too. Long story short: just avoid using an "equal sign" (=) in field names. MS normally makes many redundant parentheses () in the query builder, but it may fatally omit necessary field brackets [] in some circumstances. (If anyone's interested, I can explain further in a different subject thread.)

 

[namliam]

MS$, eventhough it is allowed, you shouldnt really use special characters like =+-_)(*&^% etc in any field or table names even spaces can cause issues if you are not carefull.

It is good practice in any case to avoid any of the above.