'原来是这样的,在access作为后台时并不慢
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim i, j As Long
rs1.Open "tblsales_temp", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rs2.Open "tbl_ccprk", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
For i = 1 To rs2.RecordCount
For j = 1 To rs1.RecordCount
If rs2("rkjg") > 0 Then
Exit For
Else
If rs2("cpbh") = rs1("cpbh") Then
If rs2("rkrq") >= rs1("jgsjks") And rs2("rkrq") <= rs1("jgsj") Then
rs2!rkjg = rs1!salesjg
rs2!rkbz = rs1!bizhong
Exit For
End If
End If
rs1.MoveNext
End If
Next
rs1.MoveFirst
rs2.MoveNext
Next
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
MsgBox "已更新产成品入库价格", vbInformation, "提示:"
但是现在更改数据库后台为SQL server 后,上面这段执行很慢。所以代码需要进行优化,改成更新查询。
优化代码:
'方案一
DoCmd.RunSQL "update tblsales_temp inner join tbl_ccprk on tblsales_temp.cpbh=tbl_ccprk.cpbh " _
& " set tbl_ccprk.rkjg=tblsales_temp.salesjg,tbl_ccprk.rkbz=tblsales_temp.bizhong " _
& " where bl_ccprk.rkrq>=tblsales_temp.jgsjks and bl_ccprk.rkrq<=tblsales_temp.jgsj"
'方案二
DoCmd.RunSQL "update tblsales_temp inner join tbl_ccprk on tblsales_temp.cpbh=tbl_ccprk.cpbh " _
& " set tbl_ccprk.rkjg=tblsales_temp.salesjg,tbl_ccprk.rkbz=tblsales_temp.bizhong " _
& " where bl_ccprk.rkrq between tblsales_temp.jgsjks and tblsales_temp.jgsj"
'方案一,简化版
DoCmd.RunSQL "update tblsales_temp AS X inner join tbl_ccprk AS Y on X.cpbh=Y.cpbh " _
& " set Y.rkjg = X.salesjg" _
& " , Y.rkbz = X.bizhong " _
& " where bl_ccprk.rkrq>=X.jgsjks and bl_ccprk.rkrq<=X.jgsj"
'方案二,简化版
DoCmd.RunSQL "update tblsales_temp inner join tbl_ccprk on X.cpbh=Y.cpbh " _
& " set Y.rkjg = X.salesjg" _
& " , Y.rkbz = X.bizhong " _
& " where bl_ccprk.rkrq between X.jgsjks and X.jgsj"