If you are using our Sharepoint 2010/2013 Enterprise Solution with Access Services you have the ability to publish your web database to Access Services 2010 or create an Access 2013 Web App in Sharepoint. The problem with SharePoint 2013 Web Apps is that you cannot create and run reports on your data in the browser (like in 2010). The solution is to create a desktop Access frontend that connects to your webapp’s data. Here is how you connect to your MS Access 2013 Web App with Access Hosting.
How to Report on your Access Web App from MS Access:
1. Go to your Web App in Sharepoint (on accessontheweb.com) and click the “customize in Access” button to download an accdw file.
2. Open and log into your web app by opening the accdw file downloaded from your Access Hosting Sharepoint site.
3. Go to File->Connections (be sure that Read-Only Connection is checked) and click on Manage->View Read-Only Connection information. Make a note of all the connection data.
4. Be sure to record all of the data shown in this connection information
5. Under Report on My Data, click the create reports button
6. This will prompt you to create a new access frontend file. Name the file and save.
7. The connection will fail because the server name is not a valid web location – so you will be prompted for the SQL connection information. Change ahcombo1 to your Sharepoint site collection url WITHOUT http:// (i.e. example.accessontheweb.com)
8. A new access file should open that acts as you frontend to your Access 2013 Web App. You can use this frontend to create reports and other frontend forms and tools!
Sign-up for a Free Sharepoint Access 2013 Web App Trial to try it yourself!
Connecting to your Access Web App with SQL Management Studio:
You can use the power of MS Access Services 2013 and Sharepoint to connect to your backend MS Access Web App directly using SQL Server Management Studio. Watch the video above or try the more detailed steps outlined below for an ODBC connection.
1. Go to your Web App in Sharepoint (on accessontheweb.com) and click the “customize in Access” button to download an accdw file.
2. Open and log into your web app by opening the accdw file downloaded from your Access Hosting Sharepoint site.
3. Under Connections, Click Manage and Select Enable Read-Write Permissions. (Note – If you select Enable Read Write Permissions again, you will disable Read-Write Permissions. When you turn on Read Write Permissions again in the future, the password will change.)
4. Once Read-Write Permissions are Enabled, click the Manage button again and select View Read-Write Connection Information and the below dialog box will pop up. You will need to reference the permissions below to setup the ODBC connection. Note the connection information and copy over to notepad.
5. If you don’t already have SQL Native Client 11.0 Driver, you’ll need to download and install them. Install and SQL Native Client 11.0 driver from Microsoft SQL Server 2012 SP1 Feature Pack. Don’t download the entire package, you only need on file which is the sqlncli.msi. You can download the file here: http://www.microsoft.com/en-us/download/details.aspx?id=35580.
6. Once downloaded, double click the file to install the SQL Native Client 11.0 driver.
7. To create the ODBC connection, launch ODBC Data Source Administrator by clicking your Windows start button and search for ODBC. Select the Data Sources (ODBC) file that comes up.
8. Click on Add to create a new data source. Select SQL Server Native Client 11.0 and then click finish.
9. Enter Any Name and Description to describe your ODBC connection. For the Server enter the domain of your Access Hosting SharePoint site but DO NOT enter the http:// (so enter something like example.accessontheweb.com). Click Next.
10. Reference the View Read-Write Connection data that you pasted into Notepad from your Access Web App earlier. Enter the User Name information in the Login ID and the Password in the Password field and click next.
11. Check the box Change the default database to and enter the Database Name from the View Read-Write Connection Information from your Web App and click next. Click Finish.
Sign-up for a Free Sharepoint Access 2013 Web App Trial to try it yourself!
