access vba代码全部丢失_EXCEL VBA更新Access数据库并向数据库添加不存在的记录求助代码不能执行的问题...

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