You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Tips
How-to
News
Videos
Stories
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.
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.
Comments: (12) Collapse
Great article!
I tried to re-create the steps, on a Windows 8 machine.
First, I had to install SQL Server Native Client 11.0
Then, when I tried to connect via Excel, I get an error: http://sdrv.ms/14dYmNg
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.
I was looking for <a href="mcafeepromocodes.com/.../a> about Web App Data and just came through this post. Thanks for sharing
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.
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.
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.
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?
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?
@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.
@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.
Comments: (loading) Collapse