Scheduled Report Creation

[Gwill1983]

Hi all,

I am fairly new to access and trying to establish whether there is a way to schedule a report within my db to create and save to a file location every Friday at 9am?

I have tried to investigate through power automate, but seems like the link is a premium function which isnt something that I currently have.

Is anybody able to give me a little guidance to see if this is a possibility before I look for alternatives?
Thanks

 

[petertenthije]

I am going to assume you know how to make a macro in Access that creates and saves a report.

What you can then do, is use Windows Taskscheduler to create a scheduled task.

To make Windows Taskscheduler work, you first need to create a VBS script.
To do so, open the notepad, and type the following code:

Dim accessApp
set accessApp = createObject("Access.Application")
accessApp.visible = true
accessApp.UserControl = true

accessApp.OpenCurrentDataBase("C:\foldername\yourdatabasename.accdb")
accessApp.DoCmd.RunMacro "Macroname"
accessApp.application.quit

Save the file as a TXT file, and close it.
Now, rename the extension of the TXT file to VBS.
If you now double click on the VBS, it will open a session of Access, open database "yourdatabasename.accdb" and run the macro named "Macroname".
If this works as expected, then you can open the windows Taskscheduler.

Inside the Taskscheduler, create a new task. From there you can follow the steps the taskscheduler guides you through.

 

[Micron]

If your org won't allow you to save & call such script files, have TS run a shortcut. That shortcut can use a command line switch called cmd. When db opens, code can check if the command property of the db exists. If so, it is TS who has opened. If not, it is a regular user. You can test with something like
If Trim(Command) = "" Then <-- regular user, or at least one who knows better than to use said shortcut if they can even see it.