1.关于配置
--启用Ad Hoc Distributed Queries
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--使用完成后,关闭Ad Hoc Distributed Queries:
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
2.T-SQL(其中AAAA为表名)
--查询ACCESS表里的数据
SELECT * FROM OPENDATASOURCE ('Microsoft.ace.OLEDB.12.0',
'Data Source="F:/CC.accdb";User ID=Admin;Password=;') ...AAAA
--插入到一个新表中,此表不存在
SELECT * INTO NEWTABLE FROM OPENDATASOURCE ('Microsoft.ace.OLEDB.12.0',
'Data Source="F:/CC.accdb";User ID=Admin;Password=;') ...AAAA
--将SQL SERVER表里的数据插入到ACCESS
--
insert into OpenDataSource( 'Microsoft.ace.OLEDB.12.0',