Excel Online – Refreshing data from an Access Online App (SQL Azure Database).


If you want to refresh the below Access Online data in Excel Online.

You will need to carefully create an Excel workbook.  But before you do that, you will need to know your Read-Write Connection Information

To view this Open your Access Database > File > Connections > Manage

From Any Location

Enable Read-Write Connection

View Read-Write Connection Information


You now have the information you need when building a workbook.

Now Open Excel > Data > From Data Connection Wizard

Other/Advanced

SQL Server Native Client 11.0

Note: if you do not see "SQL Server Native Client 11.0" you will need to download it:

Microsoft® SQL Server® 2012 SP1 Feature Pack
http://www.microsoft.com/en-us/download/details.aspx?id=35580

ENU\x86\sqlncli.msi
ENU\x64\sqlncli.msi

Microsoft® SQL Server® 2012 SP1 Native Client  Microsoft SQL Server Native Client (SQL Server Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 2005, 2008, 2008 R2, and SQL Server 2012. SQL Server Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2012 features. This redistributable installer for SQL Server Native Client installs the client components needed during run time to take advantage of new SQL Server code name ‘Denali’ features, and optionally installs the header files needed to develop an application that uses the SQL Server Native Client API.

Enter the Server name, User name and password and Select the database (Note: you will need to manually paste the database).  Also tick Allow saving password.

Choose the Table:

Next


Finish

Import Data box will appear.

IMPORTANT: You must choose "Add this data to the Data Model" or this will NOT refresh in Excel Online!

OK

You will be prompted to re-enter the Password. 

OK

The data will now be pulled into the Excel Rich Client!

But, you are not done.  You are going to need to edit that connection string via Data > Connections > Properties...



Tick Save Password

 

Click OK

You will get prompted.

Click Yes

You will now see the Password is embedded in the Connection String!

Click OK

You will get a prompt.

Click Yes

Publish the workbook to SharePoint Online.

You will now be able to choose Data > Refresh All Connections!


IMPORTANT FINAL NOTE:

Since the only way to get this to work is to choose "Save password" you need to be aware of the below information:

Connection properties
https://support.office.com/en-us/article/Connection-properties-c84c77c3-f2ac-4356-b4d7-88cd9a483c7a

Save password: 
Select this check box to save the username and password in the connection file. The saved password is not encrypted. Clear this check box to log on to the data source, if a user name and password are required, the first time that you access it. This check box does not apply to data retrieved from a text file or a Web query.

Security Note:  
Avoid saving logon information when connecting to data sources. This information may be stored as plain text, and a malicious user could access the information to compromise the security of the data source.

If users are unable to open the workbook in the rich client/download the workbook, they will not have access to the connection string information.  So you may want to remove this ability.

Permission levels and permissions
https://support.office.com/en-us/article/Permission-levels-and-permissions-49D456EB-D3C8-4402-86B1-DEB911224AFB

Open Items
View the source of documents with server-side file handlers.



Comments (0)

Skip to main content