Private Sub Command2_Click()
Dim StrCnn As String
Dim cnn As ADODB.Connection
Dim cnnd As ADODB.Connection
Dim rs_db As ADODB.Recordset
Dim strsql_db As StringDim nn As Integer
ccrpProgressBar1.Visible = True
Label2.Visible = TrueStrCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source=" & Text1.Text & ";Extended Properties='Excel 8.0;HDR=Yes'" 'excel表的驱动(text1.text 是表的地址)
Set cnn = New ADODB.Connection
cnn.Open StrCnn
strsql_db = "select * from [sheet1$]" 'sheet1是Excel工作表名
cnn.Execute strsql_db
Set rs_db = New ADODB.Recordset
rs_db.Open strsql_db, cnn, adOpenKeyset, adLockOptimistic '打开记录集
StrCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + App.Path + "\同学录.mdb;Jet OLEDB:Database Password=131428" '数据库连接串
Set cnnd = New ADODB.Connection
cnnd.Open StrCnn
j = 1
nn = rs_db.RecordCount
Do While Not rs_db.EOF
If nn > 1 And nn < 100 Then
ccrpProgressBar1.Value = j
End If
If nn > 100 And nn < 200 Then
ccrpProgressBar1.Value = j / 2
End If
Label2.Caption = "一共导入" & CStr(j) & "记录"
DoEvents
'======导入数据==============================================================================================
If j >= 1 Then
strsql_db = "insert into 同学录(xm,xb,nl,csrq,xz,sx,xx,lxdh,sj,qq,E-mail,byxx,ys,sw,mx,s,dy,dw,h)values('" & rs_db.Fields(0).Value & "','" & rs_db.Fields(1).Value & "','" & rs_db.Fields(2).Value & "','" & rs_db.Fields(3).Value & "','" & rs_db.Fields(4).Value & "','" & rs_db.Fields(5).Value & "','" & rs_db.Fields(6).Value & "','" & rs_db.Fields(7).Value & "','" & rs_db.Fields(8).Value & "','" & rs_db.Fields(9).Value & "','" & rs_db.Fields(10).Value & "','" & rs_db.Fields(11).Value & "','" & rs_db.Fields(12).Value & "','" & rs_db.Fields(13).Value & "','" & rs_db.Fields(14).Value & "','" & rs_db.Fields(15).Value & "','" & rs_db.Fields(16).Value & "','" & rs_db.Fields(17).Value & "','" & rs_db.Fields(18).Value & "')"
' strsql_db1 = "insert into 同学录(xm,xb,nl,csrq,xz,) values('" & rs_db.Fields(0).Value & "','" & rs_db.Fields(1).Value & "','" & rs_db.Fields(2).Value & "','" & rs_db.Fields(3).Value & "','" & rs_db.Fields(4).Value & "')"
cnnd.Execute (strsql_db)
End If
'=================================================================================================================
j = j + 1
rs_db.MoveNext
Loop
ccrpProgressBar1.Value = 100
rs_db.CloseSet rs_db = Nothing
cnnd.Close
cnn.Close
Set cnnd = Nothing
Set cnn = NothingEnd Sub
Dim StrCnn As String
Dim cnn As ADODB.Connection
Dim cnnd As ADODB.Connection
Dim rs_db As ADODB.Recordset
Dim strsql_db As StringDim nn As Integer
ccrpProgressBar1.Visible = True
Label2.Visible = TrueStrCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source=" & Text1.Text & ";Extended Properties='Excel 8.0;HDR=Yes'" 'excel表的驱动(text1.text 是表的地址)
Set cnn = New ADODB.Connection
cnn.Open StrCnn
strsql_db = "select * from [sheet1$]" 'sheet1是Excel工作表名
cnn.Execute strsql_db
Set rs_db = New ADODB.Recordset
rs_db.Open strsql_db, cnn, adOpenKeyset, adLockOptimistic '打开记录集
StrCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + App.Path + "\同学录.mdb;Jet OLEDB:Database Password=131428" '数据库连接串
Set cnnd = New ADODB.Connection
cnnd.Open StrCnn
j = 1
nn = rs_db.RecordCount
Do While Not rs_db.EOF
If nn > 1 And nn < 100 Then
ccrpProgressBar1.Value = j
End If
If nn > 100 And nn < 200 Then
ccrpProgressBar1.Value = j / 2
End If
Label2.Caption = "一共导入" & CStr(j) & "记录"
DoEvents
'======导入数据==============================================================================================
If j >= 1 Then
strsql_db = "insert into 同学录(xm,xb,nl,csrq,xz,sx,xx,lxdh,sj,qq,E-mail,byxx,ys,sw,mx,s,dy,dw,h)values('" & rs_db.Fields(0).Value & "','" & rs_db.Fields(1).Value & "','" & rs_db.Fields(2).Value & "','" & rs_db.Fields(3).Value & "','" & rs_db.Fields(4).Value & "','" & rs_db.Fields(5).Value & "','" & rs_db.Fields(6).Value & "','" & rs_db.Fields(7).Value & "','" & rs_db.Fields(8).Value & "','" & rs_db.Fields(9).Value & "','" & rs_db.Fields(10).Value & "','" & rs_db.Fields(11).Value & "','" & rs_db.Fields(12).Value & "','" & rs_db.Fields(13).Value & "','" & rs_db.Fields(14).Value & "','" & rs_db.Fields(15).Value & "','" & rs_db.Fields(16).Value & "','" & rs_db.Fields(17).Value & "','" & rs_db.Fields(18).Value & "')"
' strsql_db1 = "insert into 同学录(xm,xb,nl,csrq,xz,) values('" & rs_db.Fields(0).Value & "','" & rs_db.Fields(1).Value & "','" & rs_db.Fields(2).Value & "','" & rs_db.Fields(3).Value & "','" & rs_db.Fields(4).Value & "')"
cnnd.Execute (strsql_db)
End If
'=================================================================================================================
j = j + 1
rs_db.MoveNext
Loop
ccrpProgressBar1.Value = 100
rs_db.CloseSet rs_db = Nothing
cnnd.Close
cnn.Close
Set cnnd = Nothing
Set cnn = NothingEnd Sub
免费的学习交流网站,欢迎大家访问!
http://www.j2soft.cn/
http://j2soft.008.net/
其中有一个字段sj,它的格式是不是日期格式?如果是那么把这句中的
"','" & rs_db.Fields(8).Value & "','" &
改为:
"',#" & rs_db.Fields(8).Value & "#,'" & 在Access中,日期是用##阔起来的
'======导入数据==============================================================================================
If j >= 1 Then
strsql_db = "insert into 同学录(xm,xb,nl,csrq,xz,sx,xx,lxdh,sj,qq,E-mail,byxx,ys,sw,mx,s,dy,dw,h)values('" & rs_db.Fields(0).Value & "','" & rs_db.Fields(1).Value & "','" & rs_db.Fields(2).Value & "','" & rs_db.Fields(3).Value & "','" & rs_db.Fields(4).Value & "','" & rs_db.Fields(5).Value & "','" & rs_db.Fields(6).Value & "','" & rs_db.Fields(7).Value & "','" & rs_db.Fields(8).Value & "','" & rs_db.Fields(9).Value & "','" & rs_db.Fields(10).Value & "','" & rs_db.Fields(11).Value & "','" & rs_db.Fields(12).Value & "','" & rs_db.Fields(13).Value & "','" & rs_db.Fields(14).Value & "','" & rs_db.Fields(15).Value & "','" & rs_db.Fields(16).Value & "','" & rs_db.Fields(17).Value & "','" & rs_db.Fields(18).Value & "')"
' strsql_db1 = "insert into 同学录(xm,xb,nl,csrq,xz,) values('" & rs_db.Fields(0).Value & "','" & rs_db.Fields(1).Value & "','" & rs_db.Fields(2).Value & "','" & rs_db.Fields(3).Value & "','" & rs_db.Fields(4).Value & "')"
cnnd.Execute (strsql_db)
End If
'=================================================================================================================
查查rs_db各个Field的值格式是否与要求相符
运行时跟踪一下看一下strsql_db的具体值就行了。
应当括起来[E-mail]