UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Set Warnings Off Prevents Error Trapping, Access 2010    
 
   
Dougie
post Feb 8 2018, 05:15 AM
Post#1



Posts: 81
Joined: 17-December 15



I have a VBA loop which inserts rows into a table. Sometimes the row might already exist but I have found that checking for existence before insertion is too resource intensive. I decided to go ahead and insert, but trap the error when a duplicate is attempted. As there will be hundreds of separate inserts in any single run I don't want the user to continuously have to respond to prompts asking if the insert should proceed, so I set warnings to false. However when I do this the error is not trapped and the error handler is not invoked. Is there a way around this?

The basic idea is this:

On Error GOTO Err_Handler

DoCmd.SetWarnings False

For each row in a recordset
DoCmd.RunSQL("Insert a row in Table1 copied from this recordset")
Increment the counter of records added
Skip:
Next record

DoCmd.SetWarnings True

Err_Handler:
Select case Err
Case 3022 'attempt to insert duplicate
resume Skip
Case else
report error
end select

The error handler is never called so the record counter is incremented even when no record is added. This code is in a module. If I insert similar code in a form, errors are still trapped even though warnings are set off.
Go to the top of the page
 
JonSmith
post Feb 8 2018, 05:28 AM
Post#2



Posts: 3,838
Joined: 19-October 10



I'm not sure about SetWarnings affecting error trapping, it doesn't in my experience and from memory but I haven't tested.

That being said I said don't use this method.

Rather than .RunSQL you can use .Execute

Execute doesn't pop up warnings and so you don't have any you need to suppress nor errors to trap. You can set it to fail it if cannot commit the record or not.
You can also open a transaction which would make committing alot of separate INSERT statements much faster.
Go to the top of the page
 
Dougie
post Feb 8 2018, 05:46 AM
Post#3



Posts: 81
Joined: 17-December 15



Thanks, that is helpful. I think this is a corruption of some kind as SetWarnings False does not affect error trapping in other examples I have tested.
Go to the top of the page
 
BruceM
post Feb 8 2018, 08:22 AM
Post#4


UtterAccess VIP
Posts: 7,638
Joined: 24-May 10
From: Downeast Maine


I agree completely about Execute (strSQL represents your Insert string):

CurrentDb.Execute strSQL, dbFailOnError

I wonder if you could assemble SQL to insert a batch of records all at once, with the duplicates eliminated beforehand, perhaps with:

WHERE SomeField Not In (SELECT SomeField FROM TableToWhichRecordsAreBeingCopied)
Go to the top of the page
 
Dougie
post Feb 12 2018, 02:37 AM
Post#5



Posts: 81
Joined: 17-December 15



"I wonder if you could assemble SQL to insert a batch of records all at once"

Unfortunately not - at least, not in any way I can think of. When each record is inserted it must copy some details from a related record in the table. I want to insert 'ABC1234Y' wherever the table contains a record with the same first seven characters in the appropriate field. So a search finds:

SerialNumber CustomerNumber OPAccount
12xyz 56er34 ABC1234X


...and must insert:
12xyz 56er34 ABC1234Y


There is a unique index on SerialNumber+OPAccount
Go to the top of the page
 
BruceM
post Feb 12 2018, 08:12 AM
Post#6


UtterAccess VIP
Posts: 7,638
Joined: 24-May 10
From: Downeast Maine


I don't quite understand what you are doing, but I'll take your word for it that a batch insert will not work. Even so, I wonder if you could build the source recordset in such a way (grouping? select distinct?) as to avoid duplicates in the first place.

If you could build such a recordset, it could (in theory) be modified to produce the records you need to insert as new records. I expect values such as ABC1234X are for illustration only, and do not represent actual values, but if they did, and if the intent is to change ABC1234X into ABC1234Y it could be:
Left(SomeField,7) & Chr(Asc(Right(SomeField,1)) + 1)

The above may produce unwanted results if the SomeField value is "ABC1234Z", but as I mentioned I expect this is not the real value. I'm just trying to suggest it may be possible. Another possibility may be to have "ABC1234" could be in one field, and the suffix in another.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th September 2018 - 10:27 AM