Modifing a Stored Query with VBA

[chrisdedobb]

I have a form that has an unbound field on it. This field is used to display the results of a SQL Query.

I created a button to export the results to excel which is using the DoCmd.OutputTo function.

I also have a Stored Query in Access that is blank (No tables or fields associated to it) titled "EXPORTSQL".

I used a function found on MSDN to modify existing queries which is named "ModifyQuery".

The first time I run this code below, by pressing the "Export" button, everything works prefectly. I get exactly what I need exported to excel and the query is modified and saved.

when I try to run it again to export new results, I get the following error:
"Item cannot be found in the collection corresponding to the request name or ordinal"
Problem is that it is there and I have spelled everything right in the code.

after researching what was happening a little more I found out that the modify function cannot find the stored query named "EXPORTSQL" after it does it the first time. It is still there and was not renamed. The Stored Query in not blank however. It still has the information from the first time I ran it. I can remove the data manually from the stored query and save it and then the export button will do what it is supposed to but only once.

I am completely lost and have no idea where to go next. Anyone have any thoughts or ideas?

Private Sub Export_Click()
On Error GoTo Err_export_Click

Dim strSQL As String

strSQL = Me.SQL_CODE

ModifyQuery "U:\ErrorTracker\DB\TEST\ErrorTracker.mdb", "EXPORTSQL", strSQL

DoCmd.OutputTo acQuery, "EXPORTSQL", "MicrosoftExcelBiff8(*.xls)", "", False, "", 0

Exit_export_Click:
    Exit Sub

Err_export_Click:
    MsgBox Err.Description
    Resume Exit_export_Click
    
End Sub

Public Sub ModifyQuery(strDBPath As String, _
                strQRYNAME As String, _
                strSQL As String)
   Dim catDB As ADOX.Catalog
   Dim cmd As ADODB.Command
   
   Set catDB = New ADOX.Catalog
   
   ' Open the Catalog object.
   catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & strDBPath
   
   Set cmd = New ADODB.Command

   'Gets the query from the Procedures collection.
   Set cmd = catDB.Procedures(strQRYNAME).Command
   
   'Updates the query's SQL statement.
   cmd.CommandText = strSQL
   
   'Saves the updated query.
   Set catDB.Procedures(strQRYNAME).Command = cmd
   
   Set catDB = Nothing

End Sub

 

[chrisdedobb]

Figured it out

I figured out how to reset the sotred sql to blank. Just incase someone else is trying to do this, here is what I did:

Private Sub Export_Click()
On Error GoTo Err_export_Click

Dim strSQL As String

strSQL = Me.SQL_CODE

ModifyQuery "U:\ErrorTracker\DB\TEST\ErrorTracker.mdb", "EXPORTSQL", strSQL

Exit_export_Click:
    Exit Sub

Err_export_Click:
    MsgBox Err.Description
    Resume Exit_export_Click
    
End Sub

Public Sub ModifyQuery(strDBPath As String, _
                strQRYNAME As String, _
                strSQL As String)
   Dim catDB As ADOX.Catalog
   Dim cmd As ADODB.Command
   
   Set catDB = New ADOX.Catalog
   
   ' Open the Catalog object.
   catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & strDBPath
   
   Set cmd = New ADODB.Command

   'Gets the query from the Procedures collection.
   Set cmd = catDB.Procedures(strQRYNAME).Command
   
   'Updates the query's SQL statement.
   cmd.CommandText = strSQL
   
   'Saves the updated query.
   Set catDB.Procedures(strQRYNAME).Command = cmd
   
   DoCmd.OutputTo acQuery, "EXPORTSQL", "MicrosoftExcelBiff8(*.xls)", "", False, "", 0
   
   'Gets the query again from the Procedures collection.
   Set cmd = catDB.Procedures(strQRYNAME).Command
   
   'Updates the query's SQL statement to blank.
   cmd.CommandText = "Select *"
   
   'Resaves the blank query.
   Set catDB.Procedures(strQRYNAME).Command = cmd
   
   Set catDB = Nothing

End Sub