none
[SOLVED] Does DAO execute SQL within queries? RRS feed

  • Question

  • I am using DAO with a query I designed to see duplicate invoices numbers sorted by date desc.

    Here is the SQL code from the query.

    SELECT Ar.[Invoice No], Ar.Customer, Ar.[Invoiced Date]FROM Ar
    WHERE (((Ar.[Invoice No]) In (SELECT [Invoice No] FROM [Ar] As Tmp
    GROUP BY [Invoice No],[Customer] HAVING Count(*)>1  And [Customer] = [Ar].[Customer]))
    AND ((Ar.[Invoiced Date])>#8/10/2015#))
    ORDER BY Ar.Customer, Ar.[Invoiced Date] DESC;


    I have this in my code.

    Dim db As DAO.Database, rst As DAO.Recordset  
    Set db = CurrentDb()  
    Set rst = db.OpenRecordset("duplicatesForAr")

    Does using OpenRecordset([QUERY'S NAME]) the same as "  OpenRecordset( [THE QUERY'S SQL HERE] )

    In other ways of saying it: Is using the query's name the same as using the exact SQL string from that query?

    I asked this on another forum but there is a misunderstanding on what I'm trying to do or why I am doing it.

    I know what I am doing. I'm using DAO to get records for data manipulation, through the use of programming.





    • Edited by JoseKreif Monday, August 24, 2015 12:34 PM
    Friday, August 21, 2015 2:32 PM

Answers


  • So when using DAO, I wonder if using a query name is the exact same as using the SQL string that makes that query. 

    Hi. They're similar, but not exact. For all intents and purposes, you can use either one. The main difference is that using an SQL string might run a little slower (sometimes) than using a stored query definition because a stored query has been optimized by the engine already. Cheers!
    • Marked as answer by JoseKreif Monday, August 24, 2015 12:34 PM
    Friday, August 21, 2015 4:51 PM

All replies

  • Yes, you can use the query name instead of the SQL just like you have it. And if you know what you are doing why didn't you just try it? [smile]

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, August 21, 2015 3:03 PM
  • Hi. I think they are the same with a slight difference but why do you ask?
    Friday, August 21, 2015 3:05 PM
  • I did try, but I want to be positive it's doing what I think it's doing. I'm a software programmer, and it when code works the way you want it, when you your not sure the code does what it should, you get nervous whether it's working correctly, or there is some kind of coincidence.
    Friday, August 21, 2015 3:45 PM
  • Because I'm automating a system that deals with invoice information for a company. If I pull the wrong information It could be devastating.

    If I have to I'll copy that SQL string and assign it to a string and then execute it with DAO.

    I see  you can substitute table/query names with SQL statements. When using a table name, you are gonna get the table in basically no particular order. When you use a query name, I wonder if you are getting the data from the query in no particular order. I understand that Queries are showing data collected from other Tables/Queries, they dont actually hold data. Just a SQL string to pull that data together.

    So when using DAO, I wonder if using a query name is the exact same as using the SQL string that makes that query. 



    • Edited by JoseKreif Friday, August 21, 2015 3:51 PM
    Friday, August 21, 2015 3:50 PM

  • So when using DAO, I wonder if using a query name is the exact same as using the SQL string that makes that query. 

    Hi. They're similar, but not exact. For all intents and purposes, you can use either one. The main difference is that using an SQL string might run a little slower (sometimes) than using a stored query definition because a stored query has been optimized by the engine already. Cheers!
    • Marked as answer by JoseKreif Monday, August 24, 2015 12:34 PM
    Friday, August 21, 2015 4:51 PM
  • The recordset will return the records in the same order as the query. It does not matter if you use the name of a stored query or the explicit SQL expression in the OpenRecordset method.

    If you open a table

       Ste rs = CurrentDB.OpenRecordset("MyTable")

    then the records will be in the order that is defined by the primary key of the table.

    Matthias Kläy, Kläy Computing AG

    Sunday, August 23, 2015 6:27 PM
  • Though I realize help is not as great as it use to be, it does still have its benefits.  The question you've asked here could have been easily answered by referencing the help files in your program.  Please see the following excerpt:

    Access Developer
    Reference
    Database.OpenRecordset
    Method
    Creates
    a new Recordset object and appends it to the Recordsets collection.

    Syntax

    expression.OpenRecordset(Name,
    Type, Options, LockEdit)

    expression   A variable that represents a
    Database object.

    Parameters

    Name Required/Optional Data Type Description
    Name Required String

    The source of the records for the new Recordset.
    The source can be a table name, a query name, or an SQL statement (SQL string/statement: An expression that defines an SQL
    command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE
    and ORDER BY. SQL strings/statements are typically used in queries and in
    aggregate functions.)
    that returns records. For table-type
    Recordset objects in Microsoft Access database engine databases, the
    source can only be a table name.

    ....

    Sunday, August 23, 2015 6:45 PM