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.