Results 1 to 10 of 10

Thread: SQL or query?

  1. #1
    Join Date
    Sep 2004
    Posts
    17

    Question Unanswered: SQL or query?

    My Database application is getting quite large with lots queries now. But is it better for the application to convert these into SQL statements in code?, would the application be smaller or run faster?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    There's really no difference. the overhead required to "store" a query as an object as opposed to hard-coded vba is negligable.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    There is a difference tho ... With stored queries (procedures) Access optimizes them so that they run the fastest. The penalty is that they have to be bound to a defined object (form, table, etc...) [except for parameters - which can also affect the performance]. With coded queries (within VBA) there is a performance hit because Access has to translate the request into SQL code (internal) and perform the optimization - this hit is not significant. The benefit is that you're not tied to existing forms or tables ... You can construct things on the fly ... ( And for those who'll argue that the same can be done with hard queries it can ... But you'll have to daisychain the queries and/or run a macro) ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    With stored queries (procedures) Access optimizes them so that they run the fastest.
    I can now sleep well tonight knowing I've learned something usefull today.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Sep 2004
    Posts
    17
    Thanks guys

  6. #6
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244

    Lightbulb

    There's another advantage of running SQL from code - you can use variables in the SQL, so this immediately gives you a much bigger scope for working with SQL.

    If you ARE running SQL from VBA, be sure to use the .Execute method of the database, rather than DoCmd.RunSQL - it works much faster.


    Andrew Briggs
    Elmhurst Solutions Limited
    Database Design & Consultancy
    http://www.elmhurstsolutions.com
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  7. #7
    Join Date
    Mar 2004
    Location
    Cambridge, Ontario
    Posts
    21
    There's another advantage of running SQL from code - you can use variables in the SQL
    Interesting...
    I apologize for being off topic here, but I simply must ask.
    How do you go about passing a variable created in VBA to an SQL statement?
    ie: Create a variable (perhaps a calculation) in a procedure and run an SQL statement using this variable.

    Also could you explain your reasoning on the .execute method? (I have been using the DoCmd.RunSQL method)

    Thanks alot
    ---
    Phil J.
    Cherry Forest Veneers Ltd.

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by andybriggs
    There's another advantage of running SQL from code - you can use variables in the SQL, so this immediately gives you a much bigger scope for working with SQL.

    If you ARE running SQL from VBA, be sure to use the .Execute method of the database, rather than DoCmd.RunSQL - it works much faster.
    Andy,

    With the proviso that they understand that running the RunSQL ONLY WORKS within the database currently open and operated in whereas the .Execute method can run the query within any connected database ... Oh, and the .Execute method is ADO, it's not available in DAO ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by spiderweb
    Interesting...
    ...
    How do you go about passing a variable created in VBA to an SQL statement?
    ie: Create a variable (perhaps a calculation) in a procedure and run an SQL statement using this variable.

    ...
    Thanks alot
    Well you just "add" it in ... Ex:

    SQLString="SELECT * FROM " & CallingSomeFunctionHere & " WHERE (BlahBlahBlah='" & SomeOtherConditionVariable & "');"
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    Yes, that's right, DoCmd.RunSQL is only available within the current database.

    But the .Execute method is available in both DAO and ADO.

    Not sure exactly why it runs faster : it just does!

    I'd advise anybody using .Execute for the first time to read the Help page about it, as you can use additional parameters with it that can cause problems.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

Posting Permissions

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