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
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
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.