RunSQL vs. OpenQuery

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

Is there a performance advantage to running large update
and append queries (approx 150,000 records) via the
docmd.openquery method instead of using docmd.RunSQL? I'm
thinking a saved query will have the advantage by already
parsing the SQL (like a stored procedure in SQL Server).
The application is using Access 97.
 
While a stored query might be marginally faster, I'm not sure you'd notice
the difference.

On the other hand, I wouldn't use either of the approaches you mention. Use
the Execute method instead, either on the opened QueryDef object, or on the
Database object:

qdfCurr.Execute dbFailOnError
CurrentDb.Execute strSQL, dbFailOnError

That way, you can intercept any errors that may occur, and you won't get the
annoying "You're about to..." popups
 
Thanks Doug, I will try the execute method.
-----Original Message-----
While a stored query might be marginally faster, I'm not sure you'd notice
the difference.

On the other hand, I wouldn't use either of the approaches you mention. Use
the Execute method instead, either on the opened QueryDef object, or on the
Database object:

qdfCurr.Execute dbFailOnError
CurrentDb.Execute strSQL, dbFailOnError

That way, you can intercept any errors that may occur, and you won't get the
annoying "You're about to..." popups

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)






.
Click to expand...
 
Back
Top