我用记录集把数据从一张表考到另一张表,因为记录多,有得有1w多条,有的表由几十万条,一下代码是我的错误代码,不能显示进度,操作也不能中止了!请各位指点一二!不胜感激!
其中有个不合理的地方,因为数据量多,因此在显示frmprogress的时候可能要一定的时间等待中Private Sub StudentsGet_Click()
If MsgBox("确定要进行学生数据抽取!", vbOKCancel + vbExclamation, "注意") = vbOK Then
Dim rs As ADODB.Recordset
Dim rst As ADODB.Recordset
Set rs = DataEnvironment1.rsCommandyxs
Set rst = DataEnvironment1.rsCommandxs
If rs.State = adStateClosed Then
rs.Open
End If
If rst.State = adStateClosed Then
rst.Open
End If
frmProgress.ProgressBar1.Min = 0
frmProgress.ProgressBar1.Max = rs.RecordCount
frmProgress.ProgressBar1.Visible = True
frmProgress.ProgressBar1.Value = frmProgress.ProgressBar1.Min
frmProgress.Label1.Caption = "学生数据抽取中,可能要几分钟请您稍候!"
frmProgress.MousePointer = vbHourglass
frmProgress.Show vbModal, Mers.MoveFirst
Do While Not rs.EOF
frmProgress.ProgressBar1.Value = frmProgress.ProgressBar1.Min + 100
rst.AddNew
rst.Fields("教学号") = rs.Fields("jxh")
rst.Fields("学号") = rs.Fields("xh")
rst.Fields("姓名") = rs.Fields("xm")
rst.Fields("性别") = rs.Fields("xb")
rst.Fields("出生年月") = rs.Fields("csrq")
rst.Fields("入学年份") = Left(rst.Fields("教学号"), 4)
rst.Fields("班级代码") = Left(rs.Fields("jxh"), 8)
rst.Fields("院系代码") = Right(Left(rs.Fields("jxh"), 6), 2)
rst.Fields("毕业年份") = rst.Fields("入学年份") + 4
rst.Update
rst.MoveNext
rs.MoveNext
DoEvents
Loop
rst.Close
rs.Close
Set rst = Nothing
Set rs = Nothing
frmProgress.MousePointer = vbNormal
MsgBox "数据抽取完毕!", vbOKOnly + vbInformation, "完毕"
frmProgress.Hide
End If
End Sub
其中有个不合理的地方,因为数据量多,因此在显示frmprogress的时候可能要一定的时间等待中Private Sub StudentsGet_Click()
If MsgBox("确定要进行学生数据抽取!", vbOKCancel + vbExclamation, "注意") = vbOK Then
Dim rs As ADODB.Recordset
Dim rst As ADODB.Recordset
Set rs = DataEnvironment1.rsCommandyxs
Set rst = DataEnvironment1.rsCommandxs
If rs.State = adStateClosed Then
rs.Open
End If
If rst.State = adStateClosed Then
rst.Open
End If
frmProgress.ProgressBar1.Min = 0
frmProgress.ProgressBar1.Max = rs.RecordCount
frmProgress.ProgressBar1.Visible = True
frmProgress.ProgressBar1.Value = frmProgress.ProgressBar1.Min
frmProgress.Label1.Caption = "学生数据抽取中,可能要几分钟请您稍候!"
frmProgress.MousePointer = vbHourglass
frmProgress.Show vbModal, Mers.MoveFirst
Do While Not rs.EOF
frmProgress.ProgressBar1.Value = frmProgress.ProgressBar1.Min + 100
rst.AddNew
rst.Fields("教学号") = rs.Fields("jxh")
rst.Fields("学号") = rs.Fields("xh")
rst.Fields("姓名") = rs.Fields("xm")
rst.Fields("性别") = rs.Fields("xb")
rst.Fields("出生年月") = rs.Fields("csrq")
rst.Fields("入学年份") = Left(rst.Fields("教学号"), 4)
rst.Fields("班级代码") = Left(rs.Fields("jxh"), 8)
rst.Fields("院系代码") = Right(Left(rs.Fields("jxh"), 6), 2)
rst.Fields("毕业年份") = rst.Fields("入学年份") + 4
rst.Update
rst.MoveNext
rs.MoveNext
DoEvents
Loop
rst.Close
rs.Close
Set rst = Nothing
Set rs = Nothing
frmProgress.MousePointer = vbNormal
MsgBox "数据抽取完毕!", vbOKOnly + vbInformation, "完毕"
frmProgress.Hide
End If
End Sub
改成
frmProgress.ProgressBar1.Value = frmProgress.ProgressBar1.Value + 1 呢?中止操作用Ctrl+Break
思考:
这样做是不是太麻烦了?
用"select * into 表2 from 表1 where 条件"怎么样?能满足你的要求吗?
"select * into 表2 from 表1 where 条件" 不怎么好用//
记录总数为BAR的最大值
定义一个LONG型变量
RS循环一次,BAR就加一
这样做的效果是很真切
frmProgress.ProgressBar1.Min=0
frmProgress.ProgressBar1.Max=rst.recordCountdo while xxxxxxxx
frmProgress.ProgressBar1.Value = frmProgress.ProgressBar1.Value + 1
loop
这一段我敢保证可以正常运行,要不然换个方式:frmProgress.ProgressBar1.Value = 0
frmProgress.ProgressBar1.Min=0
frmProgress.ProgressBar1.Max=100for i=1 to rst.recordcount
frmProgress.ProgressBar1.Value = frmProgress.ProgressBar1.Value + int((100/rst.recordcount)*i)
next i
frmProgress.ProgressBar1.Min=0
frmProgress.ProgressBar1.Max=rst.recordCountdo while xxxxxxxx
frmProgress.ProgressBar1.Value = frmProgress.ProgressBar1.Value + 1
loop
这一段我敢保证可以正常运行,要不然换个方式:frmProgress.ProgressBar1.Value = 0
frmProgress.ProgressBar1.Min=0
frmProgress.ProgressBar1.Max=100for i=1 to rst.recordcount
frmProgress.ProgressBar1.Value = frmProgress.ProgressBar1.Value + int((100/rst.recordcount)*i)
next i