Back
Access

Visualize your Access 2013 web app data in Excel

This post was written by Harrison Shapley, a Program Manager on the Access team.

After you start organizing your
data with Access 2013 web apps, you may find yourself wishing to visualize or
summarize it. For example, imagine you are tracking your deal pipeline in
Access, including when you expect to get paid and how much. Wouldn’t it be
great if you could make a visual graph of these projections to help you make
decisions?

That’s where external data
connections come in handy. By following the steps below, you can connect an
Excel workbook directly to the SQL Server database that stores the information in your
Access 2013 web app. Then, you can build live graphs and visualizations using
Excel. Of course, you could copy and paste the data from an Access datasheet into an Excel workbook, but if the data in Access changes, you’d have to
repeat this process each time. By setting up the connection, you streamline the process, making it much
easier to generate up-to-date reports.

Best of all, you can use this
same technique with other programs and services that know how to communicate
with a SQL Server database, such as Crystal Reports, Zapier, desktop Access databases, and custom web pages. The possibilities are vast, but
for now, we’ll focus on Excel.

Creating reports and graphs in Excel

There are many ways to summarize
data in Access 2013 web apps. If Excel is your application of choice, you can follow these
steps to connect your Excel workbook to your Access 2013 web app database for easy
reporting.

Step 1. Enable connections to your database

Open the app in Access, and then go
to File to view the backstage (also
known as the Info section of the
file menu—see the picture below). There you will see information about your database, and you have
options to report on your data and manage connections to your database. Before
you can create reports, you need to enable connections to your database from
the Connections menu. At the
minimum, you will need to Enable
Read-Only Connections
. Depending on your situation, you may also need to
enable connections “from your location” or “from all locations”.

Step 2. Create a connection to your database in Excel

After enabling connections to
your database, open up a new workbook in Excel and navigate to the Data ribbon. You want to Get External Data from your SQL
database, so select the From Other
Sources
button, then From Data
Connection Wizard
. The parameters of the connection need to be specified
manually, so selecting “From SQL Server” will not work. Once the Data
Connection Wizard is open, select Other/Advanced
and click Next. Select the SQL Server
Native Client
and click Next again.

 

You will then be prompted for
the database connection information. If you go back to the backstage in Access,
you will find an option to View
Read-Only Connection Information
in the Manage Connections menu. Selecting
this option will bring up all of your connection information, including the
server name, database name, username and password. This information can be
copied directly into Excel’s data connection wizard.

Copy the information as shown
below and then click OK:

 

Step 3. Select the data to link

The next screens will ask you
about what tables you want to bring in from your database. On the first screen,
uncheck Connect to a specific table
and click Next.

On the second screen, make sure
to select Enable selection of multiple
tables
and Import relationships
between selected tables
. You only need to select the tables you want to
report on, and make sure not to select any tables from AccessSystem or
AccessRuntime. In this example, all of the tables were selected to be linked.

Finally, a dialog comes up
asking what you want to create, including a regular table, a PivotTable, and a
PowerView report. In this example, we created a PivotChart.

 

Step 4. Create the reports and visualizations you want

Once you click OK, a live
connection will be made and the data will be fetched from the SQL server and
shown on the right. You can then use the full capabilities of Excel as you
normally would to create your report.

In this case, we were creating a
PivotChart to show the companies with the most orders, so we brought the Company field from the Customers table
to the Axis area and the Order ID
field from the Orders table into the Values area. Instead of the sum of Order
IDs, we wanted the count of Orders, so this was changed from the dropdown in
the Values area. Finally, we needed help Excel understand how the two tables connected with each other, so we created a new relationship linking the Customer ID field in Orders to the ID field in Customers. The PivotChart now displayed as desired, and
we could easily see that Company F and Company H had made the most orders.

In summary, we covered how to
create reports in Excel, but this is just the beginning of what you can
achieve by exposing your data through ODBC connections. By exposing your SQL
database to ODBC connections, you can use any application that supports ODBC.
This permits many possibilities for extensibility to any kind of custom data application, not
just Access and Excel. We can’t wait to see what you do with this feature! Please share your experiences and feedback in the comments section below.

Join the conversation

