Recently I worked on a requirement where we had to export data from few tables in SQL Server 2008 R2 to Microsoft Access database. Initially it seemed to be a simple request, but the challenge we faced was, there was no MSAccess database available or MSAccess software installed on any of the servers.
So, first we had to work on creating a MSAccess database and then export the data from SQL Server to MSAccess database.
How to Create MSAccess database when Microsoft Access is not installed on any of the servers?
Follow the below steps to create MSAccess database
1. Click Start -> All Programs – Administrative Tools -> Click on Data Sources (ODBC) tool which will open a window.
2. Click Add button -> Then you get an option to choose a driver there we need to select Microsoft Access related driver.
3. Click Finish
4. Now, we will be on Microsoft Access Setup window
5. Click on Create button
6. Choose the path where you want to save the MSAccess database physical file and provide a name to the database.
7. Click OK, which will give us a message that the new MsAccess database with the specified name has been created.
8. Browse to the path and we will be able to see the physical MSAccess database file.
Now, next step is to Export the data from SQL Server Database to newly created MSAccess database. Follow below steps to export the data
1. Connect to the SQL Server instance from SQL Server Management Studio.
2. Right click on the databases where the tables exist which you want to export -> Select Tasks -> Export Data, which will open a Export Wizard
3. Choose Source Server details and click next
Data Source – Leave default
Server Name – Choose the SQL Server instance name where tables exist
Select the authentication type
Make sure database name is correctly listed
4. Choose Destination Server details
Destination – Select Microsoft Access related driver
FilaName – Browse to the path and selected the MSAccess database which we created
5. Click On Advanced button and click Test Connection to make sure connection can be made to the Access database file
6. Click OK and Click Next
7. Select the radio button “Copy data from one or more tables or views” and click next
8. Select the tables which you want to export and once all required tables are selected, click next
9. Select the check box “execute immediately” and click next
10. Click Finish which will export the data from SQL Server database tables to the MSAccess database file.
Now, we can move the MSAcccess database file to other server to access the data