none
How can I edit a query SQL with VBA? RRS feed

  • Question

  • This is probably simple, but I have a query and I can't figure out how to get a reference to it in VBA and edit its properties such as SQL and Connection. Instead I am just using docmd.deleteobject and recreating the queryDef, but that is causing issues. Long story short. How can I reference and existing QueryDef called "qryTop10" and edit its SQL so I don't have to delete/recreate it?
    Thanks!
    Wednesday, March 30, 2016 6:05 PM

Answers

  • Use the QueryDefs collection:

    Public Sub TestQueryDefs()
    
      Dim qd As DAO.QueryDef
      Dim db As DAO.Database
      
      Set db = CurrentDb
      Set qd = db.QueryDefs("qryTop10")
      
      Debug.Print "SQL:", qd.SQL
      Debug.Print "Connect:", qd.Connect
      
      Set qd = Nothing
      Set db = Nothing
    
    End Sub
    

    • Marked as answer by HTHP Wednesday, March 30, 2016 9:38 PM
    Wednesday, March 30, 2016 6:12 PM

All replies

  • Use the QueryDefs collection:

    Public Sub TestQueryDefs()
    
      Dim qd As DAO.QueryDef
      Dim db As DAO.Database
      
      Set db = CurrentDb
      Set qd = db.QueryDefs("qryTop10")
      
      Debug.Print "SQL:", qd.SQL
      Debug.Print "Connect:", qd.Connect
      
      Set qd = Nothing
      Set db = Nothing
    
    End Sub
    

    • Marked as answer by HTHP Wednesday, March 30, 2016 9:38 PM
    Wednesday, March 30, 2016 6:12 PM
  • As Stefan indicated, you can edit the SQL of the querydef.

    'New SQL for existing query:
    qd.SQL = "SELECT blah from Blah"



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

    Wednesday, March 30, 2016 7:40 PM
  • Thank you!
    Wednesday, March 30, 2016 9:38 PM
  • This is probably simple, but I have a query and I can't figure out how to get a reference to it in VBA and edit its properties such as SQL and Connection. Instead I am just using docmd.deleteobject and recreating the queryDef, but that is causing issues. Long story short. How can I reference and existing QueryDef called "qryTop10" and edit its SQL so I don't have to delete/recreate it?
    Thanks!

    Hi HTHP,

    I took a further step: i do not use QueryDefs anymore. All SQL-strings are generated dynamically and used for a multitude of different purposes.

    Imb.

    Wednesday, March 30, 2016 9:47 PM