Missing SQL statements

[traciodi]

I have a bizarre problem. I recently created a database to do some
repetitive data processing. Now that I have stablized the queries, I have
set them up to run via a macro. I have also setup the macro automatically
export ("output to") the results of various queries to Excel.

Everything runs fine, until I try to re-run the macro. Somehow, 3 of the
SQL statements have been deleted from the queries. Specifically, the queries
are still there, but the queries now just have "SELECT;" instead of the
original statement. It's only with 3 (out of 11) particular queries and
exports.

Any ideas?

 

[pietlinden]

I have a bizarre problem.  I recently created a database to do some
repetitive data processing.  Now that I have stablized the queries, I have
set them up to run via a macro.  I have also setup the macro automatically
export ("output to") the results of various queries to Excel.

Everything runs fine, until I try to re-run the macro.  Somehow, 3 of the
SQL statements have been deleted from the queries.  Specifically, the queries
are still there, but the queries now just have "SELECT;" instead of the
original statement.  It's only with 3 (out of 11) particular queries and
exports.

Any ideas?

Macros are a PITA to troubleshoot from a distance. I would recommend
saving the macro as a module, and then posting the contents of the
function created by the conversion here. To do that, right-click on
the macro and select "Save As". You can rename the module, if you
want, but it's not necessary. Then click okay. Then open the module
and copy and paste the code with your message. Are you saving the
query inside your code?

Pieter

 

[traciodi]

No, I am not saving the queries within the code (if you need to see those,
let me know), just using the OpenQuery option. Any help you or anyone can
provide would be awesome!

The queries themselves run just fine, the exports ("output to") produce
exactly the results from the queries, but when I open the queries after the
macro has been run, the original statements are gone.

Here is the code from the save macro module:
Option Compare Database

'------------------------------------------------------------
' Step_6__File_Confirmation
'
'------------------------------------------------------------
Function Step_6__File_Confirmation()
On Error GoTo Step_6__File_Confirmation_Err

' Confirmation - Step01 - Ppl to Census Joins
DoCmd.OpenQuery "Confirmation - Step01 - Ppl to Census Joins", acNormal,
acEdit
DoCmd.Close acQuery, "Confirmation - Step01 - Ppl to Census Joins"
' Confirmation - Step02 - Ppl to Census to Hours Join
DoCmd.OpenQuery "Confirmation - Step02 - Ppl to Census to Hours Join",
acNormal, acEdit
DoCmd.Close acQuery, "Confirmation - Step02 - Ppl to Census to Hours Join"
' Confirmation - Step03 - Ppl BEN to PTO Compare
DoCmd.OpenQuery "Confirmation - Step03 - Ppl BEN to PTO Compare",
acNormal, acEdit
DoCmd.Close acQuery, "Confirmation - Step03 - Ppl BEN to PTO Compare"
' Confirmation - Step04 - PplSoft Split
DoCmd.OpenQuery "Confirmation - Step04 - PplSoft Split", acNormal, acEdit
DoCmd.Close acQuery, "Confirmation - Step04 - PplSoft Split"
' Confirmation - Step05 - PTO to Peoplesoft Join
DoCmd.OpenQuery "Confirmation - Step05 - PTO to Peoplesoft Join",
acNormal, acEdit
DoCmd.Close acQuery, "Confirmation - Step05 - PTO to Peoplesoft Compare"
' Confirmation - Step06 - PTO to Peoplesoft Compare
DoCmd.OpenQuery "Confirmation - Step06 - PTO to Peoplesoft Compare",
acNormal, acEdit
DoCmd.Close acQuery, "Confirmation - Step06 - PTO to Peoplesoft Compare"
' Change - VBas to Current VBas (use new VBas report for comparisons)
DoCmd.OpenQuery "Change - VBas to Current VBas", acNormal, acEdit
DoCmd.Close acQuery, "Change - VBas to Current VBas"
' Confirmation - Step07 - VBas Prep
DoCmd.OpenQuery "Confirmation - Step07 - VBas Prep", acNormal, acEdit
DoCmd.Close acQuery, "Confirmation - Step07 - VBas Prep"
' Confirmation - Step08 - PplSoft to VBas Compare
DoCmd.OpenQuery "Confirmation - Step08 - PplSoft to VBas Compare",
acNormal, acEdit
DoCmd.Close acQuery, "Confirmation - Step08 - PplSoft to VBas Compare"
' Confirmation - Step09 - Ppl to VBas BEN with Terms
DoCmd.OpenQuery "Confirmation - Step09 - Ppl to VBas BEN with Terms",
acNormal, acEdit
DoCmd.Close acQuery, "Confirmation - Step09 - Ppl to VBas BEN with Terms"
' Confirmation - Step10 - Ppl to VBas PTO with Terms
DoCmd.OpenQuery "Confirmation - Step10 - Ppl to VBas PTO with Terms",
acNormal, acEdit
DoCmd.Close acQuery, "Confirmation - Step10 - Ppl to VBas PTO with Terms"
' Confirmation - Step11 - Ppl to VBas BEN <> BenGrp
DoCmd.OpenQuery "Confirmation - Step11 - Ppl to VBas BEN <> BenGrp",
acNormal, acEdit
DoCmd.Close acQuery, "Confirmation - Step11 - Ppl to VBas BEN <> BenGrp"
' Export: Confirmation - Step03 - Ppl BEN to PTO Compare
DoCmd.OutputTo acQuery, "Confirmation - Step03 - Ppl BEN to PTO
Compare", "MicrosoftExcel(*.xls)", "F:\Departments\IT\QA\Traci\Confirmation -
Step03 - Ppl BEN to PTO Compare.xls", True, ""
' Export: Confirmation - Step06 - PTO to Peoplesoft Compare
DoCmd.OutputTo acQuery, "Confirmation - Step06 - PTO to Peoplesoft
Compare", "MicrosoftExcel(*.xls)", "F:\Departments\IT\QA\Traci\Confirmation -
Step06 - PTO to Peoplesoft Compare.xls", True, ""
' Export: Confirmation - Step09 - Ppl to VBas BEN with Terms
DoCmd.OutputTo acQuery, "Confirmation - Step09 - Ppl to VBas BEN with
Terms", "MicrosoftExcel(*.xls)", "F:\Departments\IT\QA\Traci\Confirmation -
Step09 - Ppl to VBas BEN with Terms.xls", True, ""
' Export: Confirmation - Step10 - Ppl to VBas PTO with Terms
DoCmd.OutputTo acQuery, "Confirmation - Step10 - Ppl to VBas PTO with
Terms", "MicrosoftExcel(*.xls)", "F:\Departments\IT\QA\Traci\Confirmation -
Step10 - Ppl to VBas PTO with Terms.xls", True, ""
' Export: Confirmation - Step11 - Ppl to VBas BEN <> BenGrp
DoCmd.OutputTo acQuery, "Confirmation - Step11 - Ppl to VBas BEN <>
BenGrp", "MicrosoftExcel(*.xls)", "F:\Departments\IT\QA\Traci\Confirmation -
Step11 - Ppl to VBas BEN NE BenGrp.xls", True, ""


Step_6__File_Confirmation_Exit:
Exit Function

Step_6__File_Confirmation_Err:
MsgBox Error$
Resume Step_6__File_Confirmation_Exit

End Function

******
It's the last 3 queries (Confirmation - Step09... thru Step11) that get
changed. All of the other queries remain unchanged after the macro runs.