Back
Access

Streamline collaboration across teams: Add notifications to your Access 2013 web apps

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:

  1. Create a Messages table in the Access web app to store notification emails that you want to send.
  2. Create a data macro on the Maintenance Requests table that adds records to the Messages table whenever the Assigned To field changes.
  3. 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:

  • Subject
  • Body
  • To
  • Cc
  • Bcc

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.

Conclusion

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!

 

 

 

Notice: Array to string conversion in D:\home\site\wwwroot\wp-includes\taxonomy.php on line 3255 Notice: Array to string conversion in D:\home\site\wwwroot\wp-includes\category-template.php on line 1245

Tags

Join the conversation

7 comments
  1. @Jeff – It sounds like you architected a solution were there was an Access desktop database that used VBA to call another program, such as outlook, that sent the email? (feel free to correct me if that’s not right).

    You can continue to do something similar with an Access 2013 web app. You’d need to have a separate Access Desktop database that connects to the SQL server tables that store the data for the web app. Then, you can use the same VBA code to send the email. The resulting solution would have two components–a web interface that you could use for some functionality, and a desktop database that runs behind the scenes and sends the email.

    • Hi Andrew.
      Is this not possible with macros and the SendEmail function? I have used that extensively in my 2010 web database.
      Roger

  2. using microsoft cdo for windows 2000,can do same thing. but not trying in web application.

  3. Roger,
    I’ve been unable to find SendEMail in Access 2013 web databases. It looks like that functionality has been taken away.

    Anyone,
    On an earlier blog post I asked if it is possible to handle Rich Text in Access 2013 web databases, but I’ve not seen any replies. It would be good if there is a way.

    1st new question
    How do you base a view (aka form) on a parameter query, please?

    2nd new question
    Can anyone please tell me which Office365 plans enable the use of Access 2013 web databases? Access 2013 web databases are excellent and the Access Team has done a superb job with it, but are being let down by the appallingly complicated Office 365 marketing. I currently have an old (2010) Office 365 subscription which will be upgraded to Office 365 (same name, folks) sometime in 2013. So I don’t have any idea of when I can start to use Access 2013 on that subscription and thus am looking to transferring to a new subscription. Can I use E1 or so I stick with P2? If I subscribe now to a new P2 will that be Office 365 with Access 2013 web databases or will I be still stuck with Access 2010 web databases only?

  4. "We’ll pick the Messages table, and tell it to examine the ID field."
    How do you tell Zapier to do that?

  5. I know this is totally the wrong place to post this, but Office 365 in the UK are taking no notice. My Office 365 Preview Access 2013 web databases no longer work and neither do they in Office 365 live. It has been like this for 24 hours now. I thought Office 365 was meant to have 99.9% uptime or something. So far I’ve had 0% uptime since starting to "use" the live version yesterday.

  6. Looks like my problem has been sorted out with Office 365 Preview. Thanks to those involved.

Comments are closed.