Open MS Access and Fire Macro using MS Outlook

问题:

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.

回答1:

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.

回答2:

_{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"...

...