用代码实现,给你个例子,我的是access 你修改一下 strsql = "select * from jhtz_table " Set rs = ExecuteSQL(strsql, msgtext)
With CommonDialog1 .DialogTitle = "从Excel导出" & Text1.Text & "数据" .FileName = "*.xls" .Filter = "(Excel)*.xls|*.xls" .CancelError = True .ShowOpen End With
strcnn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source=" & CommonDialog1.FileName & ";Extended Properties='Excel 8.0;HDR=Yes'" Set cnn = New ADODB.Connection cnn.Open strcnn strsql_db = "select * from [sheet1$]" cnn.Execute strsql_db Set rs_db = New ADODB.Recordset rs_db.Open strsql_db, cnn, adOpenKeyset, adLockOptimistic
For j = 1 To rs_db.RecordCount
rs.AddNew rs.Fields("jh_dwbm") = rs_db.Fields(0) rs.Fields("jh_xh") = rs_db.Fields(1) rs.Fields("jh_sbmc") = rs_db.Fields(2) rs.Fields("jh_gg") = rs_db.Fields(3) rs.Fields("jh_dw") = rs_db.Fields(4) If IsNull(rs_db.Fields(5)) Then rs.Fields("jh_dj") = 0 Else rs.Fields("jh_dj") = Format(rs_db.Fields(5), "0.00") End If If IsNull(rs_db.Fields(6)) Then rs.Fields("jh_sl") = 0 Else rs.Fields("jh_sl") = rs_db.Fields(6) End If If IsNull(rs_db.Fields(7)) Then rs.Fields("jh_bfsl") = 0 Else rs.Fields("jh_bfsl") = rs_db.Fields(7) End If If IsNull(rs_db.Fields(8)) Then rs.Fields("jh_btsl") = 0 Else rs.Fields("jh_btsl") = rs_db.Fields(8) End If If IsNull(rs_db.Fields(9)) Then rs.Fields("jh_ptsl") = 0 Else rs.Fields("jh_ptsl") = rs_db.Fields(9) End If If IsNull(rs_db.Fields(10)) Then rs.Fields("jh_je") = 0 Else rs.Fields("jh_je") = Format(rs_db.Fields(10), "0.00") End If If IsNull(rs_db.Fields(11)) Then rs.Fields("jh_bfje") = 0 Else rs.Fields("jh_bfje") = Format(rs_db.Fields(11), "0.00") End If If IsNull(rs_db.Fields(12)) Then rs.Fields("jh_btje") = 0 Else rs.Fields("jh_btje") = Format(rs_db.Fields(12), "0.00") End If If IsNull(rs_db.Fields(13)) Then rs.Fields("jh_ptje") = 0 Else rs.Fields("jh_ptje") = Format(rs_db.Fields(13), "0.00") End If rs.Fields("jh_fl") = rs_db.Fields(14) rs.Fields("jh_cj") = rs_db.Fields(15) rs.Fields("jh_hb") = rs_db.Fields(16) rs.Fields("jh_bz") = rs_db.Fields(17) rs.Fields("jh_year") = rs_db.Fields(18) rs.Fields("jh_pc") = Combo1.Text rs.Update rs_db.MoveNext Next j MsgBox "导入成功,共有" & rs_db.RecordCount & "条记录!", 48, "信息" Exit Sub类模块中 Dim msgtext As String Dim mrc As ADODB.RecordsetPublic Function ExecuteSQL(ByVal sql As String, MsgString As String) As ADODB.Recordset Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim sTokens() As String 'Dim SQL As String On Error GoTo ExecuteSQL_Error sTokens = Split(sql) Set cnn = New ADODB.Connection cnn.Open ConnectString If InStr("INSERT,DELETE,UPDATE", UCase$(sTokens(0))) Then cnn.Execute sql MsgString = sTokens(0) & "query successful" Else Set rst = New ADODB.Recordset rst.Open Trim$(sql), cnn, adOpenKeyset, adLockOptimistic
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & "条纪录" End If ExecuteSQL_Exit: Set rst = Nothing Exit Function Set cnn = Nothing ExecuteSQL_Error: MsgString = "查询错误:" & Err.Description Resume ExecuteSQL_Exit End FunctionPublic Function ConnectString() As String ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\计划管理系统.mdb;Persist Security Info=False" 'ConnectString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=aa;Data Source=RJB-CL" End Function
最笨的方法,连接两个数据源,用sql语句一对一的鼓捣就是了
好像sql的数据导入工具可以进行该操作吧
他用的是DTS
strsql = "select * from jhtz_table "
Set rs = ExecuteSQL(strsql, msgtext)
With CommonDialog1
.DialogTitle = "从Excel导出" & Text1.Text & "数据"
.FileName = "*.xls"
.Filter = "(Excel)*.xls|*.xls"
.CancelError = True
.ShowOpen
End With
strcnn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source=" & CommonDialog1.FileName & ";Extended Properties='Excel 8.0;HDR=Yes'"
Set cnn = New ADODB.Connection
cnn.Open strcnn
strsql_db = "select * from [sheet1$]"
cnn.Execute strsql_db
Set rs_db = New ADODB.Recordset
rs_db.Open strsql_db, cnn, adOpenKeyset, adLockOptimistic
For j = 1 To rs_db.RecordCount
rs.AddNew
rs.Fields("jh_dwbm") = rs_db.Fields(0)
rs.Fields("jh_xh") = rs_db.Fields(1)
rs.Fields("jh_sbmc") = rs_db.Fields(2)
rs.Fields("jh_gg") = rs_db.Fields(3)
rs.Fields("jh_dw") = rs_db.Fields(4)
If IsNull(rs_db.Fields(5)) Then
rs.Fields("jh_dj") = 0
Else
rs.Fields("jh_dj") = Format(rs_db.Fields(5), "0.00")
End If
If IsNull(rs_db.Fields(6)) Then
rs.Fields("jh_sl") = 0
Else
rs.Fields("jh_sl") = rs_db.Fields(6)
End If
If IsNull(rs_db.Fields(7)) Then
rs.Fields("jh_bfsl") = 0
Else
rs.Fields("jh_bfsl") = rs_db.Fields(7)
End If
If IsNull(rs_db.Fields(8)) Then
rs.Fields("jh_btsl") = 0
Else
rs.Fields("jh_btsl") = rs_db.Fields(8)
End If
If IsNull(rs_db.Fields(9)) Then
rs.Fields("jh_ptsl") = 0
Else
rs.Fields("jh_ptsl") = rs_db.Fields(9)
End If
If IsNull(rs_db.Fields(10)) Then
rs.Fields("jh_je") = 0
Else
rs.Fields("jh_je") = Format(rs_db.Fields(10), "0.00")
End If
If IsNull(rs_db.Fields(11)) Then
rs.Fields("jh_bfje") = 0
Else
rs.Fields("jh_bfje") = Format(rs_db.Fields(11), "0.00")
End If
If IsNull(rs_db.Fields(12)) Then
rs.Fields("jh_btje") = 0
Else
rs.Fields("jh_btje") = Format(rs_db.Fields(12), "0.00")
End If
If IsNull(rs_db.Fields(13)) Then
rs.Fields("jh_ptje") = 0
Else
rs.Fields("jh_ptje") = Format(rs_db.Fields(13), "0.00")
End If
rs.Fields("jh_fl") = rs_db.Fields(14)
rs.Fields("jh_cj") = rs_db.Fields(15)
rs.Fields("jh_hb") = rs_db.Fields(16)
rs.Fields("jh_bz") = rs_db.Fields(17)
rs.Fields("jh_year") = rs_db.Fields(18)
rs.Fields("jh_pc") = Combo1.Text
rs.Update
rs_db.MoveNext
Next j
MsgBox "导入成功,共有" & rs_db.RecordCount & "条记录!", 48, "信息"
Exit Sub类模块中
Dim msgtext As String
Dim mrc As ADODB.RecordsetPublic Function ExecuteSQL(ByVal sql As String, MsgString As String) As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
'Dim SQL As String
On Error GoTo ExecuteSQL_Error
sTokens = Split(sql)
Set cnn = New ADODB.Connection
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", UCase$(sTokens(0))) Then
cnn.Execute sql
MsgString = sTokens(0) & "query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(sql), cnn, adOpenKeyset, adLockOptimistic
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & "条纪录"
End If
ExecuteSQL_Exit:
Set rst = Nothing
Exit Function
Set cnn = Nothing
ExecuteSQL_Error:
MsgString = "查询错误:" & Err.Description
Resume ExecuteSQL_Exit
End FunctionPublic Function ConnectString() As String
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\计划管理系统.mdb;Persist Security Info=False"
'ConnectString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=aa;Data Source=RJB-CL"
End Function