MS Access Date triggers emulation

Question

I am completely new to Access (have some experience with SQL server).

I am not sure if its even possible in MS Access 2010: if for one of table.dateField is today or earlier - need to send email about that record.

Extensive digging doesn't bring any results, seems its not feasible, but wanted group opinion on that. Thank you

Answer 1

If you are meaning that you need to execute say a query on a table, and that query will manage the execution of sending an email in respect of each offending row - then I dont think you'll have much luck;

You can open an email window from access, but this won't send an email by itself.

What are you using for your backend? If it's flexible, and you used SQL server for the backend then you could use an actual trigger which calls a stored procedure to send the email required.

Aside from this, all I can think of to do, would be

• create a query which finds the rows for which an email is to be sent;
• create a .net executable which receives a set of information as parameters & sends the email you require on the basis of that data,
• on a form in your application, use a timer event to periodically execute the logic to open the query, using like an adodb.recordset and loop through the recordset and call the .net executable for each row returned by the query.

Hope this helps

Answer 2

The Microsoft Access application does not have any built-in feature to automatically scan the database and send email messages based on the status of records in a table. However, that does not mean that such a function is "not feasible" for information stored in an Access database. In fact, implementing such a feature could be as simple as this:

The following VBScript code could be run (via cscript.exe) every morning as a Scheduled Task under Windows. It will scan a table named [Patients] and send a "Happy Birthday" message to each person who has a birthday that day.

Option Explicit

Dim con  ' As ADODB.Connection
Dim rst  ' As ADODB.Recordset
Dim msg  ' As CDO.Message

Set con = CreateObject("ADODB.Connection")  ' New ADODB.Connection
con.Open _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\Users\Gord\Desktop\Database1.accdb;"
Set rst = CreateObject("ADODB.Recordset")  ' New ADODB.Recordset
rst.Open _
        "SELECT FirstName, Email " & _
        "FROM Patients " & _
        "WHERE Month(DOB) = Month(Date()) " & _
            "AND Day(DOB) = Day(Date())", _
        con
Do Until rst.EOF
    Set msg = CreateObject("CDO.Message")  ' New CDO.Message
    msg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    msg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.example.com"
    msg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    msg.Configuration.Fields.Update

    msg.To = rst("Email").Value
    msg.From = "[e-mail]"
    msg.Subject = "Happy Birthday!"
    msg.TextBody = "Hi, " & rst("FirstName").Value & ". We hope you have a great birthday today!"
    msg.Send

    Set msg = Nothing
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing