3022 error while run update SQL

[iccsi]

I use CurrentDb.Execute strMYSQL, dbFailOnError to run my update
query.

I got run time error 3022 which is duplicate records found.
I tried to update multi records from the code.

I use following code to filter out the error, MS Access only updates
the first record.

Err_Handle:
Select Case Err
Case 3022
Resume Exit_Handle
Case Else
strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number) &
vbCrLf
strErrMsg = strErrMsg & "Error Description: " &
Err.Description
Resume Exit_Handle
End Select

I would like to know is it possible to update mutli records using VBA
code like CurrentDB.Execute


Your help is great appreciated,

 

[Dirk Goldgar]

I use CurrentDb.Execute strMYSQL, dbFailOnError to run my update
query.

I got run time error 3022 which is duplicate records found.
I tried to update multi records from the code.

I use following code to filter out the error, MS Access only updates
the first record.

Err_Handle:
Select Case Err
Case 3022
Resume Exit_Handle
Case Else
strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number) &
vbCrLf
strErrMsg = strErrMsg & "Error Description: " &
Err.Description
Resume Exit_Handle
End Select

I would like to know is it possible to update mutli records using VBA
code like CurrentDB.Execute

Are you *updating* multiple records, which is no problem at all for an
UPDATE query, or ar you *inserting* multiple records with an append query,
and want duplicates to be ignored? If you're getting error 3022, I think
you must be inserting records, not updating them, unless you are running an
update query that changes the value of a unique key.

If you are running an append query, you can remove the "dbFailOnError"
argument and I believe the non-duplicate records will be appended. That is,
instead of:

CurrentDb.Execute strMYSQL, dbFailOnError

.... use:

CurrentDb.Execute strMYSQL

I'm not sure if that works with an update query or not.