none
Embedded Queries vs Named Queries RRS feed

  • Question

  • Hello fellow developers,

    I would like to know what are the advantages and disadvantages of embedded queries and named queries in a Microsoft Access report. Or more specifically, under which conditions is better to use a Named Query instead of an Embedded Query. Is one more CPU intensive than the other?

    I have already discovered that some options of Conditional Formatting (such as Date Bars) are not available when using an Embedded Query.

    Thank you very much for your consideration.

    Thursday, October 19, 2017 3:25 PM

All replies

  • Hi,

    "Embedded" queries will transfer (import/export) with the object, so it could be considered as an advantage, I guess.

    I don't think there is any difference in execution time whether you use one query over another. Used to be, you would want a saved (named) query because it will have an execution plan already. However, if you use parameter queries, then it doesn't work the same; plus, with faster computers now, it doesn't really matter.

    Just my 2 cents...

    Thursday, October 19, 2017 5:16 PM
  • Thanks Shrek! I appreciate your reply

    Thursday, October 19, 2017 9:03 PM
  • I would like to know what are the advantages and disadvantages of embedded queries and named queries in a Microsoft Access report.

    Hi Paul,

    Essentially I do not use any named queries. Not because of performance reasons, but by concept.

    All queries are generated dynamically, depending on the nead and the context on that place on that moment. Instead of working with whole sql-strings I use 4 substrings that compose the final sql-string: the SELECT-part, the FROM-part, the WHERE-part, and the ORDER-part. In this way about 95% of the functionality of any application is automated.

    Maintenance of applictions becomes very easy with this concept.

    Imb.

    Thursday, October 19, 2017 9:42 PM
  • Greetings,

    Here is an example of a DAO Recordset object doing some data manipulation

    Dim RS As DAO.Recordet, str1 As String

    Set RS = CurrentDB.OpenRecordset("tableABC")  '--this  gets all records from tableABC
    --or
    str1 = txtbox1.Text
    Set RS = CurrentDB.OpenRecordset("Select * From tableABC Where fldx = " & str1)  '--this is kind of embeded sql with a filter

    Do While Not RS.EOF
      '--insert records in a table for example
    Loop

    And Here is a sample using Jet Sql

    Dim strSql As String

    strSql = "Insert Into tableDEF(fld1,fld2,fld3) Select fld1,  fld2, fld3 from tableABC Where fldx = " & txtbox1.Text
    DoCmd.RunSql strSql

    The Jet Sql method does the same thing that the DAO sample does except with way less code. 

    Conclusion:  embded queries is the more efficient way to manipulate data


    Rich P


    • Edited by Rich P123 Thursday, October 19, 2017 10:58 PM .......
    Thursday, October 19, 2017 10:58 PM
  • I've always been told the same thing theDBguys explained.

    It primarily has to do with optimization/execution plans, but with today's computers it has become somewhat inconsequential.


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Friday, October 20, 2017 2:21 AM
  • It primarily has to do with optimization/execution plans, but with today's computers it has become somewhat inconsequential.


    Yes, optimization issues have definitely been resolved with the hardware of today. Now it is a matter of writing spaghetti code vs clean(er) code. Since performance is hardly an issue these days it would be a matter of how much time it takes to develop an application and maintain it. Spaghetti code is always harder to maintain (and yes, there was a time that I was a chef Boyardee of spaghetti code),  but I moved on to lean cuisine type coding -- way easier on the guts of the program.


    Rich P

    Friday, October 20, 2017 4:21 PM
  • but I moved on to lean cuisine type coding -- way easier on the guts of the program.

    Hi Rich,

    I agree, but what do you consider aslean cuisine type coding. In my opinion it is maximised re-usability of code.

    Imb.

    Friday, October 20, 2017 5:28 PM
  • Spaghetti Code to populate an existing table from another table (with similar columns)

    Dim RS1 As DAO.Recordset, RS2 As DAO.Recordset

    Set RS1 = CurrentDB.OpenRecordset("tbl1")
    Set RS2 = CurrentDB.OpenRecordset("tbl2")

    RS1.MoveFirst
    Do While Not RS1.EOF
       If RS1!fldx = 'something' Then
          RS2.AddNew
          RS2!fld1 = RS1!fld1
          RS2!fld2 = RS1!fld2
          RS2!fld3 = RS1!fld3
          ...
          RS2.Update
       End If
       RS1.MoveNext
    Loop

    -- versus clean code to perform the same operation

    Dim strSql As String
    strSql = "Insert Into tbl2 (fld1, fld2, fld3, ...) Select fld1, fld2, fld2, ... from tbl1 Where fdlx = 'something'"

    --or if tbl1 and tbl2 has the exact same structure

    strSql = "Insert Into tbl2 Select * from tbl1"

    DoCmd.RunSql strSql


    Rich P

    Wednesday, October 25, 2017 7:02 PM