Export data from SQL Server to MS Access

Introduction

Exporting data from SQL Server to MS Access can be achieved in a variety of ways. The purpose of this article is to provide a fast but easy way to export data from SQL Server to Access.

Background

 

You must know how the select & insert statements work in SQL Server.

 

Using the Script

 

Create an access file named ‘Test’ on C:\ drive having the table and their columns same as that of SQL and use this script.

 

The script below is amazingly simple to use.

 

INSERT INTO OPENROWSET 
 
('Microsoft.Jet.OLEDB.4.0', 
 
'C:\Test.mdb';'Admin';'', 
 
'Select * From ‘AccessTableName’) 
 
Select * From ‘SQLTableName’

 

The OPENROWSET function in SQL Server provides a way to open an OLE DB compatible data source (i.e. MS Access in our case) directly from your SQL script.

 

Syntax

 

OPENROWSET ( 'provider_name' 
, { 'datasource' ; 'user_id' ; 'password' 
| 'provider_string' } 
, { [ catalog. ] [ schema. ] object 
| 'query' } 
) 

 

Reference

 

MSDN Library.