You are here

MS Access Blog

Subscribe to MS Access Blog feed
Updated: 25 min 33 sec ago

Calling all Excel pros

Mon, 03/24/2014 - 08:00

We invite you to join analysts from around the world to connect, share experiences, and learn more about the power of data to transform business. The only pre-requisite? A passion for data.

Join us in the heart of Silicon Valley May 7-9 for real-world insights into analytics strategies from such leading companies as Yahoo!, SurveyMonkey, Wells Fargo, and of course Microsoft! You’ll walk away with an understanding of the latest in analytics best practices, advances, and strategic directions. Check out the sessions we have on tap for you.

This gathering – created by and for the analytics community – also packs 20 hours of networking into the intense learning agenda so that you can connect with fellow Excel and other data professionals.

Looking for fresh perspectives on analytics to grow your knowledge and career?  Be inspired by renowned data visualization expert David McCandless and kick-off keynote speakers from Microsoft, showcasing powerful ways to work with data in Excel and the new Power BI for Office 365 offering. You can also access sneak peeks of the conference sessions – check them out today.

Find out more and register today – use discount code EXCELPRO at registration and save an additional $300.

www.passbaconference.com

Join Access sessions at the SharePoint Conference 2014 in March

Mon, 02/24/2014 - 11:30

Join us in Las Vegas March 3-6 for SharePoint Conference 2014 (SPC14), including sessions on Microsoft Access. Product team members will show you Access web app features available in Access 2013, Office 365, and SharePoint 2013.

Sign up for the Access Services sessions at SPC14!

If you’re attending SPC14, be sure to sign up for the following Access Services sessions:

SPC204Anyone can build a SharePoint App with Microsoft Access
Speakers: Jeff Conrad and Chris Usher

Come to this session to learn how power users can easily and quickly build SharePoint apps with a full SQL back end without writing any code. You’ll learn how to create rich UI SharePoint apps using Access with low to no involvement or impact from IT.

SPC338The ‘how to’ guide for selling and managing SharePoint Apps built using Access
Speakers: Bob Piper and Kevin Bell

SharePoint applications created by Access are real SharePoint apps that you now can put in the Office Store. These no-code data centric web apps are part of Office 365. To make the publishing of these apps complete, Access has added features to help you deploy upgrades to existing apps. So you can deploy new versions of an app in production without losing their data. Come see how it all works.

SPC335Rich extensions to SharePoint Apps using Microsoft Access
Speakers: Gary Devendorf and Arjun Raja

“Apps for Office” is a new technology for extending Office applications. You may have seen exciting Apps for Office in Excel, Word and Outlook. Well, the newest version of this technology is coming to SharePoint apps created with Access. Come see some cool Apps for Office (like Maps and Charts) that extend the value of Access web apps. We will show how to build, test, and deploy your own Apps for Office as well as how to use those in the Office Store.

In addition to the above three Access-specific sessions, you might also consider attending this session:

SPC348Update on InfoPath and SharePoint Forms
Speakers: Greg Lindhorst and Sonya Koptyev

Why should you attend the SharePoint Conference?

In addition to learning more about Microsoft Access 2013 and Access Services 2013 at the SharePoint Conference events, you can find out about the future of Microsoft products, solutions and services directly from the leaders, complete with news, announcements and demos. Unlike traditional classroom training with a fixed set of topics, the SharePoint Conference allows you to personalize your learning experience. Mix and match the sessions and session types you want to attend, including breakout sessions, hands-on labs, keynotes, and more. Each of the four days provides you the opportunity to completely immerse yourself in sessions that offer insight into the products you are working with.

Stay ahead

The food, the pools, the nightlife, and the mini-monuments are all tempting reasons to sign up. The real reason to attend is to stay ahead of the curve. SPC14 gives you the latest and greatest in SharePoint innovation, industry technologies, best practices, and unparalleled access to Microsoft and industry experts. The knowledge, connections, and opportunities that happen at SPC14 won’t stay in Vegas. Discover new ways to connect, to reimagine, and transform. Discover the new way to work together.

Personalized for you

SPC14 has dedicated tracks for Developers, IT Professionals, and Executives. The speakers, sessions and labs you know and love are back and better than ever. Broader themes focus on migration, infrastructure solutions, business insights, social collaboration, and much more. Learn how you can get more out of SharePoint and Yammer.

More than content

SPC14 is more than just presentations. Join the community and step into the fast-track with those who are changing the paradigms of how people and information interact within the changing world. Meet tomorrow’s movers and shakers. Create lasting friendships and partnerships as powerful as the experiences you’ll have.

SPC14 will be the largest and most comprehensive SharePoint and Yammer event on the planet. Are you going?

Join us in person

In today’s online and always-connected world, there is tremendous benefit in having dedicated time away from your office to focus on advancing your expertise, minus the distractions. By attending the SharePoint Conference in-person, you are giving yourself the best chance to learn, connect and explore and take advantage of everything the event has to offer.

We hope to see you there! Register now at http://www.sharepointconference.com/.

Register now for April 16 Access Day in Irvine, CA

Thu, 02/13/2014 - 10:00

Are you a developer or user of Microsoft Access, or know someone who is?  Interested in the new web capabilities in Access 2013 or how to improve Access client applications?

After a successful sold-out event in the fall, J Street Technology is organizing another one-day conference on Microsoft Access.  Join us Wednesday, April 16 in Irvine, CA  for the next Access Day featuring internationally-respected presenters, including Access MVPs, book authors, and even a member of the Access development team at Microsoft.

Access Day will include sessions by Kevin Bell, Tom van Stiphout, Alison BalterGeorge Young, and Armen Stein and cover a mix of Access client techniques and new Access Web app development.  The intended audience is Access developers and advanced users who want to learn ways to make their applications more powerful and develop Access solutions for the web.

Here’s what people said about the last Access Day event:

Great information, great networking and well worth your time!  –Andy T.

Access Day has provided a welcoming environment for professionals to meet and get acquainted with peers… –Craig R.

The presentations focused on real-world solutions and scenarios and reminded me how much I like Access.  –Luis C.

I really enjoyed the great mix of topics and the enthusiasm of each presenter was excellent.  –Craig A.

