DoCmd.RunSQL Error

[Jay]

Hi all,

Alright, I read that SQL code is faster than DAO, so to speed up some file
parsing I tried converting my code from using Recordsets to SQL code.

And I've run into a bit of a problem. I was using the code to catch
violations of the primary keys (ie. if there was a repeat value).

For Recordset, it throws error number 3022. If that error occurs, it
increases a 'rejected' counter so I know how many were bounced due to
repeated values. Any other error displays a message.

Now, when I use DoCmd.RunSQL it catches the error, but I have to go through
a bunch of messageboxes to get an error code of 2501 (SQL command cancelled).
Because I have thousands of records, I set SetWarnings to False.

Here's my problem. Since SetWarnings = False, it doesn't show the message
boxes, which doesn't trigger the error, which doesn't change my counter.

So, is there a way I can test to see if the record was not entered using an
error code when using DoCmd.RunSQL? I don't want to have to cycle through
the whole table. If this isn't easily doable, I'll forget the counter. Or
go back to DAO. I haven't noticed a significant decrease in time of
operation. Of course, that may be a problem due to my code.

Thanks,
Jay

 

[Baz]

Hi all,

Alright, I read that SQL code is faster than DAO, so to speed up some file
parsing I tried converting my code from using Recordsets to SQL code.

And I've run into a bit of a problem. I was using the code to catch
violations of the primary keys (ie. if there was a repeat value).

For Recordset, it throws error number 3022. If that error occurs, it
increases a 'rejected' counter so I know how many were bounced due to
repeated values. Any other error displays a message.

Now, when I use DoCmd.RunSQL it catches the error, but I have to go through
a bunch of messageboxes to get an error code of 2501 (SQL command cancelled).
Because I have thousands of records, I set SetWarnings to False.

Here's my problem. Since SetWarnings = False, it doesn't show the message
boxes, which doesn't trigger the error, which doesn't change my counter.

So, is there a way I can test to see if the record was not entered using an
error code when using DoCmd.RunSQL? I don't want to have to cycle through
the whole table. If this isn't easily doable, I'll forget the counter. Or
go back to DAO. I haven't noticed a significant decrease in time of
operation. Of course, that may be a problem due to my code.

Thanks,
Jay

Don't use DoCmd.RunSQL, use this:

CurrentDb.Execute "...my sql string...", dbFailOnError

 

[Jay]

Perfect, thanks!

Don't use DoCmd.RunSQL, use this:

CurrentDb.Execute "...my sql string...", dbFailOnError