Dim Con As Connection Dim rs As Recordset Dim tmpfield As Field Set rs = New Recordset Set Con = New Connection Dim CONSQL As New Connection CONSQL.open strsql Set rsMain = New Recordset rsMain.open "select * from YouSQLTable", CONSQL, adOpenStatic, adLockBatchOptimistic '打開EXCEL文件 Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source=" & _ Trim(Me.txtFilePath) & ";Extended Properties='Excel 8.0;HDR=Yes'" Con.open rs.open "select * from [" & Trim(Me.txtSheetName) & "$]", Con, adOpenKeyset, adLockOptimistic
Dim I, j As Integer I = 0 rs.MoveFirst
On Error GoTo err1 '啟動連接的事務處理 CONSQL.BeginTrans
While Not rs.EOF On Error Resume Next I = I + 1 rsMain.AddNew For j = 0 To rs.Fields.Count - 1 rsMain.Fields(j + 1) = rs.Fields(j) Next rs.MoveNext Wend rsMain.UpdateBatch '提交事務 gConn.CommitTrans Exit Sub err1: ' 事務回滾 CONSQL.RollbackTrans MsgBox "导入过程出错,请检查工作表名是否正确或文件格式是否符合要求~"
Private Sub Form_Load() Text1.Text = App.Path & "\123.xls" Text2.Text = App.Path & "\123.mdb" Text3.Text = "sheet1" Text4.Text = "sheet1" Data1.DatabaseName = App.Path & "\123.mdb" End Sub Private Sub Command1_Click() Dim db As Database Dim sheet As String, excelpath As String, AccessPath As String, AccessTable As String AccessPath = Text2.Text '数据库路径 excelpath = Text1.Text '电子表格路经 AccessTable = Text4.Text '数据库内表格 sheet = Text3.Text '电子表格内工作表 Set db = OpenDatabase(excelpath, True, False, "Excel 5.0") '打开电子表格文件 SQL = ("Select * into [;database=" & AccessPath & "]." & AccessTable & " FROM [" & sheet & "$]") db.Execute (SQL) '将电子表格导入数据库 Data1.RecordSource = "sheet1" Data1.Refresh DBGrid1.Refresh '显示电子表格导入到数据库的数据 End Sub Private Sub Command2_Click() End End Sub
Dim Con As Connection
Dim rs As Recordset
Dim tmpfield As Field
Set rs = New Recordset
Set Con = New Connection
Dim CONSQL As New Connection
CONSQL.open strsql
Set rsMain = New Recordset
rsMain.open "select * from YouSQLTable", CONSQL, adOpenStatic, adLockBatchOptimistic '打開EXCEL文件
Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source=" & _
Trim(Me.txtFilePath) & ";Extended Properties='Excel 8.0;HDR=Yes'"
Con.open
rs.open "select * from [" & Trim(Me.txtSheetName) & "$]", Con, adOpenKeyset, adLockOptimistic
Dim I, j As Integer
I = 0
rs.MoveFirst
On Error GoTo err1
'啟動連接的事務處理
CONSQL.BeginTrans
While Not rs.EOF
On Error Resume Next
I = I + 1
rsMain.AddNew
For j = 0 To rs.Fields.Count - 1
rsMain.Fields(j + 1) = rs.Fields(j)
Next
rs.MoveNext
Wend
rsMain.UpdateBatch
'提交事務
gConn.CommitTrans
Exit Sub
err1:
' 事務回滾
CONSQL.RollbackTrans
MsgBox "导入过程出错,请检查工作表名是否正确或文件格式是否符合要求~"
Text1.Text = App.Path & "\123.xls"
Text2.Text = App.Path & "\123.mdb"
Text3.Text = "sheet1"
Text4.Text = "sheet1"
Data1.DatabaseName = App.Path & "\123.mdb"
End Sub
Private Sub Command1_Click()
Dim db As Database
Dim sheet As String, excelpath As String, AccessPath As String, AccessTable As String
AccessPath = Text2.Text '数据库路径
excelpath = Text1.Text '电子表格路经
AccessTable = Text4.Text '数据库内表格
sheet = Text3.Text '电子表格内工作表
Set db = OpenDatabase(excelpath, True, False, "Excel 5.0") '打开电子表格文件
SQL = ("Select * into [;database=" & AccessPath & "]." & AccessTable & " FROM [" & sheet & "$]")
db.Execute (SQL) '将电子表格导入数据库
Data1.RecordSource = "sheet1"
Data1.Refresh
DBGrid1.Refresh '显示电子表格导入到数据库的数据
End Sub
Private Sub Command2_Click()
End
End Sub