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.

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.

Step-III:

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

Step-IV:

Select SQL Server Native Client 11.0 from the Data source drop down menu.
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).
Select Database from the Database drop down menu.
Click on the Next button.

Step-V:

Select the Microsoft Access (Microsoft Jet Database Engine) from the Destination drop down menu.
Browse the access database where you want to export the table.
Click on Next button.

Step-VI:

Select Copy data from one or more tables or views if you want to export table or view completely.
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).

Step-VII:

Select the table which you want to export from Select Source Tables and Views window.
Click on Next button.

Step-VIII:

Step-IX:

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.
Click on Next button.