Embedded Queries vs Named Queries

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.

Answer 1

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...

Answer 2

Thanks Shrek! I appreciate your reply

Answer 3

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.

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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.

Answer 8

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