Click here to Skip to main content
12,243,721 members (55,619 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

28.1K views
9 bookmarked
Posted

Export data from SQL Server to MS Access

, 18 Oct 2011 CPOL
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)

Share

About the Author

Muhammad Shahid Farooq
Software Developer (Senior) ForeVision Business Solutions
Pakistan Pakistan
Software Engineer | System Integrator | Architect

Muhammad Shahid Farooq is a software engineer with having experience of 9+ 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.

You may also be interested in...

Comments and Discussions

 
QuestionNeed Help Pin
mute_gemini5-Mar-14 21:10
membermute_gemini5-Mar-14 21:10 
AnswerRe: Need Help Pin
Muhammad Shahid Farooq9-Mar-14 22:56
professionalMuhammad Shahid Farooq9-Mar-14 22:56 
Questionerror Pin
Mustafa Magdy25-Sep-11 11:38
memberMustafa Magdy25-Sep-11 11:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web01 | 2.8.160426.1 | Last Updated 18 Oct 2011
Article Copyright 2009 by Muhammad Shahid Farooq
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid