How to Run an Access Macro within Outlook

[dwrpsych128]

I have a macro in Access 2010 that I created and now would like to run it automatically. I would like it to run when I open Outlook and press a button to execute. I have to tried to research a way of doing this but havent had much luck. How would I set this up? Also, would it be better to run the macro as a scheduled task or from within outlook?

 

[HiTechCoach]

Depending on what you are doing, yiou may be able to do it all inside Outlook using Outlook's VBA. With Outlook VBA you can use ADO or DAO to read/write from the tables in an Access database.

Would you mnind sharing more details about what you are trying to do?

 

[dwrpsych128]

Thanks for your reply. What I am trying to do is streamline my data extraction and automate as much as possible. I created a macro in Access 2010 that runs several queries by property crime type (burgs, mvts, vehicle break ins) based on the previous 3, 7, 14, and 30 days and exports the output to a folder. The macro works great!

I then created a model for each crime type in ArcGIS using modelbuilder based on the mentioned time periods which quickly plots the incidents onto a map so that such incidents can be analyzed. The model grabs the data output run from the macro and does some other geoprocessing tasks before plotting the dots on the map.

What I ultimately want to do is create a scheduled daily task so that the macro runs before I come in for work. Once the data is exported out to my automation folder I want to run another scheduled task to run the python script that I exported out from modelbuilder. This last step I think will be fairly easy to figure out. However, the primary issue is to figure out how to run an Access Macro as a scheduled daily task. I'm pretty sure I want to create a scheduled task without logging into outlook. How do you run an Access Macro as a scheduled task without opening Access? Also, if I did so would I have to leave my computer on all of the time or not shut down each night?

Hope this lengthy explanation helps.

 

[HiTechCoach]

I'm confused.

Based non your post title How to Run an Access Macro within Outlook?

What does Outlook have to do with the process? What am I missing?

... Also, if I did so would I have to leave my computer on all of the time or not shut down each night?

Yes, a computer has to be on to run something.


TIP: Powering down any electronic hardware is a sure way damage iwt which leads to failure.


About running software as a scheduled task:

It is possible to develop an Access database that can be run as a scheduled task. The key is to use UNC paths (no mapped drives) for linked tables and any networks resources. It also must run without any human interaction (enter password, etc). If The database can open, run the process and then close without any human interaction then it should work.

I find it best to create a separate Access front end that links to the back end data for the scheduled task. This way you can easily make it auto run and close without any human interaction. You want to keep the master copy of the database safe. Only use a copy for the scheduled task. When the task hangs and you must kill it, you run a very low risk of corrupting the data since it is in a separate back end. When the scheduled database gets corrupted, you can simply copy a replace from the master copy. I find it a Best Practice to regularly replace the scheduled copy.

I have never used AcrGIS modelbuilder so I don't know it it will work as a scheduled task. The same restriction will apply. Use UNC paths (no mapped drives) and no human interaction.

 

[spikepl]

One can power down to Standby/Sleep but not Hibernate - I believe a scheduled task can wake up the computer at the specified time. If not, there are alarm clocks on the web that you can download to wake up the ocmputer from stand by.