PC Review


Reply
Thread Tools Rating: Thread Rating: 1 votes, 5.00 average.

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

 
 
Don
Guest
Posts: n/a
 
      5th Feb 2011
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
 
Reply With Quote
 
 
 
 
Don
Guest
Posts: n/a
 
      5th Feb 2011
On Feb 5, 11:50*am, Marshall Barton <(E-Mail Removed)> 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
 
Reply With Quote
 
 
 
 
Mike Painter
Guest
Posts: n/a
 
      6th Feb 2011
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


 
Reply With Quote
 
Don
Guest
Posts: n/a
 
      6th Feb 2011
On Feb 5, 10:53*pm, "Mike Painter" <(E-Mail Removed)> 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



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
The Next Build Shall be RTM guys not any other builds unless MS decides to do one more build because of bugs before RTM RC2 build 5477 is the last build before RTM Drew Windows Vista General Discussion 8 13th Oct 2006 12:41 AM
I meant RC2 Build 5744 is the last build unless ms find more bugs before the RTM build and gold version there only make another build if there more bugs and not ready for RTM Drew Windows Vista General Discussion 4 12th Oct 2006 02:17 PM
SWsoft Acronis Disk Director Suite 9.0 Build 508, Acronis OS Selector 8.0 Build 917, Acronis Partition Expert 2003 Build 292, Acronis Power Utilities 2004 Build 502, F-SECURE.ANTI vIRUS.PROXY v1.10.17.WINALL, F-SECURE.ANTI vIRUS v5.50.10260 for CITRI vvcd Storage Devices 2 4th Dec 2005 12:46 PM
SWsoft Acronis Disk Director Suite 9.0 Build 508, Acronis OS Selector 8.0 Build 917, Acronis Partition Expert 2003 Build 292, Acronis Power Utilities 2004 Build 502, F-SECURE.ANTI vIRUS.PROXY v1.10.17.WINALL, F-SECURE.ANTI vIRUS v5.50.10260 for CITRI code_fu Anti-Virus 2 2nd Oct 2004 09:32 AM
SWsoft Acronis Disk Director Suite 9.0 Build 508, Acronis OS Selector 8.0 Build 917, Acronis Partition Expert 2003 Build 292, Acronis Power Utilities 2004 Build 502, F-SECURE.ANTI vIRUS.PROXY v1.10.17.WINALL, F-SECURE.ANTI vIRUS v5.50.10260 for CITRI code_fu Windows XP Basics 0 2nd Oct 2004 05:11 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:27 AM.