sql - 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

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:UsersGordDesktopDatabase1.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 = "[email protected]"
    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