Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Access query output scheduling

Sep 22, 2004 4:41AM PDT

Hi,

I am having here a situation where I need to run a SQL query every night and generate the output into an Excel file.

I am looking for any solutions how to do this !!!

Appreciate any replys

Discussion is locked

- Collapse -
Re: Access query output scheduling
Sep 22, 2004 4:49AM PDT

Run the query to some text output file, then post process it to either an Excel file with your custom application or ... just put it in a .CSV file so Excel can open it.

I will not write your application for you, but will share how I solved a too similar application.

Bob

- Collapse -
Re: Access query output scheduling
Sep 22, 2004 6:26AM PDT

That doesn't seem too difficult, even unattented (I'm not sure if that's what you mean, but it's my understanding of 'scheduling').

Write an autoexec-macro that runs and exports the query (TransferSpreadsheet, fill in the name of the query when it asks for a table), then exits Access (call a VBA-function that has a docmd.quit in its body). That's a two-line macro, and a three-line function, 5 lines combined.

Then use task scheduler to start MS Access whatever time you want it. It will execute the autoexec-macro.


If you're behind the PC to start it, all you need is a one-line macro (with the TransferSpreadsheet in it).

Hope this helps.


Kees