Sub 修改数据()
'On Error Resume Next '// 发生错误,自动执行下一句,就是忽略错误
Application.ScreenUpdating = False '//关闭屏幕刷新
Application.DisplayAlerts = False '//关闭系统提示
t = Timer '//开始时间
Set SH1 = Sheets("ATA")
Str_coon = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\Database1.accdb"
INT修改成功 = 0
INT修改失败 = 0
INT添加成功 = 0
INT添加失败 = 0
For i = 2 To SH1.Range("A65536").End(3).Row
StrSQL1 = "SELECT * FROM ATA WHERE [ID Number]= '" & SH1.Cells(i, 1) & "'" '//判断ID是否存在
If NumInfoSql(StrSQL1, Str_coon) > 0 Then
StrSQL = "UPDATE ATA SET"
StrSQL = StrSQL & " [Date]='" & Format(SH1.Cells(i, 2), "YYYY-MM-DD") & "'"
StrSQL = StrSQL & " WHERE [ID Number]= '" & SH1.Cells(i, 1) & "'"
If AddDelMove(StrSQL, Str_coon) = True Then
INT修改成功 = INT修改成功 + 1
' SH1.Cells(i, 12).Value = "修改成功"
Else
INT修改失败 = INT修改失败 + 1
' SH1.Cells(i, 12).Value = "修改失败"
End If
Else
StrSQL = "INSERT INTO ATA ([ID Number],[Date]) VALUES ( '" & SH1.Cells(i, 1) & "', '" & Format(SH1.Cells(i, 2), "YYYY-MM-DD") & "')"
If AddDelMove(StrSQL, Str_coon) = True Then
INT添加成功 = INT添加成功 + 1
Else
INT添加失败 = INT添加失败 + 1
End If
End If
Next i
strX = "修改成功的条数:" & INT修改成功 & vbCrLf & vbCrLf & "修改失败的条数:" & INT修改失败
strX = strX & vbCrLf & vbCrLf & "添加成功的条数:" & INT添加成功 & vbCrLf & vbCrLf & "添加失败的条数:" & INT添加失败
Application.ScreenUpdating = True '//恢复屏幕刷新
Application.DisplayAlerts = True '//恢复系统提示
MsgBox strX & vbCrLf & vbCrLf & "一共用时:" & Format(Timer - t, "#0.0000") & " 秒", , "北极狐提示!!" '//提示所用时间
End Sub