Results 1 to 2 of 2
  1. #1
    Star Lounger
    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 --
    db.execute strSQL
    docmd.runsql strSQL

    Is there any reason to prefer one approach over another? Any functional or performance differences?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts