I have had a hard time creating data connections with an Access 2013 App database. After a good few hours spent scouring the internet for a solution, and a good few more hours uncovering a “solution” that is underwhelming at best, I am happy to share with you my findings. I really hope that someone will leave a comment with a better solution at some point in the future.
This blog post will provide step-by-step guide on how to achieve a data connection from an Excel workbook (which can be hosted in SharePoint) to the SQL database behind an Access 2013 App. Once this is achieved, a good BI developer should have no trouble visualising the data captured via the Access App with the help of pivot tables, slicing and graphing.
The first step is to identify the server address and database to connect to along with the credentials required to authenticate.
- This can be done by navigating to the Access App, clicking the ‘settings’ icon, then clicking ‘Customize in Access’
- Download the .accdw file and open it to launch access
- Click ‘FILE’ in the ribbon
- Click ‘Manage’
- In the drop-down menu ensure that ‘From Any Location’ and ‘Enable Read-Only Connection’ are highlighted with pink squares. If not, click them
- Click ‘View Read-Only Connection Information’
- Take note of Server, Database, UserName, and Password from this dialog as you will need them all later
Next we use this information to create the data connection.
- Launch Excel
- Create a new external data connection ‘From Data Connection Wizard’
- Click ‘Other/Advanced’, then ‘Next’
- Click ‘SQL Server Native Client 11.0’, then ‘Next’
- On the ‘Data Link Properties’ dialog, uncheck the ‘Blank Password’ box and check the ‘Allow saving password’ box, then input the server name, user name, password, and database
- Test the connection, you should see a dialog box with ‘Test Connection Succeeded’
- Note that it is when you attempt to make a data connection without providing the database that you get the following error which I bet lead to to this post:
- You can now click ‘Ok’
- Uncheck the ‘Use Trusted Connection’ checkbox and replace the existing password with the correct one. Click ‘Ok’
- Select a table and click ‘Next’. You can get fancy here later, let’s just get it working first.
- Click ‘Finish’
- Click ‘Ok’
- The data connection will fail with the following error:
The final frustration!
On the next dialog, uncheck the ‘Use Trusted Connection’ checkbox and replace the existing password with the correct one. Click ‘Ok’.
The second time it works. This process of providing the connection credentials twice is required not only upon the creation of the connection but also every time the data needs to be refreshed. It makes for a rather poor UX and it is a pretty awful scenario to have to explain to a client.
I really want to believe that there is a setting (most probably under the ‘All’ tab on the ‘Data Link Properties’ dialog) that will workaround this issue however I am yet to find it. Please leave a comment if you find a solution to this issue.