Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Silver Spring, Maryland

    Unanswered: How can I test an sql statement before applying it to a query?

    How can I test an sql statement before applying it to a query:
    The reason I am asking this question is the following:

    I am trying to change the query “QryOGCGeneric” programmatically.
    If I send in a correct query (txtSQL = “Select * from tblAGENCY”)
    Everything is ok.
    The moment I send in wrong query (txtSQL = “Select * from tblAGENCIES”)

    I get an error: “Item cannot be found in the collection corresponding to the requested name or ordinal”

    This error will still come regardless of whether I send in a correct
    SQL statement thereafter or not.

    Private Function IsQueryGood() As Boolean

    Does not work either.

    Private Function PopulateQry(boolDisplayQry As Boolean)

    Dim strSQL As String
    Dim strSQL1 As String

    'Set a reference for Microsoft ADO Ext. 2.1
    'for DDL and security.
    Dim cat As ADOX.Catalog
    Dim cmd As ADODB.Command
    'Dim vwu As ADOX.View

    On Error GoTo PopulateQryErr

    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection

    'If TypeOfQuery("QryOGCGeneric") = "View" Then
    'txtSQL has already been validated

    Set cmd = cat.Views("QryOGCGeneric").Command

    cmd.CommandText = txtSQL
    Set cat.Views("QryOGCGeneric").Command = cmd

    Set cat = Nothing
    Set cmd = Nothing

    On Error GoTo 0
    Exit Function

    MsgBox Err.Description

    MsgBox "Error " & Err.Number & " " & Err.Description & vbCrLf & _
    " Most likely a wrong query", vbCritical, "OGC"

    Set cat = Nothing
    Set cmd = Nothing

    Resume Exit_This

    End Function

    Private Function IsQueryGood() As Boolean

    On Error GoTo HandleErr

    'DoCmd.OpenQuery strQueryName, acViewNormal

    IsQueryGood = False

    ' Test the sql statement first
    DoCmd.RunSQL txtSQL

    IsQueryGood = True

    Exit Function

    ' Error handling block added by Error Handler Add-In. DO NOT EDIT this block of code.
    ' Automatic error handler last updated at 10-02-2001 11:50:10 'ErrorHandler:$$D=10-02-2001 'ErrorHandler:$$T=11:50:10
    Select Case Err.Number
    Case Else
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "UtilityFunctions.OpenQueryAndWait" 'ErrorHandler:$$N=UtilityFunctions.OpenQueryAndWai t
    End Select
    ' End Error handling block.
    End Function

  2. #2
    Join Date
    Feb 2002
    What is txtSQL based on ? When you say you send in a wrong query, is it the initial txtsql or the dynamically created txtsql ?

  3. #3
    Join Date
    Feb 2002
    Silver Spring, Maryland
    I start out by creating a query with a good sql statement such as

    “Select * from tblAGENCY”

    This query is to be used to store info that a report will use. The contents of this query can can be changed programmtically.

    I am using Ken Getz's SQLScratchPad and I am giving a user the option of writing an SQL statement. I want to dump the results of that SQL statement into a Qury called "QryOGCGeneric"

    When a user now needs information about Agreements and types in

    “Select * from tblAgreement” (=txtSQL)

    instead of

    “Select * from tblAgreements” (=txtSQL)

    it corrupts "QryOGCGeneric" unless I close the application and restart.

    So I am looking for a way of testing a statement such as “Select * from tblAgreement” before I let it go the Query.



  4. #4
    Join Date
    Sep 2003
    ADOX has a catalog of queries divided into Views and Procedures. Views return data and has no parameters. Procedures do not return data, ie Update, Delete, etc, or does return data but with paramaters.

    When you updated a View query with an invalid Sql command, it no longer returns data. ADOX now sees it as a Procedure. That is why your original query name cannot be found in the Views collection.

    If you get a Not Found in Views, check the Procedures collection. It should be there. Update your commandtext.

    However if you get a valid set of data returned, your query will again be in the Views collection.

    Check both collections for your query.

Posting Permissions

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