MS Access auto/scheduled report export.

[22strider]

Hello All,

Is it possible to run MS Access report export (to MS Excel) by a user that does not have MS Access?

I need to share report that uses data over ODBC to multiple remotely located users. Not all of them have MS Access. I was wondering if there was a way they could run export that would update report in Excel format.

Thanks for your help.

Regards,
Rajesh

 

[welshgasman]

Access has a runtime version.?

 

[Dbc23]

If I'm understanding correctly you need to send the excel export of the access query to someone routinely?

If so, this is something (after a lot of learning) I've managed to do quite effectively for lots of daily reports.

First you need to create a vba function to do run the query and extract it with a "docmd.openquery" statement and designate a filename (usually a variable you create with the filepath, the name, and a variable to append the date onto the file name).

Then you create a vba script to send them an email of that file once it's created.

Then you create a macro in access to run that function.

Then you create a batch file to execute that macro.

Then you create a routine task in the windows task scheduler to execute that batch file at a given time each day.

 

[22strider]

Hello Dbc23,

What you mentioned is correct; I need to share excel export report.

Rather than sending email I was hoping to save excel export on shared drive. I can put Access db on that shared drive as well. I am trying to stay away from having to manually trigger that report. Reason is that not everybody that has access to the shared drive has MS Access.

Can that be done following steps that you have listed in your post?

Regards,
Rajesh

 

[Dbc23]

Totally, it actually simplifies it.

So you have your query created "querywhatever"

You now create a function in VBA (a sub should work, but for some reason I got less issues when it's a function).

In there you create a few variables.
today(string) = date()
FilePath (string) = The filepath you'll save the file to on the shared drive, including the final ""
Filename = Filepath & "What you want the file called-" & today & ".xlsx"

Then you execute a do.cmd

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "queryWhatever", FileName, True

It can help to wrap it with a "DoCmd.SetWarnings False" in case there's some error fields in the extracts but you should really validate the query ahead of time to make sure either those errors are OK or it's error free.

And probably have it set to Application.Close when done

Then you create a macro to run the function (you can use the macro builder)

Then make a batch file and put it somewhere simple (usually root of C: is a good spot)

Then the batch file is just a text file with a string in it like:
"C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE" "C:\Users\username\Documents\YOURDATABASE.accdb" /X NAMEOFYOURMACRO

And task scheduler you can search for in the start menu, it's pretty straightforward to schedule the task to run a .bat file, so however often you have the task scheduled to run, it opens your batch file, which opens access and executes your VBA that runs the query and saves it as an excel file in the designated directory, bam, one less thing to do every day.

 

[22strider]

Thank you very much for your help. I will follow your guidelines and report back. I might need further help in creating batch file and task scheduler. I will get back in couple of days with more specific queries.

Thanks again,
Rajesh