Is it possible to automate Access reports from outside of Access?

Jeff,

Yes,  it’s possible in several ways:

1. Setup a macro in the app, then use the /x  command line switch to execute the macro when the app opens.

2.  Do basically the same thing, by using command line argument with the /CMD command line switch (more flexible)

3.  Uses start up form code.

4.   Control access with OLE automation from another application.

#2 is usually the best  approach as it’s the most flexible and can be called from anything including the windows task scheduler

 I  have code that I can share that will help with that  if you want .

Jim

But if this is event driven, as you indicated in your message (an order comes in), and you want that email to be sent within a short period of time, then you might simply leave the application running and setup a form with a Timer event set to fire every minute (or some other interval).  When the timer event occurs, you run the code which checks for the events you want to respond to and execute the appropriate report.

However, emailing from within Access is generally done using the SendObject method or by automating Outlook.  Both of these require user intervention (implemented back in 2007 with Outlook Security), so you might want to look into a program like vbMAPI to actually send the reports.

There's no right or wrong here, but personally I would not leave it running.

I've done systems/automation both ways, and I've found your generally better off to fire it up as needed, make some fast checks if anything needs doing, and if not, quit.

 While you can be good in development (i.e. closing objects, always being explicit, etc), bugs in Access itself can cause problems, and in many cases there is nothing you can do about those. Starting over each time gives you a clean slate and a lot less problems with resources.  I'd only leave it running 24 x 7 if the checks/tasks needed to be performed at intervals < 1 minute.  

 There's also the middle of the road approach; leave it running for the most part, but only for an hour or so and then have it quit.  Then fire it right back up with the task scheduler or trigger it from an outside app.

Jim.

I agree with Jim about NOT having the reporting application running 24/7.  However, I do have several clients who do something along these lines with the Task Scheduler launching the application each morning and then the application shuts itself down at some predetermined time.

Personally, I would rather have the application running from 06:00 - 18:00 than have Task scheduler start the application every X minutes and have the application close when it completes all of its tasks.  I have had occassions where processing the immediate tasks took longer than expected, and the Task Scheduler was not able to start the application again, because it was still running.

I guess it really depends on your business model (how quickly you want to respond to events).

To help you put this together:

https://www.experts-exchange.com/videos/537/MS-Access-Different-Ways-to-Start-Up-a-Database.html

Shows different ways you can start up Access and have it do something (5 minute video).

https://www.experts-exchange.com/articles/2104/Avoiding-running-multiple-instances-of-an-application.html

Tackles the problem of an app taking too long and another instance being fired off before the first is finished.

https://www.experts-exchange.com/articles/5328/Resource-locking-in-your-applications.html

 This is a way of flagging resources within your app to prevent them from being used by multiple instances.  For example, in my automated reporting apps, I use a "printer", which has a disk file set as its port to generate PDF's.  The printer "prints" raw data to a file, which is then formatted into a PDF, so I can't have two applications using the printer at the same time.   I use this code to place a "lock" on the printer until the process is complete.

 I know this probably seems like a lot, but you can put together a very robust automated solution fairly quickly with the above along with vbMAPI that Dale mentioned (which is also what I use for e-mailing).

 In most of my systems, I have an AutoExtract app which has as it's sole job is to extract data and produce output, be it an e-mail with text, e-mail with attachements (PDF, spreadsheets, CSV's, XML, or whatever), or sends files to other places (ie. using FTP, sFTP, AS2, etc).

 In other words exactly what your trying to do<g>.

Jim.

I'm looking at the command line switch... When an order is received it's given a confirmation number. If I had a method to fire up a command prompt and execute something like D:\Access\TestDB.accdb /cmd 456789 (where 456789 is the confirmation number) could I pass that to a variable in VBA to run a report for that confirmation number?

How are you monitoring "when an order is received"?

Does your BE support triggers and email?  If so, you might be able to send an email to yourself or some other account and use Outlook automation to send the report upon receipt of that email?  Otherwise, you have to have some method of monitoring when an order is received.

I have a data agent that monitors the MySQL DB and transfers data to various other servers. I'm thinking it can be programmed to fire up  a command prompt and pass the required values through the /cmd switch. If I can capture those values and assign them to variables to be used in a query to populate the report then I would be most of the way home. From there I would need to export or print to PDF.

One note: you'll need to update the Error handlers for whatever you use, but what's there will give you a good idea of what needs to be done.

Jim.

Jim. Thank you. I believe that this will work well once pushed into testing.