Register by Feb 21 for a $30 early bird discount at www.AccessDay.org.

Find all your data connections with Office Discovery and Risk Assessment

Tue, 02/11/2014 - 05:00

This post is brought to you by Steve Kraynak, Program Manager for Excel spreadsheet management features. Special thanks to SQL MVP Scott Stauffer for contributing to this article.

Who’s using my data?

Do you need to find out where your data is being used?  More specifically, how can you find Excel workbooks that have data connections to your databases?

We went to SQL MVP Scott Stauffer to help answer that question.  Scott is helping his client launch a data warehouse that will be the preferred source of data for many areas of the business.  The client is also planning to switch to a new Enterprise Resource Planning (ERP) system, which means that legacy systems will eventually be turned off.  A new data warehouse is great, because it can provide a smooth transition from a legacy to a new ERP system, but people have lots of Excel files with connections to the legacy systems.  Scott and his team need a way to find these spreadsheets and their owners to communicate information about the new data warehouse.

The Discovery and Risk Assessment tool

In Office 2013, Microsoft released a tool called Discovery and Risk Assessment (DRA) which can answer these questions.  DRA gives organizations the ability to create an inventory of their Excel and Access files and then analyze them so they can determine which ones are the most complex, impactful, and risky.  DRA does this by crawling specified network paths and SharePoint sites to find the Excel and Access files, and then it analyzes and gives each file a rating for complexity, materiality (impact to the organization), and risk based on configurable criteria.

What’s interesting to the owner of a database or data warehouse is that Discovery and Risk Assessment can tell you about all the data connections found in the Excel files. We’ll show how you how to use DRA to get the list of all the files with data connections and see the Last Saved By property of each file.  With this list, you’ll be able to search the data connections to find which workbooks are connecting to important data sources.

How Discovery and Risk Assessment helps your business

By using Discovery and Risk Assessment to find out who owns the spreadsheets with connections to the old data sources, Scott helps his client to realize the following direct benefits:

  1. Determine how much effort will be required to update important spreadsheets to the new data warehouse.
  2. Proactively encourage and help spreadsheet owners to switch their data connections from legacy systems to the new data warehouse.
  3. Avoid disruption of switching to the new ERP system.
  4. Maximize the return on investment in the new data warehouse.
  5. Achieve one of their main goals, which is to eliminate ODBC connections to the old system prior to cutover.

In addition to these benefits, Scott’s team will be able to improve the quality of spreadsheet usage throughout the company, which is the prime reason that Office released DRA and several other tools in Office.  They will also:

  1. Find out how the spreadsheets are linked to each other.
  2. Determine whether data is up to date among linked spreadsheets.
  3. Identify users who could benefit from additional training in Excel.
  4. Minimize duplication of effort by getting an inventory of all the workbooks in the organization.
  5. Start to introduce policies and best practices to improve the overall quality of Excel usage.

For more information about Discovery and Risk Assessment (DRA), as well as the other spreadsheet management features available in Office and SharePoint, please see our announcement, the overview of the tools, and a spreadsheet management white paper on TechNet.

How to use DRA in your own business

To use Discovery and Risk Assessment in your own business, first set up DRA through the Volume Licensing Service Center for volume customers, then follow the TechNet instructions for installing and configuring it. When you configure DRA, you’ll run a discovery, which means you’ll have a database containing a list of all the Excel files DRA discovered, as well as a wealth of information about each spreadsheet, plus the list of data connections for each file.

To save you the effort of trying to figure out the database schema, follow these steps to bring the list of workbooks and their data connections into Excel.  This example uses Microsoft Power Query for Excel, but you can still use the traditional ways of getting external data into Excel. (Note: It is recommended that you do not perform the following steps on a live production database, since it could impact performance. It is recommended that you use an offline copy of the database.)

1. Go to the Power Query tab, then follow these steps:

a. Choose From Database -> From SQL Server Database.

b. Enter the server name of your SQL Server, including the instance name, if applicable.

c. Enter the database name of the database that DRA is connected to.  You can find this in the Configuration settings in DRA.

d. Expand the SQL Statement box and copy and paste the query below into it.

Here’s a detail view of the query in the screenshot.

SELECT
DA.AccountName,
FP.Value AS FilePath,
FN.Value AS FileName,
MD.Value AS Modified,
S4.Value AS LastModifiedBy,
N1.Value AS ConnectionCount,
T1.Value AS DataConnections

FROM
DiscoveryAccounts AS DA
INNER JOIN UDKDefinition AS UDK1 ON DA.AccountID = UDK1.AccountID
INNER JOIN UDKDefinition AS UDK2 ON DA.AccountID = UDK2.AccountID
INNER JOIN UDKDefinition AS UDK3 ON DA.AccountID = UDK3.AccountID
INNER JOIN UDKDefinition AS UDK4 ON DA.AccountID = UDK4.AccountID
INNER JOIN UDKDefinition AS UDK5 ON DA.AccountID = UDK5.AccountID
INNER JOIN UDKDefinition AS UDK6 ON DA.AccountID = UDK6.AccountID
INNER JOIN UDKDefinition AS UDK7 ON DA.AccountID = UDK7.AccountID
INNER JOIN Strings AS FP ON UDK1.UDKID = FP.UDKID
INNER JOIN Strings AS FN ON UDK2.UDKID = FN.UDKID AND FN.InventoryVersionID = FP.InventoryVersionID
INNER JOIN Dates AS MD ON UDK3.UDKID = MD.UDKID AND FN.InventoryVersionID = MD.InventoryVersionID
INNER JOIN Strings AS S4 ON UDK4.UDKID = S4.UDKID AND FN.InventoryVersionID = S4.InventoryVersionID
INNER JOIN Numbers AS N1 ON UDK6.UDKID = N1.UDKID AND FN.InventoryVersionID = N1.InventoryVersionID
INNER JOIN Text AS T1 ON UDK7.UDKID = T1.UDKID AND FN.InventoryVersionID = T1.InventoryVersionID

