Private Sub CommandButton1_Click()

Set Cnn = CreateObject("ADODB.Connection")

Set Rst = CreateObject("ADODB.Recordset")

Dim stPath As String

Dim strSQL As String

Dim aa As Variant

Dim i As Integer

Dim arrFid As Variant

aa = Timer

MsgBox "準(zhǔn)備傳數(shù)據(jù)到數(shù)據(jù)庫，請稍等！", vbInformation, "溫馨提示"

stPath = ThisWorkbook.Path & Application.PathSeparator & "人力資源管理系統(tǒng).mdb"

Cnn.Open "provider=Microsoft.jet.OLEDB.4.0;data source=" & stPath & ";Jet OLEDB:Database Password=" & "access"

Cnn.Execute "DELETE * FROM 車間計件工資數(shù)據(jù)庫"

'刪除所有紀(jì)錄

strSQL = "Select * from 車間計件工資數(shù)據(jù)庫"

Rst.Open strSQL, Cnn, 1, 3

arrFid = Array("prj_NO", "S_Exp", "S_X", "S_Y", "S_Deep", "E_Exp", "E_X", "E_Y")

With Sheets("綜合線表")

For i = 2 To .Range("a65536").End(xlUp).Row '數(shù)組添加比單個添加快很多!

Rst.AddNew arrFid, Array(i - 2, Str(.Cells(i, 1)), Str(.Cells(i, 2)), Val(.Cells(i, 3)), Val(.Cells(i, 4)), Val(.Cells(i, 5)), Str(.Cells(i, 6)), Val(.Cells(i, 7)))

Next

End With

? ? ?Rst.Close: Set Rst = Nothing

? ? ?Cnn.Close: Set Cnn = Nothing

MsgBox "寫入數(shù)據(jù)" & [a2].End(xlDown).Row - 1 & "行" & vbCrLf & "傳輸完畢：耗時" & Format(Timer - aa, "0.000") & "秒", vbInformation, "溫馨提示"

Application.OnTime Now + TimeValue("00:50:00"), "chuanshu"

End Sub