On Wed, 4 Feb 2004 14:30:44 +1000, "-Michelle-" <mi********@yah oo.com> wrote:
Hi
Using A2003 on XP
I am wondering from the MVP's and others, what is the most efficient way (in
terms of time to process) of updating data in a table, using the
docmd.RunSQL or Recordset 'Edit' and 'Update'?
eg: (if you need it)
1.--------------------------
mysql = "Update [mytable] SET [SomeField] = [somevalue];"
docmd.RunSQL mysql
2.-----------------------
set myrecordset = mydb.OpenRecord set([tablename / sql])
myrecordset.ed it
myrecodset.fie lds("myfield") = [somevalue]
myrecordset.up date
TIA
Michelle
Well, whichever way is faster, SQL Update vs Recordset, DoCnd,RunSQL is a very
inefficient way to execute a SQL command, and there are other good reasons not
to use it from code. It's better to use the .Execute method of a DAO Detabase
or QueryDef object, or of an ADO Connection or Command object.
With regard to whether the SQL update or the recordset is more efficent, it
depends an awful lot on context, but the SQL is generally better. For one
thing, before you can update a record using a recordset, you must find it. If
this is done with a Where clause in a SELECT statement, you are no making
several calls through the database layer instead of just 1. If you do it with
FindFirst, you are searching through the rows in a recordset with no
optimization at all.
Another factor is that any query, whether it is a SELECT or an UPDATE must
first be compiled before it is run. If you execute a SQL statement
repeatedly, compiling it each time, this will be much slower than if you
compile it once, then execute it multiple times. If you do this with a saved
query that takes parameters, the query will be compiled the first time you run
it, and the compiled state will be saved with the query and reused next time.
If you use a temporary querydef in code and reuse the same querydef multiple
times, it will be compiled the first time you execute it, and the compiled
state will be preserved until the querydef variable is released.
Here's an example using a DAO temporary querydef from code.
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prmFooID As DAO.Parameter
Dim prmFooName As DAO.Parameter
Set dbs = CurrentDB()
Set qdf = dbs.CreateQuery def("")
qdf.SQL = "UPDATE tblFoo SET tblFoo.FooName= prmFooName " & _
"WHERE tblFoo.FooID=pr mFooID"
Set prmFooID = qdf!prmFooID
Set prmFooName = qdf!prmFooName
prmFooID.Value= 1: prmFooName.Valu e="ABC"
qdf.Execute dbFailOnError ' Takes a brief time to compile before running.
prmFooID.Value= 2: prmFooName.Valu e="DEF"
qdf.Execute dbFailOnError ' Still comiled from previous Execute.
prmFooID.Value= 3: prmFooName.Valu e="GHI"
qdf.Execute dbFailOnError ' Still compiled.
' DAO can be unhappy if we don't clean up our objects in reverse order of
' dependency.
Set prmFooID = Nothing: Set prmFooName = Nothing
Set qdf=Nothing
Set dbs = Nothing