WHERE
UDK1.NAME LIKE ‘FilePath’
AND UDK2.NAME LIKE ‘FileName’
AND UDK3.NAME LIKE ‘Modified’
AND UDK4.NAME LIKE ‘LastModifiedBy’
AND UDK6.NAME LIKE ‘DataConnectionsCount’
AND UDK7.NAME LIKE ‘DataConnections’
AND N1.VALUE > 0

ORDER BY
FP.Value, FN.Value

2. Depending on the size of your database, in a few minutes the Query Editor will appear and you can simply click Apply and Close to send the data to a worksheet.

3. Now you have the data you need in Excel.  You have the file path, file name, modified date, Last Modified By property from the file, and for each file you have a text string that contains all the data connections for the file.

4. You can filter to suit your needs, but for example, if you need to find all workbooks that have a connection to your legacy ERP system, filter the Data Connections column by rows containing “MyERPSystem” (substitute the real name of your ERP system).

5. Now you have a list of all the files that have a data connection to your ERP system, and you can see who last modified them, so you know who to contact about switching to the new data warehouse.

Tell us what you think

Let us know in the comments how you use DRA in your own organization.

–Steve Kraynak, Program Manager, Excel spreadsheet management features

Access apps General Availability for Office 365 customers

Mon, 02/03/2014 - 09:37

Matt Evans is a writer on the Access team.

If you test out Access apps, but are waiting to use them in a production environment, you can now move full speed ahead.

What does general availability mean?  Customers can now create, share, and use Access apps with full confidence and the same level of guaranteed availability as the rest of Office 365.   Access apps have been part of Office 365 subscriptions since Office 365 General Availability was announced in February 2013.  Until now, Access apps were in preview and weren’t supported under the Office 365 service-level agreement (SLA) and compliance standards.

General availability brings several benefits:

  • Access apps are covered by the standard Office 365 SLA and compliance level.
  • Access app creators can now sell their apps in the SharePoint Store.
  • Access apps are available to all users, including Office 365 subscriptions where the administrator has disabled preview features since Access apps are no longer in preview.

For information about getting started with Access apps, see Manage data in Access apps in the Office 365 Business Learning Center. Also check out Access apps for developers for information about creating Access apps for SharePoint.

Frequently Asked Questions:

Q: Which Office 365 customers benefit from general availability of Access apps?

A:  Access apps are now generally available for Office 365 Small Business, Midsize, Enterprise, and Education customers. Office 365 Government customers are still in preview.

Q:  What are Access apps compared to SharePoint apps?

A: SharePoint apps, officially known as Apps for SharePoint, are self-contained pieces of functionality that extend the capabilities of a SharePoint website. Users find and download these apps from the SharePoint Store or from their organization’s private App Catalog and install them on their SharePoint sites. Access apps are web database apps designed in Access and hosted in SharePoint.

– Matt Evans

What are the top 3 updates we should make in Access web apps?

Fri, 01/24/2014 - 11:31

What are the top 3 updates you’d like to see in Access web apps? We have our own list, but we’d love to see yours–we rely on your feedback to continually improve Access. Complete our short survey or tell us your top 3 wish list items for Access web apps in the comments below. 

We have grand plans for the next full release of Access, but in the meantime, we are constantly building and shipping improvements to Access 2013 web apps. Have you tried Cascading Controls yet?

Introducing a new user experience feature in Access web apps: Cascading Controls

Fri, 01/24/2014 - 11:31

You asked, we listened! It’s just the nature of shipping software in a “services world” with Office 365: When we hear your feedback, we can respond with new features that fill your needs. In the case of Access 2013, we heard loud and clear from our first Access 2013 web app users that we needed to implement Cascading Controls as a part of our custom web app feature set.

It used to be a 3-year wait for new features in Office. However, because we are increasing the speed of our ship cycles, we are proud to announce that as of now, Cascading Controls are available to all of our Access 2013 Office 365 customers! The changes are on the server side which means you do not need to update your Access client program at all. This update is available immediately for customers using Office 365 with Access Services so look for this feature within your web apps and try it out.

How do Cascading Controls work?

It’s a common scenario. The app developer wants to make the options in a control, such as a dropdown, relevant to the option chosen from a different control. For example, you might want to associate company names with people names in a business contact database, or maybe you’d like to connect the make, model, and trim level for cars in a product database. These scenarios are now possible in Access 2013 custom web apps for SharePoint for Office 365 users.

Consider the following scenario of an app tracking a consultant’s projects. When entering a new project, you want to choose a company for whom the project will be completed and the contact for that project at that company. If you have lots of contacts at different companies in the app, you might want to see only a list of the contacts at that specific company. To enable this scenario, use a List Details or Blank view that has more than one combo box or autocomplete control. In the following screenshot, you can see a sample List Details view of a Projects table. 

In this case, the Company autocomplete control will be the parent control and the child control will be the Contacts combo box. Note that the parent control can be an autocomplete or a combo box control, but the child control must be a combo box. For the child control, click the Data callout to see the list of properties available for the control. When you specify a Row Source (can be either a table or a saved query) for the control, additional properties appear, including a property called Parent Control. In the following screenshot, you can see the new Parent Control property at the bottom of the property list.

The dropdown list for the Parent Control property will display only the names of autocomplete and combo box controls defined on the current view. Select the control you want to be the parent from the dropdown list. In the example screenshot below, we selected CompanyAutoComplete. After you select the appropriate control for the Parent Control property, Access displays an additional property called Related Field. Select the field from the dropdown list on this property that serves as the “linking” element between the parent and child controls and Access will take care of the rest.

When you view your web app within your browser at runtime, Access Services disables the child control until you select a value from the parent control. In the following screenshot, you can see that the Contact combo box initially is not an active control.

After you select a value from the Company autocomplete control, Access Services activates the Contact combo box, filters the list to only display those contacts associated with the Company in the parent control, and then allows you to select an appropriate contact. If you select a different value from the Company autocomplete control, Access filters the Contact combo box again to only display those contacts associated with the new value in the Company autocomplete parent control.

We hope you enjoy this new Cascading Controls feature within Access web apps for SharePoint that opens up more scenarios for your custom web apps built on top of Office 365. Give it a try and let us know what you think! 

