Results 1 to 2 of 2
2004-06-22, 19:20 #1
- Join Date
- Dec 2001
- Thanked 0 Times in 0 Posts
Running SQL statements from VBA (A2K)
I know of two simple ways (without going to QueryDefs) to run an SQL statement --
Is there any reason to prefer one approach over another? Any functional or performance differences?
2004-06-22, 19:41 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Running SQL statements from VBA (A2K)
There is a fundamental difference: DoCmd.RunSQL runs within the Access interface, while CurrentDb.Execute bypasses the Access interface and directly addresses the Jet Engine. Hence, DoCmd.RunSQL allows you to interface with the query, while CurrentDb.Execute is theoretically more efficient.
For example: you cannot execute a parameter query using CurrentDb.Execute, because you cannot supply the parameters. DoCmd.RunSQL will prompt you for the parameters.
DoCmd.RunSQL will ask for a confirmation ("You are about to ..."), CurrentDb.Execute won't. (Note: you can turn off the confirmation prompt by using DoCmd.SetWarnings False before, and DoCmd.SetWarnings True after the DoCmd.RunSQL instruction.)
If you open another database in code, db.Execute will run an action query in the other database. DoCmd.RunSQL will only run in the current database.
So which one you should use depends on your purpose. In general, I tend to use CurrentDb.Execute when possible.