chrisdedobb
Registered User.
- Local time
- Yesterday, 17:44
- Joined
- Nov 4, 2004
- Messages
- 16
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?
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?
Code:
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