Now this isn't as simple as DoCmd.RunSql. My original suggestion was
for simplicity sake.

My thing is to go the simplest route. If OOP were simpler to use for a
given operation than having to write several lines of VBA code (by
several I mean thousands of VBA lines to a few hundred OOP lines to
achieve the same thing) then I would suggest that.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Ticks for 100 iterations

Northwind 2007.accdb

UPDATE Employees SET [Last Name] = StrConv([Last Name], 1)

DoCmd.RunSQL: 437
CurrentDb.Execute: 93
DBEngine(0)(0).Execute: 93
CurrentProject.Connection.Execute: 202
QueryDef.Execute: 62
The last depends on the existence of a saved query of the sql.

Of course, once we leave JET/ACE we get into a-synchronicity and time
may or may not be of any interest to us.
I think I have not used DoCmd.RunSQL or CurrentDb.Execute.

Querydef.Execute seems to be a powerful but largely ignored call. I am
experimenting with it now as a dynamic ODBC cludge, that is an ODBC
call without any persistent connection.

On Oct 6, 10:17*pm, "Tony Toews [MVP]" <tto... (AT) telusplanet (DOT) net> wrote:

lyle fairfield <lyle.fairfield (AT) gmail (DOT) com> wrote:

Interesting. Thanks.

I didn't like using it because it's a PITA if you have to debug a query later with
parameters. The folling is from
One method of using SQL queries in code as parameter queries can be difficult to work
with in code
http://www.granite.ab.ca/access/sqlqueries.htm

The problem is that you need to use a query in code such as looping through a
recordset or running an action query of some sort but you want to limit the number of
records via some criteria.

However when you run the query in the query window you have to enter appropriate
parameter values. Locating an appropriate value can be a pain in the ...

Actually what I always do when action or recordset queries get the slightest bit ugly
I make a SELECT query with the required fields. By ugly I mean one join to another
table, a lot of fields or whatever. I get this query working nicely showing all the
records I want. But with no selection criteria. Now I can quickly see if the query
looks proper now or six months from now when I forget what appropriate values were
for the criteria. I save that query.

I then create the action or SELECT query in the QBE grid with dummy selection
criteria based on that query I just created. I do NOT save it. Instead I click to the
SQL code view and copy the SQL code to the clipboard. Now I go into my VBA module
and paste the SQL code into the VBA code.. I then setup the strings with the double
quotes and line continuation for readability. I also setup the proper WHERE clause
criteria from my calling form or my code.

' decrement the QOH MatItemSizeQty record, ie change 5 to 3
strSQL = "UPDATE [Mat Rcvg - Update MRR - Update Qtys] " & _
"SET miqQtyOnHand = [miqQtyOnHand]-" & sngInventoryQty & " " & _
"WHERE itdID=" & me!InvTransDetailID & ";"
CurrentDb.Execute strSQL, dbFailOnError

where [Mat Rcvg - Update MRR - Update Qtys] is the ugly basic query.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Rich P <rpng123 (AT) aol (DOT) com> wrote:

Hmn, the above certainly hasn't been my experience. That said it's been quite a
number of years since I used docmd.runsql due to the setwanrings issues.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/