使用MS Outlook打开MS Access和Fire宏 [英] Open MS Access and Fire Macro using MS Outlook

问题描述

我做了一些研究，我认为自动触发宏的最佳方法是在Access中使用AutoExec方法.

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

我的问题现在是触发打开Access DB事件的最佳方法是什么?不幸的是，Windows任务计划程序已被我的IT部门关闭(该喜欢).

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

我在想，必须有一种方法可以使Outlook作为任务打开Access DB或类似的东西.我尝试了一些想法，但未能使任何事情起作用.

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?

要为此添加更多颜色，基本上，我想将数据从远程SQL Server数据库自动导入到Access中.您可能已经猜到了，SQL Server代理也已被禁用.

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.

我正在尝试使用Outlook将这项工作作为日常工作来运行，因为这实际上是我现在所能使用的全部功能.

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

推荐答案

我通常会推荐Windows Task Scheduler，但是正如您所说，您无权访问(我仍会考虑其他替代方法-即第三方计划程序或由IT人员为您添加计划的任务.

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

您可以使用Outlook VBA中的事件在重复任务到达其提醒时触发代码.在这种情况下，您可以打开Access数据库.

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.

注意事项:

• 您需要降低Outlook中的宏安全性.您可能不被允许 为此，至少您应该考虑 这的后果.
• Access中的处理将阻止Outlook 它运行时.
• 任务必须具有提醒才能触发.代码 下面隐藏了提醒弹出窗口，但是在未设置提醒的情况下， 事件未运行.

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

此代码必须在Outlook VBA IDE的ThisOutlookSession模块中:

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

然后，在数据库中，使用AutoExec宏进行所需的处理.

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

这篇关于使用MS Outlook打开MS Access和Fire宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！