Click here to Skip to main content
Click here to Skip to main content

Export data from SQL Server to MS Access

, 18 Oct 2011
Rate this:
Please Sign up or sign in to vote.
The purpose of this article is to provide a fast but easy way to 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Muhammad Shahid Farooq
Software Developer (Senior)
Pakistan Pakistan
Software Engineer | System Integrator | Architect
 
Muhammad Shahid Farooq is a software engineer with having experience of 6+ years of working on ADO.Net, C#.Net, VB.Net, VB6/VBA, SQL Server, Oracle, MySQL and other latest technologies. He holds Bachelor’s degree in Computer Science from the University of Central Punjab, Lahore, Pakistan. To contact Shahid, email him at muhammadshahidfarooq@yahoo.com.

Comments and Discussions

 
QuestionNeed Help Pinmembermute_gemini5-Mar-14 20:10 
AnswerRe: Need Help PinprofessionalMuhammad Shahid Farooq9-Mar-14 21:56 
Questionerror PinmemberMustafa Magdy25-Sep-11 10:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 18 Oct 2011
Article Copyright 2009 by Muhammad Shahid Farooq
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid