Export Access web app tables to an Access desktop database

Enable Connections for your Access Web App

1. Open the Access web app in the Access client. If you're not sure how to open your Access web app in Access client, see Modify an Access web app.

2. Click File > Info > Manage within the Connections group.

3. Click Enable Read-Write Connection to open connections to your Access web app tables.

4. Click View Read-Write Connection Information to view all the needed connection information.

5. Copy and paste the Server, Database, User Name 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 on your computer or device. There are many methods to access this tool so to eliminate confusion, here are the corresponding executable locations for the bitness of DSN you are looking to create.

   • 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: The DSN bitness should match the bitness of your Office installation. %windir% typically points to the default location of Windows, or c:\windows

2. Click Add on the User DSN or System DSN tab of the ODBC Data Source Administrator dialog.

3. Select the SQL Server Native Client 11.0 driver and then click Finish.

4. In the Name text box, enter a name you want for this new DSN.

5. In the Server text box, enter the server name you gathered earlier from the Access web app connection information.

6. Click Next to continue.

7. Select the With SQL Server authentication using a login ID and password entered by the user option.

8. Paste the User Name and Password from the Access web app connection information you saved earlier into the Login ID and Password text boxes.

9. Click Next to continue.

10. Select the Change the default database to checkbox.

11. Paste the database name from the Access web app connection information you saved earlier into the Database Name text box where it currently lists (Default). Click Next to continue.

    Important: Don't click the down arrow for the database name if you are connecting to a database on SQL Azure (Microsoft 365 or SharePoint Online) since you won't be able to view the list of available databases and you'll encounter an error. It’s best to copy and paste your specific database name into this field.

12. Click Finish on the last page of the wizard.

13. If you want to test the connection information at this point, click Test Data Source on the ODBC Microsoft SQL Server dialog box. Click OK when you're finished.

Use the DSN within an Access desktop database to import the Access web app tables

Now that you've created an ODBC DSN, you're able to use that connection information from many applications. Here are the steps to use this ODBC DSN to import tables from your Access web app into an Access desktop database. For other applications, you'll want to seek its help documentation on using ODBC connections.

1. Create a new Access desktop database or open an existing Access desktop database.

2. On the ribbon, click External Data > ODBC Database within the Import & Link group.

3. On the Get External Data - ODBC Database dialog box, select Import the source data into a new table in the current database and then click OK.

   Tip: If you wanted to just link to the Access web app tables from within your Access desktop database, select Link to the data source by creating a linked table on the Get External Data - ODBC Database dialog box.

4. In the Select Data Source dialog box, click the Machine Data Source tab.

5. Select the ODBC DSN that you created earlier and then click OK.

6. In the Password text box on the SQL Server Login dialog box, enter the password from the Access web app connection information and then click OK.

7. Select all the web app tables you want to import into your Access desktop database.

   Note: The tables named Access.<tablename> are the ones that contain the data from your Access web app. The tables named Access.<tablename>?Images contain any image data from your Access web app.

8. Click OK to start the import process.

9. Once Access finishes the import process, click Close to close the import dialog box.

10. You now have a local table in an Access desktop database for each table in your Access web app.