Open MS Access and Fire Macro using MS Outlook

Question

I did a bit of research and I think the best way to auto-fire a Macro is to use the AutoExec method in Access.

I believe the script below will do the job.

Option Compare Database

'------------------------------------------------------------
' AutoExec
'
'------------------------------------------------------------
Function AutoExec()
On Error GoTo AutoExec_Err

    DoCmd.RunCommand acCmdWindowHide
    MsgBox "Welcome to the client billing application!", vbOKOnly, "Welcome"
    DoCmd.OpenTable "Orders", acViewNormal, acEdit


AutoExec_Exit:
    Exit Function

AutoExec_Err:
    MsgBox Error$
    Resume AutoExec_Exit

End Function

My question, now, is what is the best way to trigger the event of opening the Access DB? Unfortunately the Windows Task Scheduler has been turned off by my IT department (gotta love it).

I'm thinking there must be a way to get Outlook to open the Access DB as a Task, or some such thing. I experimented with a few ideas, but haven't been able to get anything working.

Does anyone here have any idea how to do this?

To add a bit more color to this, basically I want to auto-import data from a remote SQL Server database, into Access. As you may have guessed, the SQL Server Agent has been disabled too.

I am trying to run this job as a daily process, using Outlook, because that's really all I have available right now.

Answer 1

_{For your IT team:}
- Microsoft TechNet: Why You Shouldn’t Disable The Task Scheduler Service in Windows

_{...on the other hand, in I.T.'s defence:}
- Slashdot discussion : Why Everyone Hates the IT Department

_{...and for you:}
- Stack Exchange: How can a developer can ask for more freedom from IT policies

---

Your question reminds me of [one of many of] my side projects, which I've been meaning to use as example with a Q&A on I'm planning on writing, "Data entry to Access via SMS text messages".

It also reminded me of countless debates (battles?) of days gone by. Forgive me as I go slightly off-topic in a rant, "Developers vs I.T."...

---

I can't speak for everyone's situation, but in my opinion, some jobs worse than others for different departments defeating each other's work by doing their own jobs, and I figure there's a clear correlation, basically that "the larger or more 'government-associated' the company is, the bigger the headaches"...

... ' o̲n̲e̲ ?"

Unfortunately unreasonable restrictions placed on competent developers, mandated by off-site management, can result in a bigger security hole than the "privilege" would have.

---

Auto-execute a procedure in Access

Back to your question, you have a few options to auto-run procedures in Access:

• Create an AutoExec Macro, with a single command: RunCode. It will automatically run when the DB is opened. Instead of using the macro interface, you can just have a single command, that runs a VBA function.

  

  Note that is has to be a Function (not a Sub) and the function must use zero parameters. (More info)

• Set a Startup Form in File > Options > Current Database, and then set the form's Form_Open procedure to call your code. (More info)

• If the database is usually going to be open, you could use a form's Timer events to schedule one-time or recurring tasks. I can't recall whether Access behaves like Excel, which (if you don't cancel a pending event before closing the application) will automatically re-open the application. (More info.)

• Include the /X: switch when opening Access to specify the name of a macro to execute. For example, on command-line or in a .BAT batch file or a desktop shortcut:

  C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE /x:myMacro

• Include the /CMD: switch when opening Access to specify custom options. Then, within VBA, you can test the value of the switch, if any, with the COMMAND function. For example:

  Command Line:

  MSACCESS.EXE /cmd:Take actions one and two

  VBA:

  If InStr(Command,"two") <> 0 Then
'do action two
End if

Note that spaces are allowed, and therefore the /CMD must be the last switch used.

More about Office's command-line switches and Access's Command function.

_{...I think I'm missing another way (besides Task Scheduler), but maybe it will come to me. Obviously, third-party application are another choice that I won't get into because I've never tried them.}

---

Run Access from Outlook

You can set rules for emails with an action of Run a Script, which will call an Outlook VBA procedure, which can in turn open another file as necessary. Outlook tasks and calendar appointments can also be coerced into running a script.

With these two options, you can set it up so your code runs at certain times, intervals, or even on certain actions occurring to any extent of complexity as you desire.

_{For example you could}:

***"Start procedure X when an email is received, but only if:

• it was sent from my cellphone (as a text-to-email), and,

• it contains a specific keyword in the subject line."***

^{(hence the basis for my "data entry via text messages" project I mentioned earlier!)}

---

More Information:

• Getting Things Done – Outlook Task Automation with PowerShell

• How to Auto Create New Tasks When Receiving Specific Emails in Outlook

• How can you schedule automatic emails in Outlook 2013 for every weekday at a fixed time?

• MSDN : Working with Outlook Events

• MSDN : Getting Started: Interaction between Office Applications

• MSDN : Using Visual Basic for Applications in Outlook

Answer 2

I would normally recommend Windows Task Scheduler but as you said, you don't have access to that (I'd still consider other alternatives for that - i.e. a third party scheduler or having IT add a scheduled task for you).

But if you must...

You can use an event in Outlook VBA to trigger code when a recurring Task reaches its reminder. In that event, you can open your Access database.

Caveats:

• You need to lower macro security in Outlook. You may not be allowed to do this and at the very least you should consider the ramifications of this.
• The processing in Access will block Outlook while it runs.
• The Task must have a reminder to trigger. The code below hides the reminder popup, but without setting a reminder, the event doesn't run.

This code must be in the ThisOutlookSession module within the Outlook VBA IDE:

Private WithEvents m_reminders As Outlook.Reminders

Private Sub Application_Startup()
    Set m_reminders = Application.Reminders
End Sub

Private Sub m_reminders_BeforeReminderShow(Cancel As Boolean)
    Dim reminderObj As Reminder
    For Each reminderObj In m_reminders
        If reminderObj.Caption = "MyDailyAccessImport" Then
            Dim accessApp As Object
            Set accessApp = CreateObject("Access.Application")
            accessApp.Visible = True
            accessApp.OpenCurrentDatabase "C:\Foo\MyDatabase.accdb"
            Cancel = True
            Exit For
        End If
    Next
End Sub

Then, in your database, use an AutoExec macro to do the processing you require.