Results 1 to 3 of 3
  1. #1
    AndyOlsen
    Guest

    Returning a query's SQL statement from a function.

    How would an Access97 query's SQL statement be captured in a variable? For example, I would like to create a function that returns a string. The string will contain the SQL statement of a query created in the QBE grid.

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

    Re: Returning a query's SQL statement from a function.

    Use the SQL property of the saved query. That will give you the full SQL for that query, and you don't even have to be able to run it. I often use this method to retrieve the SQL from a query that I need to modify because a table name has been changed. You can do it like this using DAO:

    <pre> strSQL = CurrentDb.QueryDefs("MyQry").SQL</pre>


    I haven't yet taken the time to figure out the ADO equivalent for Access 2000. <img src=/S/grin.gif border=0 width=15 height=15>
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning a query's SQL statement from a function.

    Try this:
    <font face="Comic Sans MS">
    Private Function SQLStatement() as String
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("yourQueryName")

    SQLStatement = qdf.SQL

    qdf.Close
    db.Close
    Set qdf = Nothing
    Set db = Nothing

    End Function
    </font face=comic>

Posting Permissions

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