SELECT * //---(可以写出字段):(id,name,...) INTO 目的表 ---下面是打开你的源表 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
可以写两个连接, cn连接第一个数据库 table1第一个数据库中的表 cn2连接第二个数据库 table2第二个数据库中的表 select * from table1 set rs=cn.execute(sql) do while not rs.eof insert into table2(fields1,fields2,.....) Values('"& rs.Fields("Fields1") &"','"& rs.Fields("Fields2") &"',......) set rs2=cn2.execute(sql2) rs.movenext loop
标准答案来了: Option Explicit Dim Adocon As ADODB.Connection Private Sub Form_Load() Set Adocon = New ADODB.Connection Adocon.ConnectionString = "Provider = SQLOLEDB.1;Password = ; Persist Security Info = True;User ID = sa;Initial Catalog = DRUGDB; Data Source = 192.168.30.122" Adocon.ConnectionTimeout = 120 Adocon.Open '第一种:MDB导入到SQL Server Adocon.Execute ("delete from newsort") Adocon.Execute ("insert into newsort(sort_id,sort_name) SELECT sort_id,sort_name FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=" & App.Path & "\test.MDB;User ID=Admin;Password=')...carsort") '第二种:MDB导入到MDB Adocon.Execute ("delete from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=" & App.Path & "\test.MDB;User ID=Admin;Password=')...carsort1") Adocon.Execute ("insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=" & App.Path & "\test.MDB;User ID=Admin;Password=')...carsort1(sort_id,sort_name) SELECT sort_id,sort_name FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=" & App.Path & "\test.MDB;User ID=Admin;Password=')...carsort") '第三种:Excel导入到SQL Server Adocon.Execute ("delete from newsort") Adocon.Execute ("insert into newsort(sort_id,sort_name) SELECT sort_id,sort_name FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=" & App.Path & "\carsort1.xls;Extended properties=Excel 5.0')...[carsort1$]") Adocon.Close Set Adocon = Nothing End Sub
http://www.microsoft.com/china/community/Column/31.mspx不同数据库之间互导数据的。
SELECT * //---(可以写出字段):(id,name,...)
INTO 目的表
---下面是打开你的源表
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
Insert Into 目标表 Select * From 源表
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
的意思是从数据源中打开表,第一个参数是数据源驱动,第二个是数据库名,后面依次是用户名,密码,表名
这样的方式读取的表和正常的表的处理方式一样,一样可以用查询语句
cn连接第一个数据库
table1第一个数据库中的表
cn2连接第二个数据库
table2第二个数据库中的表
select * from table1
set rs=cn.execute(sql)
do while not rs.eof
insert into table2(fields1,fields2,.....) Values('"& rs.Fields("Fields1") &"','"& rs.Fields("Fields2") &"',......)
set rs2=cn2.execute(sql2)
rs.movenext
loop
Option Explicit
Dim Adocon As ADODB.Connection
Private Sub Form_Load()
Set Adocon = New ADODB.Connection
Adocon.ConnectionString = "Provider = SQLOLEDB.1;Password = ; Persist Security Info = True;User ID = sa;Initial Catalog = DRUGDB; Data Source = 192.168.30.122"
Adocon.ConnectionTimeout = 120
Adocon.Open
'第一种:MDB导入到SQL Server
Adocon.Execute ("delete from newsort")
Adocon.Execute ("insert into newsort(sort_id,sort_name) SELECT sort_id,sort_name FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=" & App.Path & "\test.MDB;User ID=Admin;Password=')...carsort")
'第二种:MDB导入到MDB
Adocon.Execute ("delete from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=" & App.Path & "\test.MDB;User ID=Admin;Password=')...carsort1")
Adocon.Execute ("insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=" & App.Path & "\test.MDB;User ID=Admin;Password=')...carsort1(sort_id,sort_name) SELECT sort_id,sort_name FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=" & App.Path & "\test.MDB;User ID=Admin;Password=')...carsort")
'第三种:Excel导入到SQL Server
Adocon.Execute ("delete from newsort")
Adocon.Execute ("insert into newsort(sort_id,sort_name) SELECT sort_id,sort_name FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=" & App.Path & "\carsort1.xls;Extended properties=Excel 5.0')...[carsort1$]")
Adocon.Close
Set Adocon = Nothing
End Sub