Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    8

    Unanswered: ADO equivalent to SQL property of QueryDef object

    HI

    In DAO I can retrieve the actual SQL string that makes up a query by doing the following:

    Dim qrs as QueryDef
    Dim strSQL as string
    Set qrs = CurrentDb.QueryDefs("qryReportsGeneric")
    strSQL = qrs.SQL

    The value of strSQL is then "SELECT * FROM..."

    However, I seem to be unable to do this using ADO. I've tried many different ways using both command and recordset objects and cannot get this string. Is there an ADO equivalent to the QueryDef SQL property?

    Any help would be greatly appreciated.

    Brian

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: ADO equivalent to SQL property of QueryDef object

    You have to use ADO's OpenSchema() function.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Mar 2004
    Posts
    8
    Thanks for the reply

    The OpenSchema method seems a bit too high-level for what I want. I just want to retrieve the SELECT statement of a specific Access query.
    Do you have any suggestions?

    Brian

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    This should do it:


    public function ViewName(QueryName as string) as string
    Dim A(0 To 2) As Variant, RS As ADODB.RecordSet
    Let A(0) = Empty
    Let A(1) = Empty
    Let A(2) = QueryName

    Set RS = ADO_Conn.OpenSchema(adSchemaViews, A())
    if RS.Eof Then
    Let ViewName = vbnullstring
    Else
    Let ViewName = RS.View_Definition
    End If
    Set RS = Nothing
    End Function
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  5. #5
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: ADO equivalent to SQL property of QueryDef object

    Originally posted by Dmitriew
    HI

    In DAO I can retrieve the actual SQL string that makes up a query by doing the following:

    Dim qrs as QueryDef
    Dim strSQL as string
    Set qrs = CurrentDb.QueryDefs("qryReportsGeneric")
    strSQL = qrs.SQL

    The value of strSQL is then "SELECT * FROM..."

    However, I seem to be unable to do this using ADO. I've tried many different ways using both command and recordset objects and cannot get this string. Is there an ADO equivalent to the QueryDef SQL property?

    Any help would be greatly appreciated.

    Brian


    Have you tried using the ADOX library along with the ADODB library?

    This will work if you want to go this route.

    Dim cat1 As ADOX.Catalog, qdf As ADOX.View, cmd As ADODB.Command

    Set cat1 = New ADOX.Catalog
    cat1.ActiveConnection = CurrentProject.Connection
    Set qdf = cat1.Views("Your Query")
    Set cmd = qdf.Command
    MsgBox cmd.CommandText
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  6. #6
    Join Date
    Mar 2004
    Posts
    8
    Thanks a lot to both of you.

    DoktorBlue: I get an 'Method or data member not found' error on:
    Let ViewName = RS.View_Definition; I don't think .View_Definition is a valid criteria value.

    Gregg: your code seems to work nicely on regular select queries; I get an error message when applying it to a parameter query but think that it'll work fine if I pass the parameter values before running the code you sent.

    Again, thanks to both of you.

    Brian

  7. #7
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by Dmitriew
    Thanks a lot to both of you.

    DoktorBlue: I get an 'Method or data member not found' error on:
    Let ViewName = RS.View_Definition; I don't think .View_Definition is a valid criteria value.

    Gregg: your code seems to work nicely on regular select queries; I get an error message when applying it to a parameter query but think that it'll work fine if I pass the parameter values before running the code you sent.

    Again, thanks to both of you.

    Brian

    It's RS!View_Definition
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  8. #8
    Join Date
    Mar 2004
    Posts
    8
    Thanks a lot to both of you.

    DoktorBlue: I get an 'Method or data member not found' error on:
    Let ViewName = RS.View_Definition; I don't think .View_Definition is a valid criteria value.

    Gregg: your code seems to work nicely on regular select queries; I get an error message when applying it to a parameter query but think that it'll work fine if I pass the parameter values before running the code you sent.

    Again, thanks to both of you.

    Brian

  9. #9
    Join Date
    Mar 2004
    Posts
    8
    Right on; works with rs!View_Definition

  10. #10
    Join Date
    Mar 2004
    Posts
    8
    With Gregg's code I realized why I was getting an error when trying to run it on a parameter query; qdf has to be defined as ADOx.Procedure instead of ADOx.View when using parameter queries.

Posting Permissions

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