Question about best practices...

  • Thread starter Thread starter Diane
  • Start date Start date
D

Diane

Hi All,

Generic question about performance and best practices:

I have code that in many places runs a lot of queries.
Is it better to run queries using the DoCmd function:

DoCmd.OpenQuery "qRunAQuery"

Or define the actual SQL statements...

' Define two SQL statements for action queries.
strSQLChange = "UPDATE Employees SET Country = " & _
"'United States' WHERE Country = 'USA'"
strSQLRestore = "UPDATE Employees SET Country = " & _
"'USA' WHERE Country = 'United States'"

Thanks,
Diane
 
Hi All,

Generic question about performance and best practices:

I have code that in many places runs a lot of queries.
Is it better to run queries using the DoCmd function:

DoCmd.OpenQuery "qRunAQuery"

Or define the actual SQL statements...

' Define two SQL statements for action queries.
strSQLChange = "UPDATE Employees SET Country = " & _
"'United States' WHERE Country = 'USA'"
strSQLRestore = "UPDATE Employees SET Country = " & _
"'USA' WHERE Country = 'United States'"

Thanks,
Diane
Click to expand...

How about... none of the above? <g>

As a "best practice" you should a) use a stored query, which will have
been optimized by the query analyzer; this can make for substantially
(or trivially, or none at all) better performance; and b) use the
Querydef's Execute method.

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim prm As Parameter
On Error GoTo Proc_Error
Set db = CurrentDb
Set qd = db.Querydefs("queryname")
For Each prm In qd.Parameters
prm.Value = Eval(prm.Name)
Next prm
qd.Execute dbFailOnError
<you can look at the qd.RecordsAffected property to see how many
records were actually updated>
....
Proc_Exit: Exit Sub
Proc_Error:
<handle any errors incl. query errors>
Resume Proc_Exit
 
Back
Top