EXCEL导入ACCESS数据库代码.doc

Dim Conn As ADODB.Connection Set Conn = New ADODB.Connection Conn.Open 连接到你的数据库XJGL.MDB的字符串 Conn.Execute select * into tmptable from excel 8.0;database= + 你的excel表名 + .sheet名$ Conn.Execute insert into xsda(学籍号,准考证号,姓名,性别,出生年月,班级)select 学籍号,准考证号,姓名,性别,出生年月,班级 from tmptable Conn.Execute drop tabel tmptable Set Conn = Nothing/Sub LoadUsers() On Error Resume Next Screen.MousePointer = 11 Dim xlspath As String Dim xlsConn As New ADODB.Connection Dim xlsrs As New ADODB.Recordset Dim sql As String Dim totalRecords As Long xlspath = Text1 sql = select * from Sheet1$ With xlsConn .ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= & xlspath & ;Extended Properties=Excel 8.0;HDR=Yes;IMEX=1; .CommandTimeout = 5 .Open End With xlsrs.Open sql, xlsConn, adOpenStatic, adLockReadOnly totalRecords = xlsrs.RecordCount ProgressBar1.Max = totalRecords ProgressBar1.Min = 0 ProgressBar1.Value = 0 Conn.Execute delete from Users Dim i As Long For i = 0 To totalRecords - 1 Conn.Execute insert into Users (ID,Name,Shenfennums,Address) values( & xlsrs(工号) & , & xlsrs(员工名称) & , & xlsrs(身份证号码) & , & xlsrs(地址) & ) ProgressBar1.Value = ProgressBar1.Value + 1 xlsrs.MoveNext Next xlsrs.Close Set xlsrs = Nothing xlsConn.Close Screen.MousePointer = 0 Unload Me End Sub/Dim extend_str As String Dim execl_cnn As New ADODB.Connection Dim execl_rst As New ADODB.Recordset Dim execl_count_rst As New ADODB.Recordset 纪录数量 Dim execl_field_rst As New ADODB.Recordset Dim execl_strconn As String Dim execl_strcmd As String Dim execl_count_str As String Dim insertStr As String 操作oracle数据库的记录集 Dim rst As ADODB.Recordset CommonDialog1.ShowOpen extend_str = Right(CommonDialog1.FileName, 3) If (extend_str = xls) Then execl_cnn.Open Provider=Microsoft.Jet.OLEDB.4.0; & _ Data Source= & CommonDialog1.FileName & _ ;Extended Properties=Excel 8.0; ElseIf (extend_str = dbf) Then execl_cnn.ConnectionString = Provider=MSDASQL.1;Driver=Microsoft Visual Foxpro Driver;SourceType=DBF;DBQ= & getDir(CommonDialog1.FileName) execl_cnn.Open End If If (extend_str = xls) Then execl_strcmd = SELECT * from Sheet1$ execl_count_str = SELECT count(*) from Sheet1$ execl_field_rst.Open execl_count_str, execl_cnn If (execl_field_rst.Fields(0).Value = 0) Then execl_strcmd = SELECT * from & getFileNoExt(getfile(CommonDialog1.FileName) & $ execl_count_str = SELECT count(*) from & getFileNoExt(getfile(CommonDialog1.FileName) & $ End If execl_field_rst.Close ElseIf (extend_str = dbf) Then execl_strcmd = SELECT * from & CommonDialog1.FileName execl_count_str = SELECT count(*) from & CommonDialog1.FileName End If execl_rst.Open execl_strcmd, execl_cnn execl_count_rst.Open execl_count_str, execl_cnn If (CommonDialog1.FileName ) Then 进度条设置 ProgressBar1.Min = 0 ProgressBar1.max = execl_count_rst.Fields(0).Value If (execl_rst.RecordCount) Then 如果有记录 If (Option1.Value) Then 如果是仪器总库 If (execl_rst.Fields.Count = 8) Then Set rst = yg_gain_table_recordset(yg_device) execl_rst.MoveFirst Do While (Not execl_rst.EOF) rst.AddNew rst.Fields(0).Value = yg_getNextId(yg_device_id) rst.Fields(1).Value = execl_rst.Fields(0).Value & rst.Fields(2).Value = execl_rst.Fields(1).Value & rst.Fields(3).Value = execl_rst.Fields(2).Value & rst.Fields(4).Value = execl_rst.Fields(3).Value & rst.Fields(5).Value = execl_rst.Fields(4).Value & rst.Fields(6).Value = execl_rst.Fields(5).Value & rst.Fields(7).Value = execl_rst.Fields(6).Value & rst.Fields(8).Value = execl_rst.Fields(7).Value & rst.Update execl_rst.MoveNext ProgressBar1.Value = ProgressBar1.Value + 1 DoEvents Loop yg_free_recordset rst Else MsgBox 结构不一致 Exit Sub End If End If If (Option2.Value) Then 如果是外借仪器 If (execl_rst.Fields.Count = 12) Then Set rst = yg_gain_table_recordset(yg_out_device) execl_rst.MoveFirst Do While (Not execl_rst.EOF) rst.AddNew rst.Fields(0).Value = yg_getNextId(yg_out_device_id) rst.Fields(1).Value = execl_rst.Fields(0).Value & rst.Fields(2).Value = execl_rst.Fields(1).Value & rst.Fields(3).Value = execl_rst.Fields(2).Value & rst.Fields(4).Value = execl_rst.Fields(3).Value & rst.Fields(5).Value = execl_rst.Fields(4).Value & rst.Fields(6).Value = execl_rst.Fields(5).Value & rst.Fields(7).Value = execl_rst.Fields(6).Value & rst.Fields(8).Value = execl_rst.Fields(7).Value & rst.Fields(9).Value = execl_rst.Fields(8).Value & rst.Fields(10).Value = execl_rst.Fields(9).Value & rst.Fields(11).Value = ex