笔记“SQL与Access”
Transact-SQL语句进行导入导出:
- 1. 在SQL SERVER里查询access数据:
-- ======================================================
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\DB.mdb";User ID=Admin;Password=')...表名
-------------------------------------------------------------------------------------------------
- 2. 将access导入SQL server
-- ======================================================
在SQL SERVER 里运行:
SELECT *
INTO newtable
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\DB.mdb";User ID=Admin;Password=' )...表名
-------------------------------------------------------------------------------------------------
- 3. 将SQL SERVER表里的s数据插入到Access表中
-- ======================================================
在SQL SERVER 里运行:
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source=" c:\DB.mdb";User ID=Admin;Password=')...表名
(列名1,列名2)
select 列名1,列名2 from sql表
总结:在SQL打开Access数据库用opendatasource();