Use Excel to trigger a process in Access.

[psycoperl]

I have some procedures that I have written in VBA in Access, I use an excel spreadsheet as one of my data sources in Access. I would like to know if there is a way when something happens in Excel (i.e its opened at 9AM) for it to trigger the process in access?

Ideally, when the file is opened in the morning (say 9AM) and in the afternoon (say 1PM) the access process would be called.

Is this possible? If so how could this be done?

 

[xenou]

You can use Excel to call Access and run any procedure:

Sub Do_Something()
    Set objAccess = CreateObject("Access.Application")
    With objAccess
        .OpenCurrentDatabase "C:\db1.mdb", False
        .Run ("MyProcedure")
    End With
End Sub

By the way you can schedule this to happen automatically through windows task scheduler and forgo the need to open Excel at all (which, by the way, if you do go through excel you'll need to create an event trigger and make sure that the event can't be fired more than once in a specific time frame). The only change would be to write the code as a vbs script and run it from windows task scheduler.

 

[psycoperl]

Thank you that was great....

Is there a way to send information (i,e. status messages) back to excel to let the user know what has been done?

 

[xenou]

Hi,

My personal preference is to write procedures as functions (value returning -- normally true/false or 0/1 to indicate success or failure). Then it is only necessary to write another function to email recipients the status code (i.e., if success, then "such and such procedure ran successfully", or if failure then "such and such procedure was completed with errors").

So:

Sub Do_Something()
    Set objAccess = CreateObject("Access.Application")
    With objAccess
        .OpenCurrentDatabase "C:\db1.mdb", False
        ret = .Run ("MyProcedure") '// procedure is  a function that returns a status code
        [COLOR="#FF0000"]Call emailEndUsers(ret)
[/COLOR]    End With
End Sub

To be honest, working with email in VBA is not my favorite as the only reliable method I have found is to use Outlook automation, which is somewhat onerous. Nevertheless, I do use it on a regular (daily) basis. I normally just leave my machine always on and Outlook always running in order to keep it simplest. BTW you can minimize outlook to the system tray so even if it is always running, it isn't "in your face".

If you aren't sure about using vba and outlook, try some simple tests to get acquainted (send yourself some emails). We can then tweak that to get it ramped up.

ξ

Edit:
note:

To be honest, working with email in VBA is not my favorite as the only reliable method I have found is to use Outlook automation, which is somewhat onerous

If your organization has a local email server, then actually CDO is very easy to use and is reliable. I used it exclusively until we moved to an exchange server "in the cloud", after which my life became more difficult.