The McKinstry Tool Tracker App: Using Access 2013 and Office 365 to manage critical assets

Fri, 01/24/2014 - 11:31

It’s a common scenario: There is a collection of things that people borrow and return. And any collection has common problems: How do you track the items? Are they available? If not, who has them? Where are they? When will they be returned? What is their condition?

For McKinstry, a builder and manager of energy-efficient commercial buildings, the problem centered on a collection of tools, from meters and sensors to hammers. McKinstry employees and subcontractors use the tools to complete energy audits and small-scale construction projects. Their solution for tracking the tools was simple: a clipboard hung in the tool room. If someone needed a tool, they would go find it in the tool room and, hopefully, mark the tool as checked-out on the clipboard. 

The drawbacks are obvious: People who needed a tool had to go to the tool room to see if one was available, and sometimes they’d forget to mark the tools when they checked one out. Tools were not where they were supposed to be, often getting left on job sites or forgotten about and not returned. Over time, the problem got worse and ended up costing the company thousands of dollars to replace lost and damaged tools.

Enter Access 2013 and Office 365. Microsoft worked with McKinstry to create a tool tracker app using Access 2013 (and hosted on Office 365) for the company to try in their Colorado office. The app features a table for tools, with views that show which are available or checked out, displayed by their storage location. There are also tables for people, jobs, and storage locations, which make it easy to associate this data with each tool. A checkout history shows the usage history of each tool displayed within the new Related Items control. The checkout history displays the last time the tool was checked out and who checked out the tool.

The Access web app UI provides a clear, easy-to-use layout for navigating the data in an Access app, a built-in Related Items control that shows the items associated with the record being viewed (think customers and orders), and a fast text-based search for data. Initial feedback was very positive, but some tweaks were requested, such as a home page with basic directions and buttons for the most common tasks (like checking out a tool).

With the early feedback addressed, daily use began at McKinstry. People could now find out whether a tool was available online from their office before going down to the tool room. They could see where the tool was stored, the condition of the tool, and its usage history. They could see who had the tools and when they were expected back.

While the UI and ease-of-use was appreciated, searching for a tool by typing in the tool name proved to be too much overhead for the people in a hurry to get out the door to their job site. They asked if a faster solution was available, such as a barcode scanner like those used for checkout in retail stores. The feature appears complicated but was actually quite simple to implement in Access. The scanner simply sends keystrokes through a USB connection, so Access uses built-in search to instantly identify the tool based on the code in the record for that tool. A data macro launched from a custom Action Bar button checks out the tool and adds a record to the checkout history with a simple button click.

McKinstry uses the Access app in their Colorado office using an Office 365 account, and plans wider adoption into other offices using either Office 365 or an on-premise app once they deploy SharePoint 2013. They know where their tools are and their current condition–improving productivity and providing a significant cost savings. Watch the video to see the tool tracking Access app in action.

Why Office 365 is right for my family

Fri, 01/24/2014 - 11:31

Today’s post is from Suzanne Emberton, a mother of six and full-time IT support analyst in Tennessee. Suzanne is an avid Office user, and in this post, she explains why Office 365 Home Premium is the right fit for her busy family.

I am busy. VERY busy. Not only do I have a large family of 4 kids and 2 step kids, I work full-time and volunteer often. I’m also a self-professed geek, so I am always looking for ways to make my life easier through technology. One amazing tool I’ve found to help my busy life is Office 365 Home Premium. With one subscription, I get all the Office products on up to five devices! Since our family seems to have 2 or more devices per person, this gives us plenty of Office goodness to spread around.

Traditional Office applications like Word, Excel, PowerPoint and even Outlook are basics I’ve always known and loved. Outlook is my family planning hub. I can easily sort through work and personal email quickly and I manage my calendars and tasks lists here as well. Outlook 2013 has great features like Quick Steps that make managing email a breeze. With an account at Outlook.com, all of the information syncs to the cloud and back to my Windows Phone, so I’m always connected to this essential information. I call it my brain, because I’d be lost without it!

Word and PowerPoint are used extensively in our home as well, for school reports and projects. The kids adore the new feature that allows you to search and insert images from Bing directly into the document. With their Microsoft accounts and SkyDrive making cloud storage so easy, they have access to their documents anywhere. My step-daughter has even started work on an essay for school in Word on her Windows Phone. She saved the initial draft to her SkyDrive and opened it in Word on our desktop at home to finalize it. As the official family proof-reader, the ability to share and manage these documents anywhere makes assisting with homework a much easier task for me. We even have the grandparents helping with homework now through using Skype video chat and sharing and editing a PowerPoint presentation together. Talk about family collaboration!

The Office 365 subscription also comes with a few applications that weren’t traditionally available for a Home and Student license scenario, Publisher and Access. Publisher is fabulous for creating flyers, newsletters, invitations and more. In our family, it has saved us from late night trips to the store, when invitations for a birthday party were needed by the next morning! A diverted family argument equals a happier home! Access is also great for creating databases, such as family heirloom recipes or DVD inventories. Templates on Office.com make getting started with this powerful program super easy.

My favorite program in the Office 365 Home Premium package though, is by far OneNote. With OneNote, I am attempting to go paperless in our home. I keep our chore lists, grocery lists, scans of important documents, scans and pictures of the kids art projects, scans of owner’s manuals to all those electronics and appliances we seem to accumulate, scans of various teacher notes and school reminders and more. I spend a little time each week scanning this stuff into OneNote and it keeps the mountain of paperwork in our house down to a much more manageable pile.

With OneNote on my computer, and my notebooks stored in the cloud and OneNote on my Windows Phone, I have access to all this information everywhere. The rest of the family also has access to the shared notebook and can add items during the week that they need, like notebook paper or shampoo. I make my grocery list at my desk computer, and then head off to the store, with my list on the phone. No more scrap paper or forgotten lists on the fridge!

At only $99 a year, it is a great value as we can easily share one license between all of our household computers and keeps us up to date with the latest Office. I feel like Office 365 Home Premium helps makes me a better mom overall and more focused on the relationships in our house, not keeping up with schedules, schoolwork and the mountain of paper. Office 365 Home Premium has been an amazing tool for our family that makes technology actually useful!

–Suzanne Emberton

Register now: PAUG 2014 Database Designer International Conference

Thu, 01/23/2014 - 12:11

Register now to join us May 3-5 for the Portland Access User Group (PAUG) 2014 Database Designer Conference. This year’s speaker lineup includes industry experts F. Scott Barker from AppsPlus, Luke Chung from FMS, Alison Balter from InfoTech Services Group Inc., Armen Stein from J Street Technology, Juan Soto from IT Impact, and Kevin Bell and Kevin Nickel from the Microsoft Access team.

This is the 16th annual Database Designer Conference and will once again be held in the peaceful and natural surroundings of the Conference Center at Silver Falls State Park, which lends itself to a climate that fosters learning, creativity, and socializing. The conference is limited to 42 attendees, which creates a very intimate setting with ample access to the experts, but also means it sells out every year.  There are currently only a few drive-in spots available so don’t delay—register now.  We hope to see you there!

 

Feral Cats: Managing Access databases in your organization

Tue, 01/21/2014 - 18:31

This post was written by Steve Greenberg, a Program Manager Lead on the Access team.

Every few months, the Microsoft Access engineering team receives a high priority e-mail from someone in our worldwide sales org. It’s always the same root issue: an IT department is frustrated with the proliferation of Access databases and the manageability headaches that ensue.  Recently, we talked with the CTO of a British company who compared databases to feral cats wreaking havoc on his organization’s work.

We find that this focus on manageability is nearly universal among customers we talk to, both large and small. Organizations want to empower users in business units to do self-serve development of apps that improve efficiency and reduce costs. But empowerment creates headaches. Data must be secured so that only the right people can access it. It must be backed up so that hardware failure is minimally disruptive. And there must only be one copy of the data, so that all parts of the organization make consistent decisions.

This release, we have a number of exciting things to announce about managing databases with Access. First, let’s talk about some new tools for managing desktop databases:

  • Microsoft Office 2013 Discovery and Risk Assessment allows you to scan an organization’s network shares and SharePoint document libraries to find Access databases. You can scan on demand or on a routine basis, and incorporate the discovery results into a master inventory. Once files are inventoried, they can be evaluated for complexity, financial impact, risk, and errors. Check it out here.
  • Microsoft Office 2013 Audit and Control Management Server contains a set of database management tools that allow you to audit databases and understand the changes being made to them. Check out the tools here.

These tools help manage desktop databases. But most people agree that the right place to store data is on a server. Data that’s centrally stored on a server is inherently more reliable and secure than data floating around as files on file shares and desktops. As you evaluate your data tracking options going forward, you should consider Access 2013 Web Apps. Manageability is one of the most significant reasons we chose to build Access 2013 Web Apps on Office 365 and SQL Azure. Here are some of the benefits:

Reliability: Office 365 and SQL Azure are both enterprise-class cloud services backed by robust Service Level Agreements. To learn more, check out the Office 365 Trust Center. Also, here’s a recent blog post about fault-tolerance in SQL Azure Databases.

Security: You can use SharePoint permissions to control who can create new apps, modify the tables, queries, views and macros of existing ones, edit data and read data.

Monitoring: IT organizations often want visibility into the usage of applications in their organization. To meet this need, SharePoint collects data about each Access Services app. To access this information, visit the Site Contents page. Hover over the app, and click the “…” icon. Then click Details.

Here’s an example of what you’ll see. The App Details page lists properties of the app, allows you to see any errors encountered by users of the app, and gives usage statistics to help you understand how often the app is being used.

For the administrator who loves dashboards, SharePoint allows you to configure monitoring so that you can visit one web page and see the usage of selected applications in your organization.

As a team, we’ve always cared deeply about manageability and believed fundamentally that data is more manageable when it’s on a server than when it’s on a desktop. A SQL Server in a datacenter is the best place for data to live. But for years, the cost and technical barriers to creating such a server meant that it was only available for mission-critical projects. With SQL Azure Databases, we finally have the technology available to allow end users to easily and affordably create real SQL databases in a datacenter. Check them out using the Office 365 preview. In order to try out Access 2013 Web Apps, be sure to choose one of the plans for business: Small Business Premium or Enterprise.

4 ways to create business apps with Access 2013

Tue, 01/21/2014 - 18:31

There are many ways to build useful apps to run your business with Access 2013. You can download an app via the Office Store, build an app using a Web App Template, or create a Custom Web App. You also have the ability to make a desktop database, just like you always have in Access. Each method offers a fast and easy way to get started. Several members of the Access team explain more in the video below:

1 – Use the Office Store

Getting an app from the Office Store is a quick way to start using a database that’s been professionally designed for a specific purpose. To use the store, you’ll need to sign up for the Office 365 preview. (Be sure to choose one of the plans for business: Small Business Premium or Enterprise). After you’ve logged into your account, click the elipsis (…) in the upper-right-hand corner of the screen, then click “Office Store.” You can easily search for apps related to your specific business. You’ll find a wide array of SharePoint apps available—the ones that include the Access 2013 logo are Access 2013 web apps.  After you find the app you want, you can click the Add button to install it directly to your SharePoint site and start using it right away. 

 

If you want, you can customize the app with Access 2013. For example, you could easily add a field to a table to track some information that’s specific to your business. You could even add new tables and new relationships to track additional types of things. When you install an Access app from the Office store, you can get up and running fast, but there’s still plenty of room to grow.

 

2 - Use Web App Templates

You can also make web apps directly from Access 2013. The easiest way to do this is by clicking on one of the web app template tiles that you see right after opening Access. You can search for additional templates if you don’t see what you’re looking for. These templates are fully-functional web databases, but instead of immediately seeing them in action, you’re taken straight to the customization step. In our case, you can select “Task management” (outlined in blue).

Once you’ve selected your template, give it a name and pick a location. If you’re logged into your Office 365 preview account (see above), you will see two available locations by default. The first is for personal apps that you don’t want to share with others. The second is for your company’s default SharePoint team site. This is a great place to put an app that you want other people in your organization to be able to use.

When you click Create, Access makes an app in the location you selected. The template you selected will include tables, relationships, and views that make it useful from the start. You can start adding data immediately by clicking Launch App in the ribbon. Or, you can customize the app in Access to your heart’s content.

 

3 – Build a Custom Web App

You can also build an app by starting from scratch. You can create custom tables and fields for your precise needs or search and add tables from our library of Table Templates. To build a custom web app, open Access 2013 and click on “Custom web app” (highlighted in blue). Just like with Web App Templates, you’ll need to give your app a name and a location.

You’ll be initially given a blank database with no tables or schema. The easiest way to start building your app from here is to use table templates. To search for a table template related to what you’d like to do, simply type something in the search box and hit enter. You’ll see a list of search results from the library. For example, if you type “Orders,” then click on the Orders table template from the results, you’ll find that a bunch of useful stuff related to tracking Orders has been added to your blank database.

 

Each template includes all the tables, fields and views you’ll need to create a working app. Some templates come along with other, related tables. For example, the Orders template also gives you a place to track related Customers, Employees, Products, Suppliers, and Categories.

Access includes a large searchable library of these templates. Whether you want to manage products, employees, customers, projects, tasks, or ideas, it’s likely you’ll find a template that’s tailored to your needs.

4 – Build a Desktop Database

If you don’t have Office 365 or SharePoint, you won’t be able to create web apps (options 1-3 above). You can, however, create desktop databases that run on your machine. If you’ve used previous versions of Access, these are the databases you’re already familiar with, and you’ll find it easy to work with files you’ve created in the past. Desktop databases have all the powerful features, such as VBA, that has made Access such a popular way to run a business.

To create a new desktop database, open up Access and click on any tile that uses the word “Desktop” such as the “Blank desktop database” or “Desktop asset tracking” (highlighted in blue). Just like with web databases, you can start from scratch or choose from a large library of templates. Once you’ve selected the template you want, give it a name and click create to start editing.

When Access is finished downloading your template, you’ll be presented with the familiar, rich database building experience you’ve come to expect from Access, complete with reporting, advanced forms, and VBA.

Conclusion

No matter which method you choose—the Office store, Web App Templates, custom Web Apps, or Desktop Databases—Access 2013 will help you get started quickly. Try it out today by signing up for the Office 365 Preview. If you’d like to take advantage of Access 2013 Web Apps, be sure to choose one of the plans for business: Small Business Premium or Enterprise.

Webinar: A gentle, human intro to Access 2013

Mon, 01/20/2014 - 11:31

Need a quick tutorial on Access 2013? Here’s your chance. You’ll learn how to create your first database in no time. First, we’ll go over the two kinds of databases you can create: browser-based Access apps and desktop databases. Then we’ll show you how easy it is to download a template–a readymade database with all the parts in place.

You just fill it out and you’re good to go. From there, we’ll step you through the basic parts of a database: tables, queries, reports, forms and macros.

Want a sneak peek? Here’s a 30-second trailer.

What you will learn at Tuesday’s webinar

  • How to identify the two types of databases: Access apps and desktop
  • How to open a database template and fill it out with your own data
  • How to identify the five database objects of a database

References for this webinar

Go to http://aka.ms/offweb for more information on how to join the series.

 –Colin Wilcox

Register now: UK Access User Group National Seminar November 21

Mon, 01/20/2014 - 11:31

If you can’t make it to Access Day in Seattle on November 21, why not attend the UK Access User Group 2013 National Seminar at the Microsoft Thames Valley Park in Reading?

The UK Access User Group conference is presented by and for Access enthusiasts, and the fall 2013 seminar features 5 sessions on a variety of topics:

 

Shape Up Or Ship Out! Microsoft Access Case Study (Presenter: Paul Millennas)
Paul will show how Microsoft Access has been used at Babcock Engineering at Rosyth on £12billion worth of projects including the construction of aircraft carriers, ships and submarines.

Adding Value To Your Applications By Integrating to Accounts Software with Specific Examples
using Sage 50
(Presenter: Tony Bayliss)
Nearly every Access project includes integration, and Tony will show how this has been achieved on some of his recent projects.

The Patient Death–What Happens Next? How a Hospital Handles and Manages the Human Side of a Patient Death (Presenter: John Baker)
John will present a case study of an application used in a key area of the National Health Service.

Web Application in Access 2013 Case Study (Presenter: Alan Cossey)
Alan will take us through the stages of development of an Access 2013 web database which includes both a standard Access front end and a web UI.

The Way Forward: A Discussion Session (Presenter: Rod Gordon)
If you are asked to develop a new system in the years to come, then what technology will you use? Building on the day’s presentations, Rod will lead a discussion session considering the different options available to you today and in the coming years.

Book your spot now for the fall 2013 National Seminar, and visit the UK Access User Group site for full seminar details.

Register now: Join us for Access Day November 21

Mon, 01/20/2014 - 11:31

Join us Thursday, November 21 for a 1-day Microsoft Access conference in Bellevue, WA. Armen Stein, long-time Access MVP and president of J Street Technology, is organizing Access Day, which features internationally-respected presenters, including Access MVPs, book authors, and members of the Access development team at Microsoft.

Kevin Bell, Luke Chung, Tom van Stiphout, George Hepworth, Andy Couch, and Armen Stein will lead sessions on a mix of Access client techniques and new Access web app development using SharePoint and SQL Azure. The intended audience is Access developers and advanced users who want to learn ways to make their applications more powerful and develop Access solutions for the web.

The conference will be held at The Bravern in downtown Bellevue, which is walking distance from the transit center and many hotels.

Check out www.AccessDay.org to register and to learn more information and schedule details.

The Access 2013 Runtime now available for download

Mon, 01/20/2014 - 11:31

After a long, hard effort by many people, the stars have finally aligned. The Access team is pleased to announce the release of the Access 2013 Runtime! We know lots of you are very anxious to get your hands on the Access 2013 Runtime. You can now go and download the Access 2013 Runtime from the following location:

http://www.microsoft.com/downloads/details.aspx?FamilyID=0c44d623-a8bc-4be5-a561-f11d33602505

The Access 2013 Runtime is available for download in 38 languages.

