Access 2013 web apps are great for collaborating around a common set of data. When people work together in this way, the changes that one person makes to the data often require the attention of someone else.
For example, if a small team is working together to track and process maintenance requests, an Access app would be an ideal place to centrally track those requests and their status. Further, imagine that I process a maintenance request and assign it to another team member who needs to take action on it. But how does he or she know? Wouldn’t it be great to be automatically informed? That’s what notifications can do.
In this article, we’ll describe a technique to build e-mail notifications into your Access 2013 web app using the power of SQL Server and a third-party service called Zapier.
How it works
In the above example of our maintenance request tracker, people need to get an e-mail when a maintenance request is assigned to them, letting them know of a required action. In the Maintenance Requests table, there’s an Assigned To field that is a lookup to the Employees table. Whenever that field changes, an e-mail should be sent to the person to whom the Request is now assigned.
Building this notification functionality involves three steps, detailed further below:
- Create a Messages table in the Access web app to store notification emails that you want to send.
- Create a data macro on the Maintenance Requests table that adds records to the Messages table whenever the Assigned To field changes.
- Configure Zapier to automatically monitor the Messages table and send an email whenever it detects a new record.
By the end, you’ll have something that looks like this:
Step 1: Create a “Messages” table
First, you’ll need to create a Messages table to store information about the email notifications that you want to send. This table will need to have fields that specify all the details about the email message, including:
Click on Create > Table in the ribbon, then select the add a new blank table link. Add fields in the table designer so that it looks like this:
Step 2: Create a data macro on the Maintenance Requests table
You want messages to be sent whenever the Assigned To field changes in a record on the Maintenance Requests table. Data Macros are the perfect tool for the job. Data macros are commands that run whenever records on a table change. For a given table, you can write macros that run when records are added, deleted, or updated. In our case, you’ll want to write an On Update macro on the Maintenance Requests table.
To do this, first, open the Maintenance Requests table in the table designer, go to the Design tab, and click Events > On Update.
You’ll only want to send the notification if the Assigned To field has changed. To do this, use an If statement that compares the old value of this field with its new value:
If that condition is true, you’ll want the macro to create a record in the Messages table. Since you’ll want this new Messages record to have the users’ e-mail address in the To field, you need to first look that up and store it in a variable:
Finally, use this email address to create the message:
Then, this macro will add a new record to the Messages table whenever the Assigned To field of a record in the Maintenance Requests table changes.
Since, in this example, you also want people to get notifications when a new Maintenance Request is created and assigned to them, you’ll create an After Insert macro that looks like this:
The only difference here is that we’ve changed the If expression at the beginning to run the macro if the Assigned To field is not blank.
Step 3: Configure Zapier to monitor the Messages table and send messages
Zapier is a third-party tool that you can use to build simple connections between one service, like an Access 2013 web app, and another, like email. Zapier calls these connections “Zaps.” To get started building our zap, go to zapier.com and sign up for a free trial account, and click Create New Zap
You’ll then find the services that you’d like to connect. In our case, we’ll choose SQL Server > New Row as the “trigger” > Gmail > Send Email as the “action.”
Click Create Zap and you’ll be prompted to enter some more information about these connections. Start with the SQL account. You’ll need to tell Zapier how to get permissions to look at the SQL database that powers your Access 2013 web app. Zapier will want to know things like server name, database name, username and password. To find out these things for your database, open it in Access, click File > Info > Manage Connections.
From this menu, you’ll want to click From Any Location to disable firewall rules that would otherwise prevent a third-party service like Zapier from connecting. Then, click Enable Read-Only Connection which will generate a username and password that Zapier can use. To view these details, click View Read-Only Connection Information, which will show you something that looks like this:
Back in Zapier, you can copy this information into the Zap. Be sure to use port 1433 to connect:
Next, you’ll want to tell Zapier which table it should monitor for changes and how it can tell which rows are new. We’ll pick the Messages table, and tell it to examine the ID field. Since the ID field is a number that is automatically increased by one as new rows are added, this will be a reliable way for Zapier to tell the new rows from the old ones.
Next, you’ll want to enter the credentials to your Gmail account so that Zapier can send the emails. Gmail is just used as a means of sending-the notifications can be sent to any inbox, such as Outlook, Yahoo, etc.
After your Gmail account is configured, you have to tell Zapier how to map the fields in the Messages table in SQL to the fields that Gmail expects. This part is pretty straightforward: the To field in Gmail maps to the To field in SQL, and so on:
That’s it! You can easily test out your Zap by choosing “Click to load samples!” This will load the first row that Zapier finds in the Message table, and you can click to send a sample message.
The final step is to make your Zap live. This means that Zapier will run it every 15 minutes to check for new records in the Messages table, sending emails if it finds any.
You can try Zapier out for free for 15 days. Since SQL connections are a premium service for Zapier, you can sign up for a paid plan when the trial expires. See www.zapier for pricing details.
This article has outlined how to use notifications for a specific example, but there are many other situations where this capability will be useful. However, regardless of the scenario, you can follow the three steps above. We can’t wait to hear about the cool things you’re able to do with this technique!