Docmd directly run an existing Update Query

[Rx_]

Trying to automate something that runs manually.

To meet a deadline, trying to avoid pasting in the large SQL code for an update query.

The steps are to:
1 delete the data in three temp tables.
2 reset the autocounter code back to 1 in two of the tables
3 (my quesiton)
I have three update queries to re-populate the three tables above

What is the best code to call an existing query (no parameters to pass)?

Docmd.runsql "name of query"
expects to "Select, Delete..."

 

[vbaInet]

Maybe you want the QueryDefs collection.

 

[Rx_]

CurrentDb.Execute "My_Update_Query_Name"
This seems to work.
Does anyone have comments on pro's or cons of using this?
A couple of internet post claim the Execute is fast.
Also, it does not seem to have those pesky Messages to hide.

DoCmd.RunSQL ("Delete * From 08_Team_Report_table")
' ChangeSeed - function that re-sets autocounter to 1, increment by 1
Call ChangeSeed ("08_Team_Report_table", "CounterField", 1, 1)
' the temp table is all clear and ready for an append query to repopulate
CurrentDb.Execute ""08_Team_Report_Append_Query"

 

[boblarson]

Just use CurrentDb.Execute instead of RunSQL.

[B]CurrentDb.Execute "Delete * From 08_Team_Report_table", dbFailOnError[/B]
' ChangeSeed - function that re-sets autocounter to 1, increment by 1
Call ChangeSeed ("08_Team_Report_table", "CounterField", 1, 1)
' the temp table is all clear and ready for an append query to repopulate
CurrentDb.Execute "08_Team_Report_Append_Query", dbFailOnError

 

[vbaInet]

I don't know about them being faster than the Docmd method (once you've turned off the warnings).

One really good advantage is rolling back if it fails. It's one of the options of the Execute method. And of course those messages being supressed.

The only thing is that they would ONLY run action queries.

 

[boblarson]

The only thing is that they would ONLY run action queries.

Well, so does RunSQL. It only runs Action Queries as well.

 

[vbaInet]

Lol. I don't think it (i.e. db.Execute) does Data-Def queries though.

 

[Rx_]

Thanks for the feed back!
The code I posted is running great for hours now. I am off to other fun stuff.
It is great to get confirmation that it is a good method.

 

[vbaInet]

Have fun doing other fun stuff

 

[gemma-the-husky]

io thought

docmd.openquery "qryname" or
currentdb.execute "qryname"

I was taught a long while ago on here, that the latter is better, as you can test for success/failure. With the former you can turn off messages, but then you dont get advised if the query fails

The other thing is resetting the autonumber.

It begs the question "why?"

 

[vbaInet]

io thought

docmd.openquery "qryname" or
currentdb.execute "qryname"

I was taught a long while ago on here, that the latter is better, as you can test for success/failure. With the former you can turn off messages, but then you dont get advised if the query fails

That's correct Dave All mentioned in post #5