11 comments
  1. This blog post makes me wonder if Access is being depreciated or eliminated.
    Here is an Access blog informing how Excel can work with Sql Server. Both of these products probably have blogs of their own. Access has a reporting functionality that can display charts and graphs just as good as excel, and has had this for many years. So why am I being told that Excel can help me achieve this same functionality, on an Access blog?
    It does say on this blog post above that "you can use this same technique with other programs … such as… past versions of Access". So please tell me if this means that only past versions of Access will have this functionality, but not the future versions?
    For me, if Access is still going to be a desk top application creator that also has some new abilities for the web that can be interesting. But if the only commonality between the old Access and the new Access is the name, then I would like to know about it please.

    • GiladP1.

      Perhaps you missed the point? The SQL Server in question is not just any SS (although this technique would work just well with any SQL Server). This article is aimed at exploiting the SQL Server, or SQL Azure, database behind a web app to deliver reporting in other tools, including Excel.

      Unfortunately, one of the motivations for wanting to do this is the lack of native reporting in an Access web app. That’s a fair criticism, IMO. Web apps should be more robust in the area of reporting. Nonetheless, Excel is a viable alternative that opens up all of the richness of the Excel environment to reporting. And that’s something we frequently do with client databases anyway.

  2. I was looking for information about Web App Data and just came through this post. Thanks for sharing

  3. I think Gilad has a valid point. The article simply demonstrates that Excel can link to sql server, sql azure in particular. Fine, but it seems peculiar that the author stated that prior versions of Access could also link to sql server for reporting. There is no apparent reason to exclude Access 2013, since it can do the same thing as previous versions in this regard.

    The current version of Access has the new web app functionality, which lacks reporting. That’s a major subtraction relative to the reporting capability of Access 2010 web databases. Some of us have speculated that the reason reporting was removed is that Microsoft was never able to offer the reporting feature of Access 2010 web dbs in their online offering.

    It would be interesting to hear why the author excluded Access 2013 reports via odbc links and only mentioned earlier versions of Access. Obviously, Access 2013 exists and is not being depreciated, but it is a curious exclusion. It might be a simple unintentional error, but assuming the author knows Access well, that’s hard to imagine.

    • To clarify, Access 2013 desktop databases (which have rich vba and reporting functionality) _can_ connect to SQL server–just like previous versions of Access desktop databases have been able to do. I updated the sentence in the article that may have been confusing you.

      As far as the differences between Access 2013 web apps and Access 2010 web databases, there are pluses and minuses. It’s true that the browser-based printable reports that were available in Access 2010 web databases were removed. But, on the flip side, the data being stored in a clean SQL database, means that many more kinds of reporting software can connect to it. Access 2010 web databases had their data stored in SharePoint lists, which aren’t as widely supported by third-party reporting tools as SQL tables.

  4. Thanks for revising that sentence. I don’t think it would have confused anyone that knows Access, but for any new user, it might have.

    I agree with you that the sql server backend is a major plus compared to Access 2010′s storage in sharepoint. Reports are almost always a requirement for LOB apps, so it’s too bad that we don’t have reporting right at hand with Access 2013 web apps.

  5. Is the free Access runtime available for Access 2013 as it was for previous versions?
    I also understand that it is not possible to create web applications with Sql-Server express but only with the full version or Sql Server, or with an online version like Azure right?

    • @Gilad

      If you want to host Access 2013 Web Apps on your own SharePoint servers, you’ll need to deploy the full version of SQL Server 2012 as part of this infrastructure. However, if you have an Office 365 subscription with SharePoint Online (which is included in the Small Business and Enterprise offerings), Microsoft will host the SQL server databases for you on SQL Azure. If you’re a subscriber, you don’t need to deploy SQL Server at all–express or full.

  6. So I have similar issue as does Gilad and Michael. I’m having difficulty understanding and showing excitement towards investing in the new future of Access as we strip away what I consider to be "critical" components such as the reporting (charting to be specific) and Web/Hybrid database features. Furthermore it is very troublesome to invest the amount time as I have to develop web database and hybrid database knowledge in working with Sharepoint 2010 (a killer feature by the way) only to see it supported in a limited fashion in SP2013 (you cannot create web databases but those created in 2010 SP are still supported). To suggest that now an Access developer look at "other tools" or Excel to provide the reporting is a HUGE loss and forces one to wonder why continue developing with Access if this is the path that Microsoft is heading down. There is a small/medium business market where all those components are still incredibly relevant. Why exclude them from the 2013 and future releases?

    One other thing, my understanding in offereing the wonderful web database features with access servcies was to democratize power users thus removing dependencies on internal IT administrators. Anytime one is required to interact with SQL server in a direct fashion you immediately remove that sense of democracy since it will always require interaction with a DBA or similar to acquire rights and server information to make connections etc… In most large organizations this can result in change requests also that are usually tedious and can kill worker inspiration. In the small/medium business market if they are forced to look at "other" products where is the value add?

    • @Jason

      We realize that making reports visible in the browser is important to some cases–it was simply a matter of priorities that we weren’t able to deliver it for the 2013 release. However, I think the benefits of having the platform based on SQL–increased speed, extensibility, portability–were well worth this cost.

      To be clear, any user who is able to author an access app is able–directly through access–to get the connection strings to the SQL database behind it. There is no IT intervention required if you follow the steps in this blog post.

      Part of deploying SharePoint 2013 servers with Access Services 2013 involves setting the SQL Server location that will store the Access Apps. After that, it’s entirely self-service–as long as you can open the app in Access 2013, you can get the connection string. And if your company uses SharePoint Online, the Microsoft takes care of the entire server infrastructure for you, so IT doesn’t need to be involved at all.

  7. Why aren’t the comments appearing in chronological order and why can’t we receive email notifications of when a new thread or a new message is posted?

Comments are closed.