How to: Make external connections to an Access Web App


You may want to access the data stored in your Access Web app from other applications. An Access Web App stores its objects and data in a SQL Azure database for SharePoint Online instances or in a local SQL Server for SharePoint on-premise installations. In order to connect to the Access Web app data on SQL, you need to use the SQL Server Native Client 11.0 ODBC driver and provide connection information from the Access Web App. Below are the steps to make the most commonly requested connections.

Note: If you experience connection issues after following these instructions, you will want to ensure your firewall is not blocking port 1433. See Security Guidelines for Azure SQL Database for more details.

Prerequisite: Download and install the SQL Native Client 11.0 driver from the Microsoft® SQL Server® 2012 SP1 Feature Pack. Within the download details, the only file you need to select is the sqlncli.msi.

Enable Connections for your Access Web App

  1. Open the Access Web App in the Access client
  2. Click the File tab > Info > click Manage within the Connections group
  3. If your Access App is on SharePoint Online, select From Any Location or From My Location to allow connections. Note: If you are on an IPv6 network, you will only have the option to connect From Any Location. In an on-premise installation, these options are not available.
  4. Select either Enable Read-Only Connection or Enable Read-Write Connection
  5. Depending on the previous selection, select either View Read-Only Connection Information or View Read-Write Connection Information
  6. Copy and paste the Server, Database, UserName and Password values into Notepad or Word, or leave the Connection Information window open so you can copy and paste the information from it later.

Create an ODBC Data Source Name (DSN) to Connect to the Access Web App on SQL

  1. Open the ODBC Data Source Administrator tool. There are many methods to access this tool so to eliminate confusion, I’ll provide the corresponding executable for the bitness of DSN you are looking to create. Note:The DSN bitness should match the bitness of your Office installation.

     

    Windows 32bit (x86) / Office 32bit (x86): %windir%\System32\odbcad32.exe

    Windows 64bit (x64) / Office 64bit (x64): %windir%\System32\odbcac32.exe

    Windows 64bit (x64) / Office 32bit (x86): %windir%\SysWow64\odbcad32.exe

     

    Note: %windir% typically points to the default location of Windows, or c:\windows

     

  2. On the User DSN or System DSN tab of the ODBC Data Source Administrator dialog, click Add
  3. Select the SQL Server Native Client 11.0 driver, click Finish
  4. Provide any name you like for this new DSN
  5. Enter the Server information gathered earlier from the Access Web App connection information, click Next
  6. Select the With SQL Server authentication… option
  7. Paste the UserName and Password from the Access Web App connection information, click Next
  8. Select the ‘Change the default database to’ checkbox
  9. Paste the database name* from the Access Web App connection information. Click Next

    *Do not click the down arrow for the database name if you are connecting to a database on SQL Azure as you will not be able to view the list of available databases and will encounter an error. It’s best to copy/paste your specific database name into this field.

  10. Click Finish, click Ok

Use the DSN within an Access desktop database

Now that you have created an ODBC DSN, you are able to use that connection information from many applications. Here are the steps to use this ODBC DSN to create linked tables within a traditional Access desktop database. For other applications, you will want to seek its help documentation on using ODBC connections.

  1. Create a new or open an existing Access desktop database
  2. On the ribbon, click the External Data tab
  3. Within the Import & Link group, click ODBC Database
  4. Select ‘Link to the data source by creating a linked table’, click Ok
  5. In the ‘Select Data Source’ dialog, click the Machine Data Source tab
  6. Select the ODBC DSN that you created, click Ok
  7. Provide the password from the Access Web App connection information, click Ok
  8. Select the tables you wish to link (Note: The tables named Access.<tablename> are the ones that contain the data.)
  9. Check the option Save Password if desired. This stores the password in the database file, but prevents you from having to enter the password each time you open the database
  10. Click Ok
  11. You should now see the linked table(s) in your desktop database (Note: You may rename the tables in the Access desktop database and remove the ‘Access_’ from the name.
  12. Doing so will not change the table names in the SQL database.)

[View:~/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-01-89/AccessAppExternalConnections.mp4:0:0]

Using SQL Server Management Studio (SSMS) to Connect to the Access App on SQL

In order to connect to the Access Web App from SSMS, you do not need to create an ODBC DSN, but you will need to first enable external connections by completing the section "Enable Connections for your Access Web App" above.

  1. Open SQL Server Management Studio
  2. In the 'Connect to Server’ dialog, use the connection information from the Access Web App to specify the server name
  3. Change the Authentication to ‘SQL Server Authentication’
  4. Provide the login and password also using the Access Web App connection information
  5. Click the Options >> button
  6. Select the Connection Properties tab
  7. Paste the database name using the Access Web App connection information for the ‘Connect to database’ option. *Do not use the dropdown arrow to attempt to locate the database name
