Trapping sqlcode with VBA

[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.

 

[Marshall Barton]

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.

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.