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
' Test the sql statement first
IsQueryGood = True
' 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
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "UtilityFunctions.OpenQueryAndWait" 'ErrorHandler:$$N=UtilityFunctions.OpenQueryAndWai t
' End Error handling block.
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.