Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67

    Unhappy Unanswered: OutputTo wiping out SQL

    I have the following line of vba code:

    DoCmd.OutputTo acOutputQuery, "01_monthly_trend", acFormatXLS

    Which allows the user to designate the name and location of excel file being created from a query. When the code is run, the export works, but the sql is wiped out from the query (01_monthly_trend). When I open the query, my nice extended sql has been reduced to:

    SELECT;

    Of course, this means the query can never be run again!

    In debugging, I found that breakpoints were not working. I tried using the Stop command to force a break, but the code runs right over it.

    I just love Access. So reliable...

    Does this sound familiar to anyone?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    The only thing that I can think to do is to have a backup copy to replace the original with after running ...

  3. #3
    Join Date
    Oct 2003
    Location
    Brisbane, Australia
    Posts
    30
    Yeah I had the same problem with outputting a query to excel. The way I got around it was to output a block report that uses the query as its datasource and it fixed the problem....no more erasing! :-P

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb Re: OutputTo wiping out SQL

    Originally posted by pd9n
    I have the following line of vba code:

    DoCmd.OutputTo acOutputQuery, "01_monthly_trend", acFormatXLS

    Which allows the user to designate the name and location of excel file being created from a query. When the code is run, the export works, but the sql is wiped out from the query (01_monthly_trend). When I open the query, my nice extended sql has been reduced to:

    SELECT;

    Of course, this means the query can never be run again!

    In debugging, I found that breakpoints were not working. I tried using the Stop command to force a break, but the code runs right over it.

    I just love Access. So reliable...

    Does this sound familiar to anyone?
    You can convert your Query to SELECT .... INTO form, so you can create a table, then use the DoCmd.OutputTo acOutoutTable insted of
    acOutputQuery


    DoCmd.OutputTo acOutputTable , yourTableName

  5. #5
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67

    Re: OutputTo wiping out SQL

    shakireen awwi ya hammbakka

  6. #6
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67

    Talking

    Thanks, I decided to use the SELECT ... INTO option, because the users are used to the Excel format that results from tables and queries.

Posting Permissions

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