Automatic send e-mail on table field change (1 Viewer)

[LeoM]

Good morning everyone.
Actually I have a need to send an automatic e-mail to users whenever a field of a specific table is change.
The table is part of an external application and is linked to my access database trough ODBC connection and users may change that field trough that application.
I have the function to send e-mail but my problem is to understand how I can track the change of the field in that table.
Just to give some reference, I need to track the change of the field "RFSU_PLANNED_DATE" in the table "WH_REPORTING_V_RAMZ_PROJECT" and send an email to specific users telling the field "RFSU_PLANNED_DATE" is change for project "PROJECT_ID".
Hope is clear. looking forward to have some idea/solution.
Cheers.

 

[June7]

The only thing that might give you this capability is table DataMacro. Maybe BeforeChange. I never used DataMacros so not sure if it can determine exactly which field changed to trigger the macro.

 

[The_Doc_Man]

Data Macros, because they are not in the graphic/form/report side of Access interfaces, have no .OldValue that you could use to compare old and new values in a table. You could easily know that a record was changed in a given table. Finding which record changed and by how much is a far tougher question.

If the change occurs through a form, you do FAR better by triggering your e-mail from the form that made the change.

However, there is this to consider:

The table is part of an external application and is linked to my access database trough ODBC connection and users may change that field trough that application.

If Access doesn't "own" the table, you can't put a data macro on it. The owning application is the only one that can do it. From that statement, the app either IS or ACTS LIKE a database engine. Access cannot do the "proxy" event because it is not the engine performing the change.

 

[June7]

Ooops, I overlooked that "external application" line - what application? SQLServer has triggers (in Access the equivalent is DataMacro).

 

[LeoM]

Thanks first of all.
The external application is another database, not sure which one but most probably SQL.
So, am i wrong to say that I should find some work around ? or, if it is possible to use Data Macro, someone can be so kind to explain how to work with that ? I check some documents in the net but not so clear honestly.
Cheers

 

[June7]

As already pointed out, DataMacro only relevant for Access tables. Since your data is likely in SQLServer, DataMacro is not applicable. Google SQLServer triggers. Would need direct access to the external database.

 

[LeoM]

What if I decide (at the first logon for example) to create a local table by copy of the linked one and apply the DataMacro on that local table which is owned by Access in this case ? But, as I said, I need support on how it works DataMacro (how and what to do).
Cheers

 

[June7]

That won't do anything if the data edit takes place in the external table. I don't know if a Data Macro can be created with VBA. Never used them.

Google.

And editing in the local table won't change data in the external table unless you also run an UPDATE action.

 

[ebs17]

whenever a field of a specific table is change

An action such as detecting a change and sending an email can only be triggered by an event.

How would such an event occur?
- DataMacros in Access tables or triggers in SQL Server evaluate events at table level. The evaluation at table level is the most direct, fastest and most secure. But to do this you first have to know and master your backend. With “I believe” you have no meaningful options.
- If you know HOW a user changes this table value in the frontend, this is also an event like Form_BeforeUpdate in the bound form or an execute statement of an action query or a recordset statement via edit. Here you would have to cover all conceivable cases for changing the field and have an overview of what can happen.
You would have to include every conceivable front end for this test. In addition, table data could take place directly in the table via standard imports and manual interventions.
- You install a monitoring service that checks the table value at appropriate periods and compares it against a saved value. So this could be a form timer. If you cannot master the first two points, this variant is certainly the easiest and most practical for you.

Form timer would therefore be a suitable keyword for your further research.

 

[CJ_London]

What is wrong with using the form before update event?

try a test to ensure you have an oldvalue. If you do then use the before update event to compare oldvalue with the current value and if different, send your email

 

[June7]

Because OP said "and users may change that field trough that application".

 

[LeoM]

What is wrong with using the form before update event?

try a test to ensure you have an oldvalue. If you do then use the before update event to compare oldvalue with the current value and if different, send your email

The problem is that i don't have the form where the modification it may be done by the user so how I can control something i don't have in my db? Sorry maybe I don't have enough experience but as far i know i can control the event only if i can manage the form (so the event), if not please advise. Thanks.

 

[CJ_London]

my bad - my interpretation was 'that application' meant access.

Assuming the application designers cannot provide a suitable modification to meet your requirement, think the option of using a form timer as suggested by ebs is the only way you can go. Perhaps have it in a separate access application for that sole purpose. Perhaps the timer runs every minute/hour/day, depending on the urgency

You'll need a copy of the table in access or at least one that contains a PK to link on and the field(s) you want to monitor plus s linked table to the source and use an upsert query to update it on the timer event. Then use a data macro to send your email. Probably use the before change and after insert macros.

Alternatively without using data macros, you need 3 or 4 queries, one to identify which records have changed (using a left join) which you can use for your emails, a second one to then update the access table, a possible third to identify new records and send an email and a final one to append new records.