Microsoft Access 2013 provides a rich platform for developing database management solutions with easy-to-use customization tools. If no end user customization is required (including report modifications), you can choose to distribute those Access 2013 solutions so that they run without requiring a full installation of Access 2013. To do so, you must package and distribute your application with the Access 2013 Runtime.

Web-based apps for SharePoint that are built with Access 2013 do not require a runtime–only a supported web browser is required.

Enjoy!

Attend Access 2013 presentations in Denver on July 2 and July 11

Mon, 01/20/2014 - 11:31

Join our own Kevin Bell as he takes a break from his summer vacation to show off the new Access 2013! Kevin will present to the Denver Area Access User Group meeting on Tuesday, July 2 at 6-9pm MDT and to the Colorado SharePoint Users Group on Thursday, July 11 at 6-8pm MDT. 

Both meetings will be held at the Microsoft office located at 7595 Technology Way, Suite 400, Denver, CO 80237.

July 2, 6-9pm: The best of both worlds: Hybrid apps with Access 2013 

Access 2013 web apps allow you to quickly create a database in Office 365, but if you are familiar with traditional Access desktop development, you may encounter some limitations. One of the best features in Access 2013 is that web apps created on Office 365 use SQL Azure to store their data. Unlike traditional SQL Server databases, SQL Azure is cloud-based, so your data can be accessible anywhere. However, every byte of data requested from the Access client is now traveling over the internet, so you need to be very diligent about returning the least amount of data possible. Kevin will show several techniques to help you convert your existing desktop applications to an Access 2013 ‘hybrid’ app. Go to www.daaug.org to learn more about the Denver Area Access User Group.  

July 11, 6-8pm: Real SharePoint apps, real fast with Access 2013 

With the new Microsoft Access 2013 and Access Services 2013, anyone can build SharePoint Apps backed by SQL Azure in just about 60 seconds. In this presentation, Kevin will show you how easy it is to create, share, and collaborate with Access 2013 in an IT managed environment. The new Access allows you to satisfy the need for agile development from your business units and still rest assured that the data is secured, backed up, and managed appropriately. Kevin will also talk about how legacy Access desktop applications within your organization can be brought forward to the web on SharePoint and SQL Azure. Go to www.cospug.com to learn more about Colorado SharePoint Users Group. 

About the presenter 

Kevin Bell is a test engineer on the Microsoft Access team in Redmond, Washington. Prior to joining Microsoft in 2008, Kevin was a partner in a consulting practice in Colorado that specialized in building software solutions for small businesses and departments in large corporations, primarily working with Microsoft Access and SQL Server. Over the years, Kevin has been a member, officer, and frequent presenter at the Denver Area Access User Group and now regularly presents at the Seattle Access User Group and the annual Portland Database Designers Conference. This past November he had the pleasure of presenting at the 2012 UK Access Groups National Seminar in London.

 

New server release: Spreadsheet controls in Office 2013

Mon, 01/20/2014 - 11:31

This blog post was originally published in September 2012, and we’ve updated it to announce the recent release of the server applications. It is brought to you by Steve Kraynak, Office Program Manager focusing on spreadsheet management features.

In April, we released 2 important new spreadsheet management server-based applications to complement the desktop spreadsheet management features we introduced with the release of Office 2013.   

Now available are Audit and Control Management Server (ACM), and Discovery and Risk Assessment, which are both designed to help you manage the use of spreadsheets and Access databases. Now there are 5 features designed to help you manage the use of Excel spreadsheets and Access databases:  We talked about these applications in a post last September, and now they’re available to customers:

  • Audit and Control Management Server (new)
  • Discovery and Risk Assessment (new)
  • Spreadsheet Inquire
  • Spreadsheet Compare
  • Database Compare
Some background: EUCs

The acronym EUC, which refers to End-User Computing applications, has become a common way to say “critical spreadsheets and databases created by end users without the involvement of IT.”  The 2 new applications are aimed at helping organizations deal with the significant risks introduced by EUCs.  
 
In many ways, EUCs are a very good thing.  A powerful tool like Microsoft Excel gives the end user a fantastic amount of ability, agility, and efficiency.  However, with great power comes the chance to make some mistakes.  In many situations, these EUCs are very important spreadsheets or databases that are designed, developed, tested, accepted, and used by a single person or a small group of people.  It’s the norm that EUCs are created on the fly, without a structured development process (design, develop, test). Since we know that humans occasionally make mistakes, it’s a good bet that mistakes will make their way into the EUCs and that these mistakes will go unnoticed until something bad happens. 
 
The data produced by the EUCs is typically trusted and accepted by stakeholders, and often becomes the basis for critical decisions and reporting.  Without proper controls, use of EUCs can lead to financial and operational loss, through undetected errors, unexpected mistakes, and even fraud. 

What’s new in Office 2013

With the 5 new applications mentioned above, we now offer much-needed tools to help organizations begin to gain control of their EUCs: 

  • Now, you can find and assess the complexity and risk of your EUCs using Microsoft Office 2013′s Discovery and Risk Assessment.
  • End users, auditors, spreadsheet developers, and analysts now have a powerful analytical tools for spreadsheets with the introduction of Spreadsheet Inquire.
  • Anyone can quickly and easily determine differences between any 2 spreadsheets by using Spreadsheet Compare, and likewise for Access databases with Database Compare.
  • And, to round out the suite of EUC control applications, your organization can monitor and track changes down to the cell level using Audit and Control Management Server.
How to get the new stuff

Three of the new tools are available on the desktop with Office 365 ProPlus or Office Professional Plus 2013.  These include Spreadsheet Inquire, Spreadsheet Compare and Database Compare.  Because Spreadsheet Inquire is an Excel Add-in, you need to turn it on in your Excel Options to start using it.  Spreadsheet Compare and Database Compare will show up as shortcuts along with the rest of your Office tools in the Windows Start Menu.
 
The server products, ACM Server and Discovery and Risk Assessment, are available through volume licensing and enterprises can talk to their Microsoft sales representative to learn more about licensing with SharePoint and Office.

Where do I begin? Understanding the scope of your risk

One of the first steps in tackling any problem is to understand the problem. If an organization hasn’t taken stock of their EUCs, and hasn’t put in place adequate procedures and controls, then chances are good that no one knows how much risk they have with EUCs, or even how many EUCs they have.  Discovery and Risk Assessment gives organizations the ability to create an inventory of their EUCs and analyze them so they can determine which EUCs are the most complex, impactful, and risky. 

Simply having an inventory is one important aspect of controlling EUCs in an organization.  The new Discovery and Risk Assessment application also helps you determine which are the most risky, so you can start taking further steps to reduce your risks.  It does this by crawling specified network paths and SharePoint sites to find the EUCs, and then it analyzes each EUC and gives them a rating for complexity, materiality (impact to the organization), and risk based on configurable criteria.

Finally! Comparison tools for Excel (and Access, too)

The new desktop tools provide some great efficiency improvements.  If you ever wanted an easy way to compare 2 spreadsheets, now you have it.  Spreadsheet Compare lets you pick any 2 workbooks and compares them in a fraction of the time it would take to you to do it manually.  Also, the differences between spreadsheets are categorized so it’s easy to focus on important changes, like changes to formulas or VBA.  You can also see changes to the data in cells.  Spreadsheet Compare makes it easy to distinguish between the different types of changes that can occur in a spreadsheet.  Also, Spreadsheet Compare is able to determine when rows or columns have been inserted or deleted and it factors those changes into the equation before comparing the cells that may have shifted as a result. Rather than showing differences just because thousands of cells moved down by one row, Spreadsheet Compare can simply report that a row was inserted.

Database Compare provides similar capability for Access databases.  Now you can pick any 2 Access databases and get a report of the differences between the tables, queries, modules and more.  If someone changes an important query, now you can easily see exactly what was changed.

Powerful analytical capabilities

Spreadsheet Inquire gives you the ability to perform a detailed analysis of a workbook in Excel.  You can think of the Workbook Analysis as an MRI for spreadsheets. The Workbook Analysis feature gives you the ability to find and report on almost 50 different aspects of your spreadsheet.  For example, you can easily find all the formulas with errors, inconsistent formulas, or invisible cells, and you can create a report with all this information so you can begin to document and understand your spreadsheets better, which is a critical aspect of any good EUC control program. 

Another highlight of Spreadsheet Inquire is the Workbook Relationship Diagram.  This feature maps out the data lineage of your workbook.  If you have linked workbooks or data connections, now you can quickly see of where all the data is coming from, whether you have broken links, and whether the data is current. On the other hand, maybe you’re in the process of developing a new spreadsheet.  In that case, you can use these same tools to help you build a better spreadsheet from the start. 

Keep track of changes in all of your mission-critical EUCs

The workhorse of all these new products is Audit and Control Management (ACM) Server, which provides powerful change management features for Excel and Access files.  ACM Server keeps a close eye on your critical EUCs and gives you the ability to see the “who, what, where, and when” for changes in EUCs down at the cell level.  You can see if someone has modified formulas or VBA code, and ACM Server will show you the old value and the new value after the change.  ACM Server also keeps the revision history for your files, so you can easily compare any 2 versions, or even restore a prior version. 

To get more information about ACM Server and the rest of the spreadsheet control tools, please read Spreadsheet Management with the new Office

Join Access sessions at TechEd in June

Mon, 01/20/2014 - 11:31

Join us in New Orleans for Microsoft TechEd North America June 3-6and in Madrid, Spain for Microsoft TechEd Europe June 25-28.

Scott McFadden and Jeff Conrad from the Microsoft Access product team will present on the new Access web app features available in Microsoft Access 2013 at this year’s event in New Orleans. Harrison Shapley and Kevin Nickel from the Microsoft Access product team will share a similar presentation at the TechEd event in Madrid.

Sign up for the Access Services session at TechEd

If you’re attending Microsoft TechEd this year, be sure to sign up for the Access Services session:
SES-B309 – Real SharePoint Apps, Real Fast with the New Microsoft Access 2013.

Come see how Microsoft Access 2013 and Access Services 2013 will let anyone build SharePoint Apps backed by SQL Azure in just about 60 seconds. In this session, learn how to create, share, and collaborate in an IT managed environment. Office 365 provides everything you need to enable end users to create high-value, “no code,” collaborative business apps. The new Access allows you to satisfy the need for agile development from your business units and still rest assured that the data is secured, backed up, and managed appropriately. Also, hear about how legacy Access desktop applications within your organization can be brought forward to the web on SharePoint and SQL Azure.

In addition to learning more about Microsoft Access 2013 and Access Services 2013 at the Microsoft TechEd events, you can find out about the future of Microsoft products, solutions and services directly from the leaders, complete with news, announcements and demos. Unlike traditional classroom training with a fixed set of topics, TechEd allows you to personalize your learning experience. Mix and match the sessions-and session types-you want to attend, including breakout sessions, hands-on labs, keynotes, and more. Each of the four days provides you the opportunity to completely immerse yourself in sessions that offer insight into the products you are working with. 

Connect and explore

Connect with thousands of your peers and Microsoft product team members who build the products you work with every day, as well as industry experts who are deploying and building Microsoft solutions that may benefit your projects. Explore vendor solutions that can help you achieve your goals. Discuss challenges and brainstorm potential solutions with like-minded individuals at structured networking opportunities including the TechExpo Welcome Reception, Ask the Experts, and the Attendee Party in North America and Country Drinks in Europe.

Explore products and tools that can make your job easier and cut down on the time you spend troubleshooting issues. There is no better way to learn about Microsoft products than to play with them in the hands-on labs, at demo stations and elsewhere throughout the conference. TechEd is your best chance of the year to see how Microsoft products were made to be used and learn how to use them.

Join us in person

In today’s online and always-connected world, there is tremendous benefit in having dedicated time away from your office to focus on advancing your expertise, minus the distractions. By attending TechEd in-person, you are giving yourself the best chance to learn, connect and explore and take advantage of everything the event has to offer.

We hope to see you there!

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

Mon, 01/20/2014 - 11:31

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!

 

 

 

Pages

Drupal 7 Appliance - Powered by TurnKey Linux