Trigger Access macro when live table changes?

[Charlie8585]

All,
I have a couple of live tables in an MS Access file linked to an online SQL db. I need to have Access automatically send an email (via Outlook) when that table changes.
E.g. if a customer creates a subscription on website then customer_subscription table would have a new entry. I'd like this to trigger Access to autosend 'subscription confirmed' email to customer instantly (& without a human needing to click a button).

Is this possible? And, if so, thoughts on most elegant way to do it?

Note:

• Perfectly familiar re how to send emails through Outlook/VBA - this is more a question of how to trigger the Send macro when order comes in
• Am well aware that Access is not 'proper' platform for this sort of thing - this is just a quick & dirty temporary measure for a few weeks...

C.

 

[ebs17]

It is certainly most elegant when the procedure that writes data into the table also calls the procedure for creating the mail.

 

[jdraw]

What is the DBMS of the online SQL database?
Not sure it matters, but perhaps you could have a trigger to invoke a "notification event" to initiate your "auto send process".
I agree with ebs17, would be good to have the "send confirmation" and the "add new entry" in same procedure.

 

[GPGeorge]

All,
I have a couple of live tables in an MS Access file linked to an online SQL db. I need to have Access automatically send an email (via Outlook) when that table changes.
E.g. if a customer creates a subscription on website then customer_subscription table would have a new entry. I'd like this to trigger Access to autosend 'subscription confirmed' email to customer instantly (& without a human needing to click a button).

Is this possible? And, if so, thoughts on most elegant way to do it?

Note:

• Perfectly familiar re how to send emails through Outlook/VBA - this is more a question of how to trigger the Send macro when order comes in
• Am well aware that Access is not 'proper' platform for this sort of thing - this is just a quick & dirty temporary measure for a few weeks...

C.

If a record in the local Access table is changed as a result of some update to the SQL Server table, you could write a Data Macro on the local Access table to respond to that change, i.e. an inserted, updated or deleted record in the Access table. A Data Macro can invoke a VBA function that handles your notification process.

You use the term "live table" here, so I'm not sure what that implies about the tables involved, though.

 

[Pat Hartman]

Access (the Jet/ACE) engine has no way of knowing what happens in a SQL Server (or whatever) database connected to a web site. It might not even be open. as @ebs17 implied, only the database where the record was insterted knows that something happened. You would need a trigger in that database to send the email. Either an actual on insert trigger on a table or simply code in the procedure that logs the new customer. Whether that is possible or not, I can not tell you. The answer is dependent on two things.
1. Whether a trigger (or the code) can even send an email
2. Whether the hosting company will support the functionality.

For this to happen using Access, you need to create a batch process that runs based on a timer event to look for new entries. The Access app would need to be open 24/7. Since this won't be running on a server, you probably also need a way to manage this and know that the batch process is running so the batch process should create log entries which you need to monitor regularly.

When I have to create batch processes such as this using Access, I run them from a special purpose db that only has the necessary functionality. The monitoring function is necessary. Somehow we managed to have one or two outages each month. Sometimes it was because someone sat at the desk with the "batch" computer and ignored the sign to not use it or we had a power outage that caused the PC to reboot and Access didn't always close cleanly so it didn't reopen cleanly.

 

[NauticalGent]

Kind of abstract, but have you thought about adding a trigger to the linked SQL table that would in turn make an entry to a linked (or local) Access table and then a Table Macro on THAT table send the email?

Edit...I see GPG has already mentioned that!

 

[jdraw]

Charlie8585,
Can you tell us a little more about your environment? DBMS; hosted or in-house;..
.
I see John has responded while I'm typing... I am a bit slow in responding.

I have not seen documentation that says Data Macros must be on Access databases. I don't know if a data macro can be on a table -- "Access linked to other DBMS". I have used data macros in all Access situation.

Has anybody tested to confirm the "limit" of Table data macro? Or seen documentation for same?

 

[Minty]

Depending on the flavour of SQL server being used, you can certainly use a SQL trigger to send an email from a DB Mail enabled SQL server.

