• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

What is the best way to export the results of a query to excel or csv

If I have a query, for example "Stock Requirements" or "Bill of Materials" what is the best way to export the results to an excel or csv file to a folder on the local C:\MyFolder drive. Hopefully without over-riding any existing file.

I would like to have a button on the main form that exports it without a load of questions

Derek
0
DatabaseDek
Asked:
DatabaseDek
2 Solutions
 
Pasha KravtsovSupport EngineerCommented:
Create a view with your sql query and then build a SSIS Package to export the data automatically via SQL Server Agent.

Edit: Sorry forgot to switch it to Expert Comment
0
 
Rey Obrero (Capricorn1)Commented:
to export to excel

docmd.transferspreadsheet acexport,, "Stock Requirements", "C:\MyFolder\myExcel.xls", True


what version of office are you using ?
0
 
DatabaseDekAuthor Commented:
This is for an application that is packaged and goes out as an mde so it will have to work with all versions.

I suspect the old .xls will do. Is that what you are referring too?

Derek
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rey Obrero (Capricorn1)Commented:
ok, the code above should work.. try it and post back the result
0
 
PatHartmanCommented:
You need to make the destination a variable or you will keep overwriting the existing file and you probably want to do it in two parts.  Part 1 gets the path.  If this changes frequently, you should prompt for it each time.  If it is generally fixed for a user, then it should be stored in a table as part of a setup.  That way the user can choose the location once and not be prompted each time but still have the ability to go back and change the default location.  The second part is the file name.  You may want to include the date as a suffix or perhaps something else to make it unique.
0
 
DatabaseDekAuthor Commented:
I will try code and variable and post back.

Many thanks

Dere
0
 
DatabaseDekAuthor Commented:
Sorry took so long to come back.

Works a treat and I have a variable for the path

Thanks to you both

Derek
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now