Comments (19)

  1. Andrey Artemyev says:

    Just in case someone’s looking for a VBA way of creating a linked table to Access Web App data, here it is http://social.msdn.microsoft.com/Forums/office/en-US/0caa8980-1599-4a86-a7c3-c52fe3393db9/linking-my-webapp-tables-to-a-desktop-app

  2. Anonymous says:

    I can connect to my db with SSMS (2008 R2) OK but with 2012 (Developer edition) I get the error –

    "A connection was successfully established with the server, but then an error occurred during the login process (provider:SSL Provider, error: 0 – The message received was unexpected or badly formatted.)"

    and then SS crashes. No problem setting up a DSN or using DSNless connections in an ACCDB. Any ideas?

  3. Albert D. Kallal says:

    Thanks for this post. About the only tip I can add is if one launches the ODBC manager from inside of Access by selecting the External Data tab on ribbon and then clicking on ODBC then Access will “always” launch the correct bit version of the ODBC manager to create the DSN.

    Best regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

  4. mike says:

    I try to make a BCS connection to this database in SharePoint online. I struggle a bit. Can you help me out?

  5. jerry says:

    Can you provide any information on how to access the SQL instance that is generated with the installation of an Access Web Application that is published and then consumed via the App Store? I have a published application and am unable to locate information
    on how to connect to the SQL instance that is populated via the Access Web App.

    Thank you in advance!

  6. Joana Villas-Boas says:

    Is it possible to change the username auto-generated by access to read/right access to the Database?
    Is it possible to connect a Azure Subscritpion to the SQL Database generated by Access?

  7. Stiven says:

    Anyone know how I can connect to my Access Web app data from SharePoint Online?

  8. Prakash says:

    Hi is there a way to access this Access Web APP using C# or REST API

  9. Edward Moore says:

    Has anyone received answers to their questions? I can repeat a few of them, but if no one received answers over this many months I don’t see the point.

    rather disappointing.

  10. Mike Anderson says:

    This doesn’t work for my SharePoint.com Access Web database. I’m using the ReadWrite data connections information from my DB as shown in the video, and it won’t work for me.

    HOWEVER, if I use "Report on my database" I can then use the linked tables, etc. to create reports. The problem with this solution, however, is that saving the file as an ACCDE and having my users use the database via Access Reader app (downloaded from MS)
    – my users get the error ‘ODBC–connection to SQL Server Native Client 11.0(some name).database.windows.net’ failed. You’d think that the self-generated connection (that works for ME across multiple computers, but nobody else) would also work in the ACCDE
    file. But no, I guess not.

  11. Hi Mike,

    When you choose the "Report on my Data" option external connections will also be enabled with the default options of "From My Location – Allow connections to the database from this location" and "Enable Read-Only Connection". If you want to share the reporting
    database, you would need to change the "From My Location" option to "From Any Location". These connection options are located just below the "Report on my Data" button within the Manage – Connections button.

  12. Connection is great but image data type is not working. Every other datatype is working but not the image data type.
    If you create a form on the linked table, you will see that the image datatype is represented as number datatype.

  13. Roland Wales says:

    I have Access 2010, and my File tab > Info has no option to manage connections. The only 3 options are “send and receive changes with the server”, “application log”, and “compact and repair”. What is going wrong, that I can’t follow the directions and get the results you describe above?

    Thanks,

    1. Slack says:

      From what I’ve been able to find out, this does NOT work with an MS Access 2010 DB published to SharePoint 2010 or 2013. If I use an MS Access 2013 published to SP 2013, I can get the same screens as shown. Also here is another more detailed set of instructions I was able to find for Access 2013 published on SP 2010/2013.

  14. Kevin says:

    I successfully attempted this the first time I tried. I wanted to move my access database to a new SharePoint site. I followed the instructions at this site: https://blogs.office.com/2012/09/27/moving-and-backing-up-your-access-2013-web-apps/

    When I went through this websites steps again, I got got the following error when clicking “Test Data Source”:

    Attempting connection
    [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open server ‘serverx’ requested by the login. Client with IP address ‘xx.xx.xxx.xxx’ is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.

    TESTS FAILED! ”

    I tried adding my IP address to the Azure Management Portal without success. Not sure I did it right.

  15. When I try to connect from SSMS I get to login to Azure and when I enter my Office365 credentials I get an error that says I don’t have an Azure subscription.
    Is this an accepted behaviour or can someone please help overcome this issue?
    Thanks,
    Anton

  16. Banji says:

    I developed an Access Web App, which I want to make available to a number of departments in the company. Each department has it’s own SharePoint Site. I’d like to publish the App via a link from each department’s website . However, I want to filter the records the end user sees, based on their department SharePoint the request is coming from. is this possible using URL Query strings or via macros?

    1. You would either need to choose a single SharePoint site to publish the app and then add hyperlinks to that app within the other department sites or you could add the app to your app catalog and create an separate instance of the app within each department’s site. You would have to consider whether the users have permissions to the SharePoint site where the app is instantiated and if you want a single database with all of the data or if it’s preferable for each department to have their own database. Regardless of where the app is published, you are able to restrict records using the UserEmailAddress or UserDisplayName functions. See post: https://blogs.technet.microsoft.com/the_microsoft_access_support_team_blog/2015/12/31/access-app-restrict-editing-records-by-user/

Skip to main content