环境:office Access 2010+SQLServer2008R2+Windows Server 2008R2
方式:Transact-SQL
方法:
1. 在SQL SERVER里查询access数据:
1 SELECT * 2 3 FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0', 4 5 'Data Source="c:\DB.mdb";User ID=;Password=')...表名
2. 将access导入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=' )...表名
3. 将SQL SERVER表里的数据插入到Access表中
1 insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 2 3 'Data Source=" c:\DB.mdb";User ID=;Password=')...表名 4 5 (列名1,列名2) 6 7 select 列名1,列名2 from sql表
问题:
1、如何保持标识列值不变:
有自动增长列时,插入会提示:仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时,才能为表'XXX'中的标识列指定显式值.
1 set IDENTITY_INSERT [INFO] ON 2 3 INSERT INTO [sdedb].[dbo].[INFO] 4 ([ID] 5 ,[NAME]) 6 SELECT 7 [ID] 8 ,[NAME] 9 FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0', 10 'Data Source="c:\DB.ACCDB";User ID=;Password=' )...[INFO]; 11 set IDENTITY_INSERT [INFO] off 12 GO
2、SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问
运行以下SQL语句启用Ad Hoc Distributed Queries:
1 exec sp_configure 'show advanced options',1 2 reconfigure 3 exec sp_configure 'Ad Hoc Distributed Queries',1 4 reconfigure
运行以下SQL语句关闭Ad Hoc Distributed Queries:
1 exec sp_configure 'Ad Hoc Distributed Queries',0 2 reconfigure 3 exec sp_configure 'show advanced options',0 4 reconfigure
3、安装AccessDatabaseEngine_X64.exe,不然无法找到数据库引擎