A small risk exists that between running the first and second queries, another change to the source data occurs.

Since this is a monitoring service, you may also need a means to delete records from the access table (or maybe better to flag them as 'complete') where a record no longer appears in the source data.

 

[LeoM]

My work around it was exactly partially described: the idea, assuming i'm not familiar with Data Macro (but i will read some documentation about), is :
- Assuming i want to do the control one time per day (at the first logon of the frontend, any user);
- Assuming i will create a daily local copy of the linked table;
- Assuming i will create a temporary table with one field "date of last verification", which is the date of comparison between linked and local table;

My process should be:
1. Check if he verification is already done (by checking last verification date which i update after the verification is done);
2. If the last verification date is less then today, it means i need to do the verification so with a query i compare the fields i needs to check. If the last verification date is not less then today, it means verification is done so it is not first logon and I don't need verification (exit from process).
3. If my verification gives me records, then i sent e-mail, if not exit from process.
4. On Exit from process i will :
a) Create the copy of the linked table updated to today to have fresh copy to be compare tomorrow.
b) update "verification date" in the new table

How it sound ? something wrong ? i know is a work around that require a couple of tables/query and set the process in the Autoexec Macro, but maybe it works. At the same time I'm really curious to know how Data Macro work because probably will facilitate the process.

Cheers

 

[gemma-the-husky]

If an external application changes data, surely it's up to the external application to audit the changes, and issue change reports.

You could run a process to store the data daily in your access database, and check for changes each day. I can't think how else you could do it.

 

[LeoM]

If an external application changes data, surely it's up to the external application to audit the changes, and issue change reports.

You could run a process to store the data daily in your access database, and check for changes each day. I can't think how else you could do it.

Yes, exactly the above process i described.
Cheers

 

[CJ_London]

- Assuming i will create a daily local copy of the linked table;
- Assuming i will create a temporary table with one field "date of last verification", which is the date of comparison between linked and local table;

Not sure I understand why you need either of these.

What is the benefit of having a local copy of the linked table, just use the linked table?

Why a temporary table? What if something changed from the last time you created the temporary table? you would have no record of the change. Would think this needs to be a permanent table showing the updated values. Particularly if you are going to use data macros since these would need to be recreated every time you create the table.

I use a similar technique for a client where they require email notifications to be sent to managers when they are required to post project reviews for specified projects. The below may be more than you need for your process, in my client's case they are often working remotely around the world and can have unstable connections.

So your process point 2 - I use a 2 field check which contains 2 date/time fields. The first is populated with now() as soon as the process starts and the second set to null. The second is populated with now() when the process finishes. The process typically takes 3-4 minutes, so the check is

if isnull( second field) and now()<first field+5 minutes then
    do nothing as someone else is running the process
elseif nz(second field,0)<date() then
    run email process
end if

This prevents multiple users from running the email process if they all log in within a few minutes of each other, but also protects against the first user starting the process and then aborting (powercut/whatever). After 5 minutes, if the second field is not populated, the next user to log in will trigger the process.

 

[LeoM]

Not sure I understand why you need either of these.

What is the benefit of having a local copy of the linked table, just use the linked table?

The benefit is to use it everyday to compare with the live table (linked) in order to verify the differences. This is what i though otherwise, how i can control the modification in the same "live" linked table ? Because it is not clear to me in your explanation. Where i should add that code ?

Why a temporary table? What if something changed from the last time you created the temporary table? you would have no record of the change. Would think this needs to be a permanent table showing the updated values. Particularly if you are going to use data macros since these would need to be recreated every time you create the table.

I use a similar technique for a client where they require email notifications to be sent to managers when they are required to post project reviews for specified projects. The below may be more than you need for your process, in my client's case they are often working remotely around the world and can have unstable connections.

Actually i have almost the same problem, they want to track if any Project Start Date change.

So your process point 2 - I use a 2 field check which contains 2 date/time fields. The first is populated with now() as soon as the process starts and the second set to null. The second is populated with now() when the process finishes. The process typically takes 3-4 minutes, so the check is

if isnull( second field) and now()<first field+5 minutes then
    do nothing as someone else is running the process
elseif nz(second field,0)<date() then
    run email process
end if

How to use ?

This prevents multiple users from running the email process if they all log in within a few minutes of each other, but also protects against the first user starting the process and then aborting (powercut/whatever). After 5 minutes, if the second field is not populated, the next user to log in will trigger the process.

Thanks, appreciate it.

 

[ebs17]

they want to track if any Project Start Date change

A simple step to start with would be to include creation and update timestamp fields in the table.
In this way you can easily determine those records where there have been changes since the last time you looked at them (which you remembered).

 

[LeoM]

A simple step to start with would be to include creation and update timestamp fields in the table.
In this way you can easily determine those records where there have been changes since the last time you looked at them (which you remembered).

As I said, this table is not under my control, is linked to my database trough ODBC hence, unless I do local copy, I cannot add any field.