Export Access web app tables to an Access desktop database

Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013.

This article examines how to export Access web app tables to an Access desktop database as local tables. To achieve this task, you’ll enter the connection information to your Access web app tables, devise an ODBC Data Source Name (DSN) to connect to the Access web app tables, and then import the data from the web app tables into local tables in an Access desktop database. Listed below are the necessary steps you must follow to complete this task.

Enable Connections for your Access Web App

  1. Enter the Access web app in the Access client. If you’re unsure how to open your Access web app in Access client, see Modify an Access web app.
  1. Pick File > Info > Manage within the Connections group.
  1. Select Enable Read-Write Connection to open connections to your Access web app tables.

  1. Choose View Read-Write Connection Information to check all the needed connection information.

  1. Copy and paste the Server, Database, User Name and Password values into Notepad or Word, or put the Connection Information window to one side so you can copy and paste the information from it at a later time.

Create an ODBC Data Source Name (DSN) to Connect to the Access web app on SQL

  1. Select the ODBC Data Source Administrator tool on your computer or device. There are countless methods to enter this tool so to prevent confusion, here are the corresponding executable locations for the bitness of DSN you are seeking 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 reflect the bitness of your Office installation. %windir% usually refers to the default location of Windows, or c:\windows

  1. Press Add on the User DSN or System DSN tab of the ODBC Data Source Administrator dialogue.
  1. Pick the SQL Server Native Client 11.0 driver and then choose Finish.

  1. In the Name text box, type a name you want for this new DSN.

  1. In the Server text box, state the server name you collected earlier from the Access web app connection information.

  1. Pick Next to continue.

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

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

  1. Select Next to continue.

  1. Pick the Change the default database to checkbox.

  1. Paste the database name from the Access web app connection information you saved earlier into the Database Name text box where it now lists (Default). Choose 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.

  1. Select Finish on the last page of the wizard.

  1. If you want to play around with the connection information at this stage, select Test Data Source on the ODBC Microsoft SQL Server dialogue box. Pick OK when you’re finished.

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

Now that you’ve developed an ODBC DSN, you’re able to use that connection information from several 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 check its help documentation on using ODBC connections.

  1. Design a new Access desktop database or open an established Access desktop database.
  1. On the ribbon, select External Data > ODBC Database within the Import & Link group.
  1. On the Get External Data – ODBC Database dialogue box, select Import the source data into a new table in the current database and then press 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 dialogue box.

  1. In the Select Data Source dialogue box, choose the Machine Data Source tab.
  1. Click the ODBC DSN that you formed earlier and then tap OK.
  1. In the Password text box on the SQL Server Login dialogue box, type the password from the Access web app connection information and then press OK.
  1. Pick 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.

  1. Press OK to start the import process.
  1. After Access completes the import process, select Close to close the import dialogue box.
  1. You now have a local table in an Access desktop database for all tables in your Access web app.

Leave a Reply

%d bloggers like this: