Access data import sqlserver2008r2

Environment: Office Access 2010+sqlserver2008r2+windows Server 2008R2

MODE: Transact-SQL

Method:

1. Query access data in SQL Server:

1 SELECT * 2 3  from OpenDataSource ' microsoft.ace.oledb.12.0 ' , 4 5 ' Data source= "C:\DB.mdb"; User id=; password=') ... Table name 　　 　　 　　

2. Import access to SQL Server

1 SELECT * 2 3  into newtable 4 5  from opendatasource ('microsoft.ace.oledb.12.0',6 7 ' Data source= "C:\DB.mdb"; User id=; password=' ) ... Table name 　　 　　 　　 　　

3. Insert data from the SQL Server table into an Access table

1 Insert  into OpenDataSource ' microsoft.jet.oledb.4.0 ' , 2 3 ' Data source= "C:\DB.mdb"; User id=; password=') ... Table name 45  (column name 1, column name 2)67Select from SQL table 　 　　 　　

Problem:

1. How to keep the identity column values intact:

When there is an auto-grow column, the insert prompts: You can specify an explicit value for the Identity column in table ' XXX ' only if the column list is used and Identity_insert is on.

1  　　　　 Set Identity_insert [INFO]  on2 3  　　　　 INSERT  into [Sdedb].[dbo].[INFO] 4([ID] 5,[NAME])6  　　　　 SELECT7  　　　　 [ID] 8,[NAME]9  　　　　  from OpenDataSource('microsoft.ace.oledb.12.0',Ten  　　　　 'Data source= "C:\DB. ACCDB "; User id=; password=')...[INFO]; One  　　　　 Set Identity_insert [INFO] off A GO

View Code

2. SQL Server blocked access to the STATEMENT ' Openrowset/opendatasource ' of the component ' Ad Hoc distributed Queries '

Run the following SQL statement to enable AD Hoc distributed Queries:

1 exec ' Show advanced Options ',12reconfigure3exec'Ad Hoc Distributed Queries',14reconfigure

Run the following SQL statement to close the ad Hoc distributed Queries:

1 exec ' Ad Hoc Distributed Queries ',02reconfigure3exec'Show advanced options  ',04reconfigure

3, install Accessdatabaseengine_x64.exe, otherwise cannot find the database engine

Access data import sqlserver2008r2