Sql Server 2000 直接操作 Access 数据

/************************************
Author: William
DateTime: 2009-08-16
导数据库问题。
*****************  users ***********************/

/******************************************
Step 1：  插入 users 数据
*********************************************/

insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:/DB2.mdb";User ID=Admin;Password=')...users
(loginName,[Name],workgroupshareUser,
AccountRestrictionSettingsAllowUseOfWebMail,
AccountRestrictionSettingsAllowUseOfIMAP,
AccountRestrictionSettingsCanSendToOutsideWorld,
AccountRestrictionSettingsCanReceiveFromOutsideWorld
)

SELECT loginName,[Name],workgroupshareUser,
AccountRestrictionSettingsAllowUseOfWebMail,
AccountRestrictionSettingsAllowUseOfIMAP,
AccountRestrictionSettingsCanSendToOutsideWorld,
AccountRestrictionSettingsCanReceiveFromOutsideWorld
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:/DB1.mdb";User ID=Admin;Password=')...users
where loginName in (.com','')

/******
Step 2：  插入 Folders 数据
 A. top N, 选择插入几条记录
        B. 修改owner值 = ID值
****/
-- (A)

insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:/DB2.mdb";User ID=Admin;Password=')...Folders
(--ID,
Name,Parent,Owner,ItemTable,SystemFolder,InheritParentAccess,SpecialType,LastItemUpdate)

SELECT top 2 'Contacts',0,0, 'Contacts',1,0,null,getdate() 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:/DB1.mdb";User ID=Admin;Password=')...Folders  

-- （B）
update OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:/DB2.mdb";User ID=Admin;Password=')...Folders
 set owner = id

select * from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:/DB2.mdb";User ID=Admin;Password=')...Folders order by 1 desc

/******
Step 3：  插入 Contacts 数据
 A. top N, 选择插入几条记录
        B. 修改owner值 = ID值
****/

insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:/DB2.mdb";User ID=Admin;Password=')...Contacts
(--ID,
Folder,owner,[name],firstName,lastName,company,department,created,distlist,MessageClass,modified,private )

SELECT
--id,
--folder,owner,[name],firstName,lastName,company,department,created,distlist,MessageClass,modified,private
'1087','1087',[name],firstName,lastName,company,department,created,distlist,MessageClass,modified,private
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:/DB1.mdb";User ID=Admin;Password=')...Contacts 
where owner in (
select Id FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:/DB1.mdb";User ID=Admin;Password=')...users
where loginName in ('XXX') -- ,'XX@XXX.com'
)