Share via

How to: Make external connections to an Access Web App

  • Article

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