Discussion:
SQL erases after the query is ran
(too old to reply)
WannaChevy03
2006-01-30 17:01:04 UTC
Permalink
Hi,
I have built a form, with a "run" button that runs multiple queries...

DoCmd.OpenQuery ("Get1 Spec Revs")
DoCmd.OpenQuery ("Get2 Plant Forms")
DoCmd.OpenQuery ("Get3 More Data")
If Check89 = True Then DoCmd.OutputTo acOutputQuery, "Get4 No HSI Form",
acFormatXLS, "C:\Temp\Output.xls", True

The first 3 queries are make-table queries, and the fourth is not. All 4
queries run just fine, and I get the data that I need, however, I can't run
it a second time because the SQL erases itself in the fourth query only!!!
This is VERY frustrating because I have to constantly copy and paste the SQL
back into it. Any suggestions?

Thanks!
Jessica
OfficeDev18 via AccessMonster.com
2006-01-30 17:18:55 UTC
Permalink
Is "Get4 No HSI Form" a query residing on the Query tab of your database? How
about posting the query's SQL so we can get a look at it?

Sam
Post by WannaChevy03
Hi,
I have built a form, with a "run" button that runs multiple queries...
DoCmd.OpenQuery ("Get1 Spec Revs")
DoCmd.OpenQuery ("Get2 Plant Forms")
DoCmd.OpenQuery ("Get3 More Data")
If Check89 = True Then DoCmd.OutputTo acOutputQuery, "Get4 No HSI Form",
acFormatXLS, "C:\Temp\Output.xls", True
The first 3 queries are make-table queries, and the fourth is not. All 4
queries run just fine, and I get the data that I need, however, I can't run
it a second time because the SQL erases itself in the fourth query only!!!
This is VERY frustrating because I have to constantly copy and paste the SQL
back into it. Any suggestions?
Thanks!
Jessica
--
Sam

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200601/1
WannaChevy03
2006-01-30 18:20:27 UTC
Permalink
Yes, it does reside on the query tab of my database. Here is the SQL for #4...

