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

I currently use Access to generate reports from MySQL and DB2 databases. I'm basically using Access as a reporting frontend using Pass Through queries. Is it possible to automate report generation from outside of Access, maybe through the use of an API?

I have reports that need to be generated as events take place in the database. For example: if an order comes into our DB then a pretty PDF copy of that order needs to be sent via email back to the customer.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

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 .

Dale FyeOwner, Developing Solutions LLCCommented:
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.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeOwner, Developing Solutions LLCCommented:
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).
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
To help you put this together:

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

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

 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>.

JeffDeveloperAuthor Commented:
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?
Dale FyeOwner, Developing Solutions LLCCommented:
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.
JeffDeveloperAuthor Commented:
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.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Entirely doable.

Here's the code you need to read in the command line arguments, which you would execute at start-up, and then a routine to grab the values as you need them in queries or what not.

That might look something like this:


          ' Get command line.
          ' Arg 1 is task flag
30        Call GetCommandLine(3)

          ' Check if task name was passed.
40        If IsNull(GetCommandLineArg(0)) Or IsEmpty(GetCommandLineArg(0)) Then
50            gstrMBTitle = "Command Argument Missing."
60            gstrMBMsg = "Extract task name must be passed as first command line argument."
70            gstrMBMsg = gstrMBMsg & vbCrLf & "Please correct."
80            gintMBDef = vbCritical + vbOKOnly
90            gintMBBeep = True
100           gintMBLog = False
110           Call DisplayMsgBox
120           GoTo Form_Open_Exit
130       End If

140       Select Case GetCommandLineArg(0)

              Case "EDI846"
                  ' Need to pass formatted data to Gentran for 846 dump.
150               Call SendEDI846(GetCommandLineArg(1))

Open in new window


Public Sub GetCommandLine(Optional intMaxArgs As Integer)

    Const RoutineName = "GetCommandLine"
    Const Version = "2.0"

    'Declare variables.
    Dim strChr As String
    Dim strCmdLine As String
    Dim strCmdLnLen As Integer
    Dim intInArg As Integer
    Dim intI As Integer
    Dim intNumArgs As Integer

    'See if intMaxArgs was provided.
10  If IsMissing(intMaxArgs) Then intMaxArgs = 10

    'Make array of the correct size.
20  ReDim gvarCmdArgArray(intMaxArgs - 1)
30  intNumArgs = 0
40  intInArg = False

    'Get command line arguments.
50  strCmdLine = Command()
60  strCmdLnLen = Len(strCmdLine)

    'Go thru command line one character at a time.
70  For intI = 1 To strCmdLnLen
80      strChr = Mid(strCmdLine, intI, 1)

        'Test for space or tab.
90      If (strChr <> " " And strChr <> vbTab) Then
            'Neither space nor tab.
            'Test if already in argument.
100         If Not intInArg Then
                'New argument begins.
                'Test for too many arguments.
110             If intNumArgs = intMaxArgs Then Exit For
120             intNumArgs = intNumArgs + 1
130             intInArg = True
140         End If
            'Add character to current argument.
150         gvarCmdArgArray(intNumArgs - 1) = gvarCmdArgArray(intNumArgs - 1) + strChr
160     Else
            'Found a space or tab.
            'Set intInArg flag to False.
170         intInArg = False
180     End If
190 Next intI

    ' Don't want to do this as some arguments may be optional.
    'Resize array just enough to hold arguments.
    'ReDim Preserve gvarCmdArgArray(intNumArgs - 1)

End Sub

Open in new window

Public Function GetCommandLineArg(intArgNumber) As Variant

    ' Returns an argument from the command line
    ' Null is returned on Error or non-existant argument

    Const RoutineName = "GetCommandLineArg"
    Const Version = "2.0"

10  On Error GoTo GetCommandLineArgError

20  If intArgNumber > UBound(gvarCmdArgArray()) Then
30      GetCommandLineArg = Null
40  Else
50      GetCommandLineArg = gvarCmdArgArray(intArgNumber)
60  End If

70  On Error Resume Next

80  Exit Function

90  UnexpectedError ModuleName, RoutineName, Version, Err.Number, Err.Description, Err.Source, VBA.Erl
100 GetCommandLineArg = Null
110 Resume GetCommandLineArgExit

End Function

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

JeffDeveloperAuthor Commented:
Jim. Thank you. I believe that this will work well once pushed into testing.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.