Trapping sqlcode with VBA

  • Thread starter Thread starter steve
  • Start date Start date
S

steve

I'm trying to trap the sqlcode returned from Access, if it's even available,
after executing an sql statement using docmd.runsql. The On Error routine
in my code is running IF I have setwarnings set to TRUE, but the Err object
is not showing the sqlcodes I expect. For example, if I try to insert a row
with a PK value that already exists, Err.Number will equal -2501 (or
something like that), but my old experience (SQLServer and DB2) is telling me
that I should expect a -100 since the database should return a duplicate PK
error (which is DOES - if I set setwarnings to TRUE, I get a relevant message
from access, but I want to hide that from the user and manage the error
myself. Not sure if I should just be looking at Err.Number and mapping
Access's codes to sqlcodes, or if the underlying database error handling is
available in an onject that I can't find.

Any help would be huge. I have enough grey hair already and this is really
irritating me !

Thanks.
 
steve said:
I'm trying to trap the sqlcode returned from Access, if it's even available,
after executing an sql statement using docmd.runsql. The On Error routine
in my code is running IF I have setwarnings set to TRUE, but the Err object
is not showing the sqlcodes I expect. For example, if I try to insert a row
with a PK value that already exists, Err.Number will equal -2501 (or
something like that), but my old experience (SQLServer and DB2) is telling me
that I should expect a -100 since the database should return a duplicate PK
error (which is DOES - if I set setwarnings to TRUE, I get a relevant message
from access, but I want to hide that from the user and manage the error
myself. Not sure if I should just be looking at Err.Number and mapping
Access's codes to sqlcodes, or if the underlying database error handling is
available in an onject that I can't find.
Click to expand...


Explore using the Execute method instead of RunSQL. It does
not generate messages, runs synchronously, and its
dbFailOnError argument allows you to trap the errors.
 
Back
Top