Oracle - Access- Outlook

[saustin]

Hi All,

Am really enjoying a new access report which queries an Oracle db and then is emailed in outlook to several users. Works so well in fact that my users have now asked me if it is possible to be emailed this report ONLY WHEN certain records in the Oracle table are UPDATED. Have never done this before. Is there something on the oracle side that could run and accomplish this ?

Thanks, Steve.

 

[Felgar]

I think DBMS Alerts are what you are looking for. I've never used them so I can't give you any code or anything like that. But the idea is to put triggers on the tables and columns you want to monitor for changes. The trigger can call a dbms procedure to raise the alert.

In the application, you call an oracle dbms procedure which will not return until the alert is raised. So you would basically be waiting at the procedure call until the database is updated. Of course, you would proceed with preparing the report.

I've only read about the theory behind the alerts, sorry I cannot provide a concrete example. I'm also not sure what issues you might have with calling the Oracle procedure from within Access. Hopefully this points you in the right direction though.

 

[saustin]

Hi Felgar,
Thanks for the response. Am coming up empty on dbms alerts, but found lots of trigger info !! Now it's just a matter of creating an access form with a timer which would go out and run a report query as a result of the trigger. Cool. Steve.