Question for Learning: Docmd.OpenQuery versus db.Execute
I've got something bugging me, so I thought I'd throw it to the Gurus for some thoughts/advice:
I recently have been trying to incorporating the DAO execute method. It seems that for action queries, the idea that "this option generates a run-time error and rolls back all successful changes if any of the records affected are locked and can't be updated or deleted" (from microsoft help) is appealing.
Is the execute method "better" than the openquery method? I sometimes get errors when attempting to run queries with the Execute Method, such as "Too Few Parameters -- Expecting 2" or "Overflow". When I change the line of code from db.execute back to docmd.openquery, the error goes away. I don't know enough about the execute method to know how to troubleshoot, so I abandon the idea of using it.
When is the best time to use either method? Is one method preferable over another?
Thanks for your insight.
;-)
I recently have been trying to incorporating the DAO execute method. It seems that for action queries, the idea that "this option generates a run-time error and rolls back all successful changes if any of the records affected are locked and can't be updated or deleted" (from microsoft help) is appealing.
Is the execute method "better" than the openquery method? I sometimes get errors when attempting to run queries with the Execute Method, such as "Too Few Parameters -- Expecting 2" or "Overflow". When I change the line of code from db.execute back to docmd.openquery, the error goes away. I don't know enough about the execute method to know how to troubleshoot, so I abandon the idea of using it.
When is the best time to use either method? Is one method preferable over another?
Thanks for your insight.
;-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad you liked them partner - hopefully the questioner will find them useful. :-)
ASKER
Thank you for taking the time to respond to this question -- I have been programming in Access for around 7 years now, and this site has been invaluable to me, both as a problem solver and as a learning tool.
I have not been using the querydef reference when trying the db.execute. I will try that.
One last question, though -- would you two consider one method superior over the other? Why would I want to use db.execute over docmd.openquery?? Am I correct in the rollback advantage?
Thanks again,
Ronda
I have not been using the querydef reference when trying the db.execute. I will try that.
One last question, though -- would you two consider one method superior over the other? Why would I want to use db.execute over docmd.openquery?? Am I correct in the rollback advantage?
Thanks again,
Ronda
Thank you for points.
As Leigh mention docmd.openquery it is prebuild Access Method.You could not use roll back on access functions just on DAO or ADO or ...
As Leigh mention docmd.openquery it is prebuild Access Method.You could not use roll back on access functions just on DAO or ADO or ...
You'd use a querydef *instead* of db.execute - because the querydef allows you to define the parameters.
If all you're looking to do is run an update SQL statement - then opening a DAO querydef seems a little unnecessary.
docmd.openquery will use Access expressions - but will ask for confirmation first (requiring Setwarnings to be set first to prevent it).
db.execute won't prompt - but at the same time won't use those expressions mentioned.
If all you're looking to do is run an update SQL statement - then opening a DAO querydef seems a little unnecessary.
docmd.openquery will use Access expressions - but will ask for confirmation first (requiring Setwarnings to be set first to prevent it).
db.execute won't prompt - but at the same time won't use those expressions mentioned.
very good explanations.
Regards
Dmitry