Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    using saved queries with ADO (Access 2000 SR1)

    Hi

    I'm just getting to grips with syntax changes from DAO to ADO. In my old Access 97 code (DAO) I have used several techniques like this:

    set dbs=currentDb
    dbs.Execute "name of saved query"
    -------------------------------------------------
    or:
    set qdf = dbs.QueryDefs("name of saved query")
    qdf.Execute
    -------------------------------------------------
    or:
    set rst=dbs.OpenRecordset("name of saved query")

    ---------------------------------------------------

    Do these have equivalents in ADO - i.e. can I open or execute saved query names - or do I have to resort to getting the SQL property of the query and executing that (or opening an ADO recordset based on this SQL)?

    Thanks

    Jeremy

  2. #2
    Lounger
    Join Date
    Apr 2001
    Location
    Khon Kaen, Thailand
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using saved queries with ADO (Access 2000 SR1)

    Hi,

    Here's what I have done so far.

    Sub DeleteDAO()
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    dbs.Execute "Delete * From Test;"
    dbs.Close
    End Sub

    Sub DeleteADO()
    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection
    cnn.Execute "Delete * From Test;"
    cnn.Close
    End Sub

    Find more info about moving from DAO to ADO at <A target="_blank" HREF=http://msdn.microsoft.com/library/techart/daotoadoupdate.htm>http://msdn.microsoft.com/library/te...oadoupdate.htm</A>.

    HTH.
    Tim K.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: using saved queries with ADO (Access 2000 SR1)

    Saved queries are already optimized, so they're the preferred method where possible. As with DAO, there are multiple ways of running a query from ADO, either a saved query or using a SQL string. Look up the Execute method in Access 2000 help by typing "Execute a query" into the Answer Wizard tab (without the quotes) and you'll see a list of topics, including the Execute method for both the Command and Connection objects and the Open method for a recordset. All of these may be used with either a SQL string or a saved query.
    Charlotte

Posting Permissions

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