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. {codecitation class="brush: sql; gutter: true;" width="500px"}
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'C:\Test.mdb';'Admin';'', 'Select * From ‘AccessTableName’) Select * From ‘SQLTableName’ {/codecitation} 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' }
)
History
Initial Version - November 26, 2008. Reference
MSDN Library. About the Author
Muhammad Shahid Farooq is a software engineer with having experience of 2+ years of working on C#, Java, SQL, VB.Net, VB 6 and other latest technologies. He holds Bachelor’s degree in Computer Science from the University of Central Punjab, Lahore, Pakistan.
|