Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1157
  • Last Modified:

How to automate to Run MS Access Report every day, SAve as PDF and send email

i have MS Access Report. I need to automate the procedure and make sure the report will run every morning at 7 AM, save as PDF and send email to the particular person
IS is possible and what needs to be done?
0
rfedorov
Asked:
rfedorov
  • 9
  • 8
  • 2
  • +2
3 Solutions
 
mmarisCommented:
"MARS is a dynamic, flexible, function-rich and intuitive automation tool for scheduling, exporting, distributing and delivering your Microsoft® Access reports, queries and macros. "

http://www.christiansteven.com/products/mars/
0
 
rfedorovAuthor Commented:
thank you, do you know how much the license?
0
 
mmarisCommented:
no I don't, sorry.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
rfedorovAuthor Commented:
looks like, this is not what i want
it too complicated and not free
0
 
rfedorovAuthor Commented:
How can I use Windows "Scheduler" to run a batch file, at a specified time and frequency.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What version of Access are you using?

You can add a Task to the Scheduler that will run your access database, and fire off a macro when needed. This macro could perform the duties you need. To do that:

"full path to msaccess.exe" "full path to your database" /x "your macro name"

Please note that if you have either an AutoExec macro, or a Startup form defined in your database this may cause you trouble, since Access would also fire those items.
0
 
rfedorovAuthor Commented:
Thank You,
I am using Access 2003
how to add a a Task to the Scheduler?
Could you be a litttle bit more precise
0
 
Helen FeddemaCommented:
The Scheduler is a Windows app.  For Windows XP, use the Scheduled Tasks applet in the Control Panel.  For Vista, open the Administrative Tools program group in the Control Panel, and then the Task Scheduler.
0
 
Helen FeddemaCommented:
You can write a VBScript (to run from the Task Scheduler) to do the report printing, supposing you have created a macro or function.  Here is a sample script:
Dim appAccess 
Dim strDBName

Set appAccess= WScript.CreateObject("Access.Application")
strDBName = "D:\Documents\Northwind.mdb"
appAccess.OpenCurrentDatabase strDBName
appAccess.Run "FunctionName"

Open in new window

0
 
Helen FeddemaCommented:
You can create the script in Notepad, or the Microsoft Script Editor.  Save it with the .vbs extension, and then select it to run from the Task Scheduler.
0
 
rfedorovAuthor Commented:
It looks like thefirst part is working, but strange way
What i did:
This is my file called ReportToRun.Vbs
Set objAccess = CreateObject("Access.Application")
ObjAccess.OpenCurrentDatabase "pathtodb\CARE_DB.mdb"
ObjAccess.DoCmd.OpenReport "MyReport", acViewPreview
Set ObjAccess = Nothing
i set up all dates and time, and at specified time it runs, but gives me Security Warning form with
three buttons : Cancel. Open and MOre info
I have to press Open and then the form just dissaper, runs report and closes
i do not want to see the warnig, it should pass    


   

 
0
 
The_Girl_EarlCommented:
starting from the beginning
Create yourself a macro in your access database.
This should open the report and then do a send to the email address / addresses of your choice in your chosen format.

Test it quickly to see it does what you need it to do....

Once you have done that, open Task scheduler, and go to create task...

Give the task a name in the first screen - in the triggers tab - set it to run at 7am and choose the relevant options of being logged in etc.

In the Actions tab you need to create a New Action
You need it to start a program from the dropdown options given.

Then, in the program script box, put in the path to the msaccess.exe on your machine. For example...

"C:\Program Files(x86)\Microsoft Office\Office14\MSACCESS.EXE"

- note - yours will possibly be in Program files without the (x86) - I'm on Windows 7 with Office 2010.... if you're not sure where msaccess.exe is on your machine, just do a search for it...

In the Add Arguments (optional) put in the path to your database, and the name of the macro you have created to run... for example, I have a database callsed servicedesk2010 on my desktop, and my macro is called Macro1 ( note - I am using windows 7, so the path to your desktop will be different if you're not, but this is an example...

"C:\users\earlj\desktop\servicedesk2010.mdb" /x "Macro1"

If you want any further conditions etc. then just add them into the relevant boxes, but this will do nicely.

Don't forget the " in the relevant places as above.

Give it a quick test, all should be ok.
0
 
rfedorovAuthor Commented:
It looks like thefirst part is working, but strange way
What i did:
This is my file called ReportToRun.Vbs
Set objAccess = CreateObject("Access.Application")
ObjAccess.OpenCurrentDatabase "pathtodb\CARE_DB.mdb"
ObjAccess.DoCmd.OpenReport "MyReport", acViewPreview
Set ObjAccess = Nothing
i set up all dates and time, and at specified time it runs, but gives me Security Warning form with
three buttons : Cancel. Open and MOre info
I have to press Open and then the form just dissaper, runs report and closes
i do not want to see the warnig, it should pass  
0
 
Helen FeddemaCommented:
Do what The Girl Earl said -- make a macro in your Access database to do what you want, test it to make sure it runs, then run the macro from the VBScript, using this syntax:
ObjAccess.DoCmd.RunMacro "MacroName"
0
 
rfedorovAuthor Commented:
Thank you, any help with macro,please?
0
 
The_Girl_EarlCommented:
very easy... go to macro section in your database, and open a new blank macro...
First line
OpenReport
Then in the bottom, put in the report name and the view ie preview
then
SendObject
in the bottom, use the relevant dropdown to pick Report, and then your report name, then the view you want it sending as ... excel, word etc... then the email address you want it sending to... subject line, text etc.
You can use a third line command to Close the report too, if you want.

Then just save the macro as whatever you want to name it.
0
 
rfedorovAuthor Commented:
Thank you, I am confused now
let pretend my database located in "pathtodb\CARE_DB.mdb"
and name of the report "MYReport"
email is "name@hotmail.com"
how to write the macro,please.....
0
 
Helen FeddemaCommented:
I recommend doing this task in 4 steps:
1.  A function that outputs the report to a PDF file
2.  A macro that calls the function with the RunCode action
3.  A VBScript that runs the macro
4.  A Windows Scheduled Task to run the VBScript

The functionis listed below; replace the object names with yours.
Private Sub SendPDFReport()
'Created by Helen Feddema 17-Jan-2010
'Last modified 17-Jan-2010

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim itm As Outlook.MailItem
   Dim strFileName As String
   Dim rpt As Access.Report
   Dim strReport As String
   Dim strCurrentPath As String
   Dim strFileNameAndPath As String
   
   strCurrentPath = Application.CurrentProject.Path & "\"
   strReport = "rptProductPrices"
   strFileName = "Product Prices.pdf"
   strFileNameAndPath = strCurrentPath & strFileName
   
   'Output report to PDF in current path
   DoCmd.OutputTo objecttype:=acOutputReport, _
      objectname:=strReport, _
      outputformat:=acformatpdf, _
      outputfile:=strFileNameAndPath, _
      autostart:=False
   
   'Create new mail message and attach text file to it
   Set itm = appOutlook.CreateItem(olMailItem)
   With itm
      .To = "someone@xyz.com"
      .Subject = "Daily report"
      .Body = "Your message"
      .Attachments.Add strFileNameAndPath
      'To edit before sending
      .Display
      'To send automatically
      '.Send
   End With
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 
Helen FeddemaCommented:
On second thought, you don't need to create a macro, just run the code from the VBScript:
Dim appAccess  
Dim strDBName 
 
Set appAccess= WScript.CreateObject("Access.Application") 
strDBName = "D:\Documents\Northwind.mdb" 
appAccess.OpenCurrentDatabase strDBName 
appAccess.RunCode "SendPDFReport"

Open in new window

0
 
Helen FeddemaCommented:
Correction:  The RunCode in the VBScript should just be Run (it is RunCode as a macro action)
If you get messages asking if you want to Open, etc., that requires you to go through some steps to turn off these annoying security messages.  See my Access Archon # (attached)
accarch119.zip
0
 
Helen FeddemaCommented:
I don't think you can do the whole process in a macro (in particular, the saving to a file and then attaching the file to a mail message).
0
 
rfedorovAuthor Commented:
Helen, Thank you for your help.
I am able to run the script automatically, and print report i need
Done
Now. i want you modify a little bit the process
the only thing i need ---to save to PDF file in the folder i specify

This is my script
//////////////////////////////////////////////////////////////
Set objAccess = CreateObject("Access.Application")
ObjAccess.OpenCurrentDatabase "\My Path\CARE_DB.mdb"
ObjAccess.DoCmd.OpenReport "PAULA report", acViewPreview
Set ObjAccess = Nothing
///////////////////////////////////////////////////////////

please help me to finish the task and possible enter the line
Save to pdf in the folder C:/backup
Just that...

thank you
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 9
  • 8
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now