SELECT More_Data.PITEM_ID AS Specification, More_Data.PITEM_REVISION_ID AS
Revision, More_Data.PCREATION_DATE AS Date_Revised, More_Data.POS_USERNAME AS
Who_revised, More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE AS Object_Type
FROM More_Data
WHERE (((More_Data.PCREATION_DATE) Between [Forms]![No HSI
PRF]![CreateStart] And [Forms]![No HSI PRF]![CreateEnd]) AND
((More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE) Like [Forms]![No HSI
PRF]![DocType] And
(More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"End_Item Revision Master"
And (More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"SalesOrder Form" And
(More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"Alloy_Item Revision
Master" And (More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"Commodity
Sales Form" And
(More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"PlantReview Form" And
(More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"Category Form") AND
(([More_Data].[PITEM_ID] & [More_Data].[PITEM_REVISION_ID]) Not In (SELECT
More_Data.PITEM_ID & More_Data.PITEM_REVISION_ID FROM More_Data WHERE
INFODBA_PWORKSPACEOBJECT_POBJECT_NAME="G1A.HSI-Plant Review")))
ORDER BY More_Data.PITEM_ID, More_Data.PITEM_REVISION_ID;
OfficeDev18 via AccessMonster.com
2006-01-30 20:26:18 UTC
Permalink
I dunno. Anybody?

Sam
Post by WannaChevy03
Yes, it does reside on the query tab of my database. Here is the SQL for #4...
SELECT More_Data.PITEM_ID AS Specification, More_Data.PITEM_REVISION_ID AS
Revision, More_Data.PCREATION_DATE AS Date_Revised, More_Data.POS_USERNAME AS
Who_revised, More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE AS Object_Type
FROM More_Data
WHERE (((More_Data.PCREATION_DATE) Between [Forms]![No HSI
PRF]![CreateStart] And [Forms]![No HSI PRF]![CreateEnd]) AND
((More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE) Like [Forms]![No HSI
PRF]![DocType] And
(More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"End_Item Revision Master"
And (More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"SalesOrder Form" And
(More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"Alloy_Item Revision
Master" And (More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"Commodity
Sales Form" And
(More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"PlantReview Form" And
(More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"Category Form") AND
(([More_Data].[PITEM_ID] & [More_Data].[PITEM_REVISION_ID]) Not In (SELECT
More_Data.PITEM_ID & More_Data.PITEM_REVISION_ID FROM More_Data WHERE
INFODBA_PWORKSPACEOBJECT_POBJECT_NAME="G1A.HSI-Plant Review")))
ORDER BY More_Data.PITEM_ID, More_Data.PITEM_REVISION_ID;
--
Sam

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200601/1
Paul B
2006-02-28 11:45:01 UTC
Permalink
I'm having exactly the same problem as WannaChevy03 - running an OutputTo and
the SQL is being erased after the Query. Have found lots of references to
this problem on the net, but no solutions.

Anybody got any ideas?
Post by WannaChevy03
Yes, it does reside on the query tab of my database. Here is the SQL for #4...
SELECT More_Data.PITEM_ID AS Specification, More_Data.PITEM_REVISION_ID AS
Revision, More_Data.PCREATION_DATE AS Date_Revised, More_Data.POS_USERNAME AS
Who_revised, More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE AS Object_Type
FROM More_Data
WHERE (((More_Data.PCREATION_DATE) Between [Forms]![No HSI
PRF]![CreateStart] And [Forms]![No HSI PRF]![CreateEnd]) AND
((More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE) Like [Forms]![No HSI
PRF]![DocType] And
(More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"End_Item Revision Master"
And (More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"SalesOrder Form" And
(More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"Alloy_Item Revision
Master" And (More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"Commodity
Sales Form" And
(More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"PlantReview Form" And
(More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"Category Form") AND
(([More_Data].[PITEM_ID] & [More_Data].[PITEM_REVISION_ID]) Not In (SELECT
More_Data.PITEM_ID & More_Data.PITEM_REVISION_ID FROM More_Data WHERE
INFODBA_PWORKSPACEOBJECT_POBJECT_NAME="G1A.HSI-Plant Review")))
ORDER BY More_Data.PITEM_ID, More_Data.PITEM_REVISION_ID;
d***@gmail.com
2015-05-18 14:48:11 UTC
Permalink
Same here. My query uses fields in a form to filter data, and I am trying to use a macro to export the query results as an Excel file, but the query just erases itself.
g***@gmail.com
2017-06-19 14:33:59 UTC
Permalink
Post by WannaChevy03
Hi,
I have built a form, with a "run" button that runs multiple queries...
DoCmd.OpenQuery ("Get1 Spec Revs")
DoCmd.OpenQuery ("Get2 Plant Forms")
DoCmd.OpenQuery ("Get3 More Data")
If Check89 = True Then DoCmd.OutputTo acOutputQuery, "Get4 No HSI Form",
acFormatXLS, "C:\Temp\Output.xls", True
The first 3 queries are make-table queries, and the fourth is not. All 4
queries run just fine, and I get the data that I need, however, I can't run
it a second time because the SQL erases itself in the fourth query only!!!
This is VERY frustrating because I have to constantly copy and paste the SQL
back into it. Any suggestions?
Thanks!
Jessica
If it's deleting your query, then maybe write to an instance of Excel using the Get4 query as a recordset:

Dim xl, get4 As Recordset
Set get4 = CurrentDb.CreateQueryDef("", CurrentDb.QueryDefs("Get4 No HSI Form").SQL).OpenRecordset
'If records are returned...
If get4.RecordCount > 0 Then
Set xl = CreateObject("Excel.Application").Workbooks.Add
get4.MoveFirst
''''Add fields to Excel file
For f = 0 to get4.Fields.Count - 1
xl.Worksheets(1).Cells(1, f+1).Value = get4.Fields(f).Name
Next
''''Add recordset contents to Excel file
Do Until get4.EOF
For f = 0 to get4.Fields.Count - 1
xl.Worksheets(1).Cells(get4.AbsolutePosition + 2, f+1).Value = get4.Fields(f).Value
Next
''''Move to next record
get4.MoveNext
Next
''''Show Excel file
xl.Application.Visible = True
End If

Loading...