e-mail on changes

[DonP]

We have an Access database that is being updated online via a login and ASP forms. Is it possible to make it send e-mail, or write to a file, when certain fields have been updated? It would just need to tell us which record ID has had the change, and possibly the field name. It is not necessary for us to know when most fields have had updates, only certain ones. Being notified by e-mail would be best, but we can also periodically check a special log file for the porpose too, if that's easier. I am not sure even how to begin, so if anyone has ideas, I would be happy to try them!

Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[wushutwist]

An answer to this question is a bit difficult without knowing the application but I will try. First off, if you are constantly running into this and other problems I would suggest moving the db to a more robust rdbms like Oracle or SQL Server. Since you are using Access I will assume Oracle is way out of budget, therefore that leaves SQL Server. I "real" rdbms has object known as triggers that can do just what you are asking for. Okay enough of that.

If you must stick with Access then here is what you have to do: Firstoff, you have to have some logic, either in the ASP pages or in COM objects, that determines if a record that is being updated is in fact being changed. If only certain fields are important than only check those fields.

The would do this is the following: Create a server side function that returns a boolean value and accepts as parameters the PK of record and the values of all fields that need to be checked. The function should then query the Access DB for the record and compare all of the necessary field parameters against the returned recordset for changes. The function should then return True if there were changes and False otherwise. Then have a if statement that calls the function with the correct parameters as its condition clause. If the if statement is true use an object either CDONTS or ASPMAILER to pop off an email with that record's PK.

I hope that helps, sorry for the long post.

Wushutwist


Sun Certified Java 2 Programmer

 

[DonP]

We plan on going to SQL eventually but we are a small non-profit with limited budget and people power. Also, the database is not really that large yet.

Thanks for your ideas! What I had in mind was something in the existing database update ASP scripts, which are forms, rather than adding more ASP scripts. Is there a way to detect if fields in the submit form have been edited, and which ones when the form is submitted? Querying the database itself for changes is probably an overkill for our needs.

Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[wushutwist]

To check for changes to a blank form is easy, just see if the field is equal to "".

To check for differences of a pre-populated form (as in editing) is a little more complex but it is always pretty easy. Submit both the original value used to populate the field and the new field value. If both are equal then there has not been any changes.

Wushutwist

 

[DonP]

When the editing form loads into the browser, it automatically pulls in the data from the database and presents it. Some fields often do not have data but there are a couple that always do, and it is some of those that I need to know if they've been changed. Generally, the fields I am interested in are never blank to begin with. Since the data is there on the client side in the browser at that point, how can I compare when there has been a client change when it is submitted? I don't care so much what the change is, just that there's been one to a particular field(s).

Along those same lines, there is another form for creating a new record. I just realized that I also need to use the same facility to know when a record has been added, and which one it is, without much care as to the changes in the fields themselves. If the record is new, we would want to review the whole thing and don't really care about individual fields.

I'm not a programmer so need some details, if it is possible with this sparce information. The forms are far too large to post here.

Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[wushutwist]

How are is the data "automatically" pulled in from the database? In addition to placing those values in the form field, store them in hidden fields. Therefore when you submit, you will have both the original and the new values. Then you can check for differences.
Wushutwist