How to Build an SQL Query on the Fly for Data Export to Excel

Discussion in 'Microsoft Access Form Coding' started by Don, Feb 5, 2011.

  1. Don

    Don Guest

    Currently we are using a query to export some data to an Excel
    spreadsheet:

    Private Sub ExportPositions_Click()
    DoCmd.OpenQuery "QRY_REPORT_EXCEL_AllPositions", acViewNormal,
    acEdit
    DoCmd.RunCommand acCmdExportExcel
    DoCmd.Close acQuery, "QRY_REPORT_EXCEL_AllPositions"
    End Sub

    The customer likes the format of the resulting Excel spreadsheet, but
    we need a bit more flexibility in building the query on the fly, The
    "obvious" solution would be to replace the DoCmd.OpenQuery with a
    DoCmd.RunSQL and provide an appropriate SQL string making a temporary
    table and deleting it after use. However, the option making an
    intermediate table:

    DoCmd.RunSQL "SELECT * INTO tblTemp FROM tblTestData WHERE
    tblTestData.Status=""Filled"""

    causes a pop-up appears asking if you would like to add xx rows to
    tblTemp.

    Is there anyway to suppress the pop-up by somehow defaulting to
    accepting the creation of the table?

    Is there a more elegant way to approach this problem?

    Thanks!

    Don
     
    Don, Feb 5, 2011
    #1
    1. Advertisements

  2. Don

    Don Guest

    On Feb 5, 11:50 am, Marshall Barton <> wrote:
    > Don wrote:
    > >Currently we are using a query to export some data to an Excel
    > >spreadsheet:

    >
    > >Private Sub ExportPositions_Click()
    > >    DoCmd.OpenQuery "QRY_REPORT_EXCEL_AllPositions", acViewNormal,
    > >acEdit
    > >    DoCmd.RunCommand acCmdExportExcel
    > >    DoCmd.Close acQuery, "QRY_REPORT_EXCEL_AllPositions"
    > >End Sub

    >
    > >The customer likes the format of the resulting Excel spreadsheet, but
    > >we need a bit more flexibility in building the query on the fly, The
    > >"obvious" solution would be to replace the DoCmd.OpenQuery with a
    > >DoCmd.RunSQL and provide an appropriate SQL string making a temporary
    > >table and deleting it after use.  However, the option making an
    > >intermediate table:

    >
    > >DoCmd.RunSQL "SELECT * INTO tblTemp FROM tblTestData WHERE
    > >tblTestData.Status=""Filled"""

    >
    > >causes a pop-up appears asking if you would like to add xx rows to
    > >tblTemp.

    >
    > >Is there anyway to suppress the pop-up by somehow defaulting to
    > >accepting the creation of the table?

    >
    > >Is there a more elegant way to approach this problem?

    >
    > Better to set an existing query's SQL property and then use
    > the TransferSpreadsheet method (see VBA Help for details).
    > The code could be something like:
    >
    > Dim db As Database
    > Dim qdf As DAO.QueryDef
    > Dim strWhere As String
    > Const strBaseQuery As String = "SELECT * FROM tblTestData "
    > Set db = CurrentDb()
    > Set qdf = db.QueryDefs!QRY_REPORT_EXCEL
    >         'construct the query's where clause
    > strWhere = "WHERE tblTestData.Status='Filled' "
    >
    > qdf.SQL = strBaseQuery & strWhere
    > Set qdf = Nothing
    > Set db = Nothing
    > DoCmd.TransferSpreadsheet acExport, , QRY_REPORT_EXCEL, _
    >                    "path\workbookfile", ...
    >
    > --
    > Marsh
    > MVP [MS Access]- Hide quoted text -
    >
    > - Show quoted text -


    Awesome!

    Thanks Marsh!!

    Don
     
    Don, Feb 5, 2011
    #2
    1. Advertisements

  3. Don

    Mike Painter Guest

    Don wrote:
    <snip>
    >
    > causes a pop-up appears asking if you would like to add xx rows to
    > tblTemp.


    See SetWarnings in help to elimianate these questions
    DoCmd SetWarnings False
    DoCmd SetWarnings True
     
    Mike Painter, Feb 6, 2011
    #3
  4. Don

    Don Guest

    On Feb 5, 10:53 pm, "Mike Painter" <> wrote:
    > Don wrote:
    >
    > <snip>
    >
    >
    >
    > > causes a pop-up appears asking if you would like to add xx rows to
    > > tblTemp.

    >
    > See SetWarnings in help to elimianate these questions
    > DoCmd SetWarnings False
    > DoCmd SetWarnings True


    Also awesome!

    Thanks Mike!!


    Don
     
    Don, Feb 6, 2011
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Tony Wainwright

    Query on the fly

    Tony Wainwright, Apr 13, 2004, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    140
    Ivan Grozney
    Apr 13, 2004
  2. John

    Creating a query on the fly and getting an error message

    John, May 26, 2004, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    154
    TPratt
    May 26, 2004
  3. Guest

    On the Fly Query Prob

    Guest, Oct 28, 2004, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    154
    Guest
    Oct 28, 2004
  4. Guest

    SQL on the fly...?

    Guest, Sep 6, 2006, in forum: Microsoft Access Form Coding
    Replies:
    7
    Views:
    171
    Brendan Reynolds
    Sep 8, 2006
  5. DS

    SQL UPDATE on the fly

    DS, Aug 3, 2007, in forum: Microsoft Access Form Coding
    Replies:
    5
    Views:
    171
Loading...

Share This Page