This would be by far the simplest way, as it involves nothing external to the always-on SQL server.
Anything else would rely on an Access app running 24/7 for GPG suggestion. Or being fired up regularly via a scheduled task to check the table for changes.

 

[NauticalGent]

I have not seen documentation that says Data Macros must be on Access databases. I don't know if a data macro can be on a table -- "Access linked to other DBMS". I have used data macros in all Access situation.

I know they are not an option for linked SharePoint lists, I have not attempted them on SQL tables

 

[jdraw]

Hi John,
I did some searching but did not find any definitive info re Data Macro scope.
I assumed, and that is a long way from fact, that they were an "Access-only feature".
But given the OP's limited info, I figured that if the sql database involved could trigger an event based on the update/add of a customer subscription, that event could involve a linked table. And if data macros could apply to a linked table, then that could be a route.

It is unclear (to me) how his live Access tables are in communication with his online sql db.
I am not a SQL Server person, nor Sharepoint, but do recall (15+ years ago ) we had Oracle triggers and pass through queries.

I think minty's response is on target if the OP is using sql server. If not, then the "as yet unknown SQL db", may have similar functionality.

 

[MsAccessNL]

I believe that you can email directly from certain sql servers as reaction to a trigger.

Send e-mail from a trigger

I am trying to develop a mail trigger. Could someone assist on how this could be achieved so that when a use inserts a record it check the "speed" field such that when the inserted value exceeds 10...

stackoverflow.com

 

[MsAccessNL]

Kind of abstract, but have you thought about adding a trigger to the linked SQL table that would in turn make an entry to a linked (or local) Access table and then a Table Macro on THAT table send the email?

Edit...I see GPG has already mentioned that!

I experimented with the data macro, but it doesn't react to entries which are not made directly (by hand) into the table. Entries by code or on another way (linked table) did not trigger the table macro.

 

[jdraw]

Here is a thread with responses from CJ_London that has some info related to data macros.

I provided a demo audit log using data macros with Access FE/BE.

 

[GPGeorge]

I know they are not an option for linked SharePoint lists, I have not attempted them on SQL tables

Data Macros are the "local Access table little brother" to SQL Server table triggers. They DO fire when data is changed in the table.

We use them in Northwind, for example, to write entries to audit fields (CreatedBy, CreatedDate, UpdatedBy, and UpdatedDate) on some tables. We use them to call a VBA function to retrieve the User from Environ, so I know they can call VBA.

To MSAccessNL, I haven't seen that particular behavior. I'm not doubting your direct experience, but it is very different from what I've seen. Look at the DM's on Orders and Purchase Orders tables in Northwind Developer and tell me whether they do or do not behave the way you describe for your environment.

 

[NauticalGent]

To MSAccessNL, I haven't seen that particular behavior. I'm not doubting your direct experience, but it is very different from what I've seen. Look at the DM's on Orders and Purchase Orders tables in Northwind Developer and tell me whether they do or do not behave the way you describe for your environment.

 

[Charlie8585]

All.
Excellent, concise responses that answer my question. Thank you.

What is the DBMS of the online SQL database?
Not sure it matters, but perhaps you could have a trigger to invoke a "notification event" to initiate your "auto send process".
I agree with ebs17, would be good to have the "send confirmation" and the "add new entry" in same procedure.

By 'live' table I meant local table(s) linked to DBMS (MySQL, externally hosted). Of course auto-emails (subscription confirmations, order cancellations) will eventually be handled on that platform. However this won't be dialled in for 5-7 weeks (tediously) so was musing re whether can use Access locally to handle as interim measure in meantime.

Access (the Jet/ACE) engine has no way of knowing what happens in a SQL Server (or whatever) database connected to a web site. It might not even be open. as @ebs17 implied, only the database where the record was insterted knows that something happened. You would need a trigger in that database to send the email. Either an actual on insert trigger on a table or simply code in the procedure that logs the new customer. Whether that is possible or not, I can not tell you. The answer is dependent on two things.
1. Whether a trigger (or the code) can even send an email
2. Whether the hosting company will support the functionality.

