You are currently viewing How to Export SQL Server Table To Access Database

How to Export SQL Server Table To Access Database

How to Export SQL Server Table To Access Database

In this article, we will learn how to export table data from SQL database to MS Access database (.mdb) file using SQL Server Management Studio.

First of all, convert .accdb access file to .mdb file. Then follow the following steps to export SQL Server table to MS Access .mdb file.

Step-I: Open SQL Server Management Studio and click on Connect button as shown in the following Image.

Open MSSQL

Step-II:

  • Right-click on the database from where you want to export table data. In this demo, we will use AdventureWorks2014 database.
  • Move cursor to Tasks.
  • Click on Export Data… from the pop-up menu.
Export SQL Table to Access mdb 2

Step-III:

SQL Server Import and Export Wizard will open. Click on Next button.

Step-IV:

  1. Select SQL Server Native Client 11.0 from the Data source drop down menu.
  2. Select Server name from the Server name drop down menu. If you are exporting data from the server where SQL Server instance is installed then type (local) or put .(dot).
  3. Select Database from the Database drop down menu.
  4. Click on the Next button.
Export SQL Table to Access mdb 4

Step-V:

  1. Select the Microsoft Access (Microsoft Jet Database Engine) from the Destination drop down menu.
  2. Browse the access database where you want to export the table.
  3. Click on Next button.
Export SQL Table to Access mdb 5

Step-VI:

  1. Select Copy data from one or more tables or views if you want to export table or view completely.
  2. Select Write a query to specify the data to transfer (You can export whole table data or filter the data using WHERE clause in the query).
  • Click on Next button.
Export SQL Table to Access mdb 6

Step-VII:

  1. Select the table which you want to export from Select Source Tables and Views window.
  2. Click on Next button.
Export SQL Table to Access mdb 7

Step-VIII:

  1. Review the Table in Source and Destination tabs as shown below.
  2. Click on Next button.
Export SQL Table to Access mdb 9

Step-IX:

  1. Check the Run immediately check box if you want to run it without saving the SSIS Package. You can also save the SSIS Package by putting the check mark on Save SSIS Package check box.
  2. Click on Next button.
Export SQL Table to Access mdb 10

Step-X:

  1. Click on Next button.
Export SQL Table to Access mdb 11

Step-XI:

  1. The execution successfully completed.
  2. Total number of rows exported will be shown.
  3. Click the Close button.
Export SQL Table to Access mdb 12

The data will be exported successfully which you can see by opening the Access Database as shown below.