Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Aug 2004
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query SQL dissappearing (2003)

    Hi

    I have the following query SQL:
    PARAMETERS [Forms]![frm_reportsmenu]![PanelList] Text ( 255 );
    SELECT qryInvoicePayments.Legaid, rqryStatus.TotValue AS [Total QQAA], qryInvoicePayments.TotalQQAA AS [Total Paid]
    FROM qryInvoicePayments LEFT JOIN rqryStatus ON qryInvoicePayments.Legaid=rqryStatus.legaid
    WHERE (((rqryStatus.Panel) In (Forms!frm_reportsmenu!PanelList))) Or (((Forms!frm_reportsmenu!PanelList)="ALL"))
    ORDER BY rqryStatus.Measure, rqryStatus.StatusDt;

    which via a form inputbox statement:
    Forms!frm_reportsmenu!PanelList = InputBox("Enter ALL or Panel abbrev", "Panel", "ALL")

    runs the following by dumping the data to Excel:
    DoCmd.OutputTo acQuery, stDocName, "MicrosoftExcelBiff8(*.xls)", "M:" & stDocName & ".xls", True

    This runs as expected the FIRST TIME ONLY. The second time, I get the error message 3066, and look to find that whilst the QUERY exists the SQL has dissappeared completely.

    Any suggestions as to how I can retain my SQL?

    Thanks
    Steve W.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query SQL dissappearing (2003)

    Is this really an Excel question?

  3. #3
    New Lounger
    Join Date
    Aug 2004
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query SQL dissappearing (2003)

    Thanks HansV

    It's an Access problem. Is there a simple way to redirect this to the correct bucket? Or do I need to repost in ACCESS?

    Steve W.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query SQL dissappearing (2003)

    The thread has been moved to the Access forum.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query SQL dissappearing (2003)

    What exactly do you mean by "the SQL has disappeared"? What happens if you open the query in design view?

  6. #6
    New Lounger
    Join Date
    Aug 2004
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query SQL dissappearing (2003)

    The SQL before dumping to excel moves from:
    PARAMETERS [Forms]![frm_reportsmenu]![PanelList] Text ( 255 );
    SELECT qryInvoicePayments.Legaid, rqryStatus.TotValue AS [Total QQAA], qryInvoicePayments.TotalQQAA AS [Total Paid]
    FROM qryInvoicePayments LEFT JOIN rqryStatus ON qryInvoicePayments.Legaid=rqryStatus.legaid
    WHERE (((rqryStatus.Panel) In (Forms!frm_reportsmenu!PanelList))) Or (((Forms!frm_reportsmenu!PanelList)="ALL"))
    ORDER BY rqryStatus.Measure, rqryStatus.StatusDt;

    after dumping its reconfigured itself to only:
    SELECT;

    And in design view no tables, queries, fields are shown. It's ready for me to start design from the beginning.

    Thanks
    Steve W.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query SQL dissappearing (2003)

    I have never seen this problem myself, but there are a few newgroup threads reporting exactly the same thing, unfortunately without being resolved. This one mentions a workaround.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query SQL dissappearing (2003)

    I've never seen this behavior either, though we don't frequently export to Excel using the DoCmd.OutputTo command. It appears that the problem has been around for at least two versions based on the 2002 post Hans found. You don't indicate what Service Pack you have installed for Access 2003. If you don't have SP1 installed, I would be certain to do so.

    Another kludgy workaround would be to set the SQL in the QueryDef each time you execute the statement - and yet a third option would be to attach to an existing workbook and use the Excel ODBC driver to populate the workbook. You could also try the TransferSpreadsheet method - it is more recent functionallity and may be more robust, and I think it more closely follows the manual export you can do. Finally, you could use Automation to create a new workbook by stepping throgh a recordset created by your query.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •