Import data from the SQL Server database to the Access Database

General data import and export:
Use the DTS wizard to migrate your access data to SQL Server. You can use these steps:
1) on the Tools menu in SQL Server Enterprise Manager, select data transformation
2) services (data conversion service), and then select czdimport data (import data ).
3) In the choose a data source dialog box, select Microsoft Access as the source, and then type your. mdb database (. MDB File Extension) or browse to find the file.
4) In the choose a destination (select target) dialog box, select Microsoft ole db prov ider for SQL Server, select database server, and click the required authentication method.
5) in the specify table copy or query dialog box, click Copy tables ).
6) in the select Source tables dialog box, click Select All ). Next, complete.

Use a Transact-SQL statement to import and export data:
1. query access data in SQL Server:

Select * From OpenDataSource ('Microsoft. Jet. oledb.4.0 ', 'Data source = "C:/DB. mdb"; user id = admin; Password =')... table name

2. Import access to SQL Server
Run in SQL Server:
Select * into newtable from OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "C:/DB. mdb "; user id = admin; Password = ')... table Name

3. insert data in the SQL Server table to the Access Table
Run in SQL Server:
Insert into OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "C:/DB. mdb "; user id = admin; Password = ')... table Name (column name 1, column name 2) Select column name 1, column name 2 from SQL table

Instance:
Insert into OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'c:/DB. mdb'; 'admin'; '', test) Select ID, name from test

Insert into OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'c:/trade. mdb'; 'admin'; '', table name) Select * From sqltablename