Automatically export report as PDF

[napsterxp]

Hi there, before I start please note I have little/no VBA experience so please be kind



Relatively speaking, I've got a pretty simple database. The presenters at our community radio station fill out a form of what song they play and this (along with the system time) is stored in a table.

I have a wildcard query to bring up all entries from a certain date and at present, I export a report of this by hand on a Saturday for everyday of the previous week - but I want to automate this?!

I can change the query to be a standard one to just bring up entries for today. I then, somehow, need to automatically export a report of this query as a PDF at midnight every night into a certain folder with the file name being today's date.

This is where my knowledge starts to fizzle out so any help would be greatly appreciated and I'll of course give you a shout out on air!

Adam

[June7]

Task automation with Access is not simple. The only way I could accomplish was to have a VBScript file run by Windows ScheduledTasks.

ScheduledTasks runs the script at designated times.

The script opens database. Code behind default open form executes my export if time conditions met.

Use Notepad to create and save text file. Change file extension from txt to vbs.

My script code is:

' ProcessKillLocal.vbs
' Sample VBScript to kill a program
' Author Guy Thomas http://computerperformance.co.uk/
' Version 2.7 - December 2010
' ------------------------ -------------------------------' 
Option Explicit
Dim objWMIService, objProcess, colProcess
Dim strComputer, strProcessKill 
strComputer = "."
strProcessKill = "'msaccess.exe'" 
If (Month(Date()) >= 6 And Month(Date()) <= 9) Or ((Month(Date()) <= 5 Or Month(Date()) >= 10) And Day(Date()) < 8) Then

Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2") 
Set colProcess = objWMIService.ExecQuery ("Select * from Win32_Process Where Name = " & strProcessKill )
For Each objProcess in colProcess
 objProcess.Terminate()
Next 
'WSCript.Echo "Just killed process " & strProcessKill & " on " & strComputer
'WScript.Quit 
'End of WMI Example of a Kill Process 

Dim objFSO, oShell
Set objFSO = CreateObject("Scripting.FileSystemObject")
'open the Access file
Set oShell = CreateObject("WScript.Shell")
oShell.Run """C:\Program Files\Microsoft Office\Office12\msaccess.exe"" ""databasae file path\name"""
End If