Member Avatar for laura301019

I have an application that uses microsoft access as the database. The application is to allow users to book appointments online. I would like to include an email reminder service so that an email is sent to the user 24 hours or so before their appointment. Not sure how I can implement this so any ideas would be great?

Thanks in advance

  • 3 Contributors
  • 2 Replies
  • 627 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by geniusvishal

Dani AI

Generated

A compact, reliable pattern that complements and 's suggestions is: make the reminder selection idempotent, claim rows atomically, send mail, then record the result. That minimizes duplicate sends and avoids long-lived locks on the Access file. The following focuses on practical changes that work with an .mdb/.accdb backend without reworking hosting.

Add small columns: a numeric ReminderStatus (0 = pending, 1 = processing, 2 = sent, 3 = failed), ReminderSentDate (nullable), and ReminderAttempts (int). Select candidates with Access SQL like:

SELECT ID, Email, AppointmentDate
FROM Appointments
WHERE AppointmentDate BETWEEN Now() AND DateAdd('h',24,Now())
  AND ReminderStatus = 0;

Example C# worker pattern (use as a console app / scheduled job). It claims each row with an atomic UPDATE, sends mail only when the claim succeeds, then updates status and logs attempts:

using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Net;
using System.Net.Mail;

// connection string -> correct provider for .mdb or .accdb
string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\to\db.accdb;";
using (var conn = new OleDbConnection(connStr))
{
    conn.Open();
    var select = new OleDbCommand("SELECT ID, Email, AppointmentDate FROM Appointments WHERE AppointmentDate BETWEEN Now() AND DateAdd('h',24,Now()) AND ReminderStatus=0", conn);
    var list = new List<(int id,string to,DateTime appt)>();
    using (var r = select.ExecuteReader())
        while (r.Read()) list.Add((r.GetInt32(0), r.GetString(1), r.GetDateTime(2)));

    foreach (var e in list)
    {
        var claim = new OleDbCommand("UPDATE Appointments SET ReminderStatus=1 WHERE ID=? AND ReminderStatus=0", conn);
        claim.Parameters.AddWithValue("?", e.id);
        if (claim.ExecuteNonQuery() == 0) continue; // someone else claimed it

        var msg = new MailMessage("noreply@example.com", e.to) { Subject = "Appointment reminder", Body = $"Reminder for {e.appt:g}" };
        using (var smtp = new SmtpClient("smtp.example.com")) { smtp.Credentials = new NetworkCredential("user","pass"); smtp.EnableSsl = true; smtp.Send(msg); }

        var done = new OleDbCommand("UPDATE Appointments SET ReminderStatus=2, ReminderSentDate=Now(), ReminderAttempts=Nz(ReminderAttempts,0)+1 WHERE ID=?", conn);
        done.Parameters.AddWithValue("?", e.id); done.ExecuteNonQuery();
    }
}

Key tips: run the worker with an account that can open the Access file, keep DB connections short, add a ReminderLog table for failures and retries, test with near-future appointments, and handle timezones consistently (store UTC or document local zone). For deliverability, use authenticated SMTP and proper SPF/DKIM; for higher volume or concurrency needs, migrate to a server-grade DB and use a queue.

Recommended Answers

All 2 Replies

Member Avatar for JorgeM

Well, you would have to run some type of task every 24 hours.

There are a variety of ways to do this...

You can setup a page that handles this service and use a cron job to call that page once every xx minutes and check the database for an upcoming event in the next 24 hours and if it finds a match, send the email.

You can setup a vb.net, c# console application to run on the computer on a schedule (via scheduled tasks) that performs a lookup in the DB and if a match is found, send the email.

You can write a program (console app) that runs all of the time as a service and just executes a method on a scheduled basis so you manage the routine within this program, not via an external scheduler.

You mentioned that your database is Access, but if it was an Enterprise version of MSSQL, you can schedule jobs to SQL procedures to search and if a match is found, send an email from SQL itself.

etc.., etc..

the key here is that you need something to happen repetitavely, check for the condition, if there is a match, perform the desired action.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.