For this to happen using Access, you need to create a batch process that runs based on a timer event to look for new entries. The Access app would need to be open 24/7. Since this won't be running on a server, you probably also need a way to manage this and know that the batch process is running so the batch process should create log entries which you need to monitor regularly.

When I have to create batch processes such as this using Access, I run them from a special purpose db that only has the necessary functionality. The monitoring function is necessary. Somehow we managed to have one or two outages each month. Sometimes it was because someone sat at the desk with the "batch" computer and ignored the sign to not use it or we had a power outage that caused the PC to reboot and Access didn't always close cleanly so it didn't reopen cleanly.

Pat - I tried @GPGeorge 's suggestion (data macros). And (for me) - the data macro functionality is greyed out in Access for the linked tables (which is logical). So only solution/workaround seems to be, as you suggest, batch process running on timer. Thank you for the suggestion re log entries - will implement. Will probably dig out an old thin client PC to use specifically for purpose.

 

[Pat Hartman]

The "trigger" or "data macro" is attached to the table in the database. It is executed by the database engine. So, SQL Server would execute any triggers attached to the tables it is managing and ACE would execute any data macros attached to the tables it is managing.

If the website is hosted locally, the SQL Server database that it uses would be "outside" the firewall and the ACE database would be "inside" the firewall. You are very unlikely to ever get the security staff to allow a process that runs "outside" the firewall to update a database that is "inside" the firewall. And it probably isn't possible at all if the website and associated database are hosted offsite given the security implications..

Code in the webpage or a trigger in the database that powers it would need to send email. But, again, you are at the mercy of the security folks. They will have the final say as to whether this can happen.

 

[GPGeorge]

All.
Excellent, concise responses that answer my question. Thank you.

By 'live' table I meant local table(s) linked to DBMS (MySQL, externally hosted). Of course auto-emails (subscription confirmations, order cancellations) will eventually be handled on that platform. However this won't be dialled in for 5-7 weeks (tediously) so was musing re whether can use Access locally to handle as interim measure in meantime.


Pat - I tried @GPGeorge 's suggestion (data macros). And (for me) - the data macro functionality is greyed out in Access for the linked tables (which is logical). So only solution/workaround seems to be, as you suggest, batch process running on timer. Thank you for the suggestion re log entries - will implement. Will probably dig out an old thin client PC to use specifically for purpose.

Perhaps I wasn't clear enough. Data Macros are attached to "local" Access tables. As you saw, you can't attach them to linked tables. Their "big brother", or SQL Server table triggers, work similarly on SQL Server tables, but are capable of much more complex logic.

That said, if you implement a plan such as the one Pat suggested, and use a timer to periodically update a local Access table from the linked SQL Server table, then a Data Macro on that local Access table WILL fire, and it can then do singing and dancing routines for you, culminating in an email back home.

 

[The_Doc_Man]

Based on the various opinions I've seen here, the method I think is the best is, if you are going to write a new record into a table to store the new subscription, at the same time build the e-mail and send it. Even if you can't guarantee that the connection has been made and the message has been uploaded, you can always include the idea of "Please allow x weeks for processing."

There is an old software engineering principle that says that the best place to build two related things is to build them in the same place at the same time while you still have all the data you need. This principle is a reflection of such sayings as "Don't put off until tomorrow what you can do today" and "No time like the present."

If you really CAN'T take this advice, then the next best method is to consider the process by which the connection gets made so that the central database "knows" it has new data. SOMETHING must happen to allow this type of update to be made, and waiting for a trigger is kind of passive, whereas making a connection is an active event.

The way you talk, this SOUNDS like you have a central server and people have laptops in the field that aren't always online to the central site. Otherwise you would have continuous connections and could trigger various events on that central server. The fact you would ask this question implies that you cannot. So either confirm my guess or explain what you really have as a high-level overview.

Another thought is that if you have Outlook but it is not connected, you can still create the mail and put it in your Outlook OUTBOX. It would then be sent the next time you CAN connect to an Exchange server. You could also put mail in your Drafts folder and empty that on next connection.