RunSQL vs. OpenQuery

[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.

 

[Douglas J. Steele]

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

 

[Guest]

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!)






.