If you've steered clear of a cloud-based database solution because you think they're too complicated, you should consider creating an Access web app. These apps are a great way to offer easy data entry and viewing via a browser. Unfortunately, the interface is limited, and that means you can't manipulate your data into a meaningful report on the fly or for the boss. This situation is further complicated by storage. The data's not in the web app; it's stored in a cloud-based SQL Azure database (if you're using SharePoint Online to share the web app).
In this article, I'll show you how to import Access web app data into an Excel workbook where you can then filter, graph, analyze, report, and more. Importing is an easy two-step process, but it's not particularly intuitive. You must first enable a connection for the database. Then, you create the connection in Excel, which allows you to import the data. If you skip the first step, the subsequent import will fail.
I'm using Excel 2016 (365 E3 subscription) on a Windows 10 64-bit system. This article assumes that you have a web app shared by SharePoint Online and Excel 2016 desktop, but you can also use Office 2013. Access web apps aren't available to or compatible with older versions of Access. There's no downloadable demonstration file.
You can learn more about Access web apps by reading How to collect and modify data using an Access web app. If you're not using SharePoint Online, you can read about local configuration options in How to: Make external connections to an Access Web App.
The easy way
The easiest way to get data from your web app into an Excel sheet is to copy the data directly from a datasheet. The problem with this route is that the data is static. If all you need is a one-time data grab, take the easy route. If, on the other hand, you need ongoing access to the most up-to-date data, you'll want to import that data via a connection that's saved with the workbook file.
Step 1: Enable a connection
Despite SharePoint's connectivity magic, there's no connection between your local copy of Excel and your web app. You must create that connection yourself. The first step is to enable a connection to your web app; it's easy to miss this step if you don't know about it. It's not an intuitive task.
First, open your (local) web app in Access 2016 (as if you were modifying the database, not using the browser app). Then, click the File tab. From the Manage dropdown, choose Enable Read-Only Connection (Figure A). Leave your web app file open. In the next section, you'll paste connection values from your web app into Excel.
Enable a connection.
There are other connection options, but always choose the option that provides the least access and yet still meets your needs. In this case, read-only is all we need to import the data so we can work with it in Excel.
Step 2: Create the connection
With the web app connection-ready, you can create a connection to the data in Excel by following these steps.
In an open workbook, click the Data tab. Then, in the Get External Data group, click the From Other Sources dropdown and then select From Data Connection Wizard (Figure B). In the first pane, choose Other/Advanced and then click Next.
Start the wizard.
From the list of providers, select Microsoft OLEDB Provider For SQL Server (Figure C) or the SQL Server Native Client and click Next. (If you're using Excel 2013 and you used the native client, the remaining steps might not match your experience, but they're close enough that you shouldn't get lost.)
Choose a provider.
Return to Access and choose View Read-Only Connection Information (Figure D) to display the connection values shown in Figure E. Using this figure as a guide, copy and paste the appropriate connection values into Excel's Data Link Properties dialog. You can click Test Connection if you like, but it isn't necessary.
View the connection values into Excel.
Enter the connection values into the Excel wizard.
Click OK to complete the connection and view the web app components shown in Figure F.
You now have access to the data in your Access App.
Now, uncheck the Connect To A Specific Table option and click Next. Enter a meaningful name for the connection (Figure G) and click Finish. If you like, check the Save Password In File option. Use caution when doing so because saving the password with the connection file can be a security risk. Enter the password, if necessary, and click OK.
Name the connection so you can reference it later.
In the resulting dialog, check the Enable Selection Of Multiple Tables option and select the appropriate tables. Be sure to check the Import Relationships Between Selected Tables option (Figure H) and then click OK. It's important to select the tables you might need, but don't go overboard and select them all unless that's what you truly need. (Don't select the AccessSystem or AccessRuntime system tables.)
Identify the data you want to import.
The next dialog prompts you to specify how and where to store the data. As you can see in Figure I, I want to store the data as an Excel Table object in a new worksheet. Click OK to import the data.
Specify where to store the data.
Our connection generated three new sheets (Figure J): one for the books, one for the errata files tracked for each book, and thanks to the relationships, there's even a table of books with no errata files to track. (I renamed the sheets.) With the data in Excel, you can now report and analyze the data using features that aren't available in the web app interface.
It took several steps, but the data is now in Excel as a Table object.
Once you create the connection in an Excel workbook, it's available for further use. To make sure you're working with the most up-to-date data, click Refresh, enter the password, and click OK.
To open the connection again, click the Data tab. In the Get External Data group, click Existing Connections. Then, in the resulting dialog, select the connection and click Open to display the Import Data dialog.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at firstname.lastname@example.org.
- Office Q&A: Three simple problems with quick solutions
- How to update multiple Outlook contacts using an easy grouping trick
- Two technique enhancements from readers during 2016
- Office Q&A: How to replace direct formats with styles in a Word document
- Three ways to expose formatting inconsistencies in a Word document
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.