Capturing SQL error codes in Access

[Bobby]

Hi
I'm using Access 2003 with SQL server 2000, linked via ODBC.

Can anybody tell me how to capture SQL error codes in Access? If this
is not possible, is there any way I can simply turn off SQL errors?

Can I use docmd.setwarnings false? If so which event would I put it
on. On error doesn't seem to work.

Thanks

Colin

[Allen Browne]

You will need to be a bit more specific here.

If you code:
Set rs = Currentdb.Openrecordset("SELECT DogID FROM Dogs")
you get an error if there is no table/query named Dogs, or of that table
does not contain a field named DogID. You can't recover from that error and
continue using the recordset: that would be meaningless.

Other errors occur if you try to move to a non-existent record, or a linked
table is disconnected, or you try to insert a record without supplying a
value for a required field, or you violate a validation rule or unique
index, or try to alter a foriegn key field to an invalid value, or ...

So, you need different approaches for different kinds of errors. The Error
event of the form catches the engine-level errors. The error handling in a
procedure handles other errors. The program options allow you to suppress
some confirmation messages, and SetWarnings handles some of those as well as
suppressing other things.

If you are talking about distinguishing confirmations and error warnings
when running action queries, this might help:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bobby" <bo****@blueyonder.co.ukwrote in message
news:11**********************@o80g2000hse.googlegr oups.com...

Hi
I'm using Access 2003 with SQL server 2000, linked via ODBC.

Can anybody tell me how to capture SQL error codes in Access? If this
is not possible, is there any way I can simply turn off SQL errors?

Can I use docmd.setwarnings false? If so which event would I put it
on. On error doesn't seem to work.

Thanks

Colin

[Rich P]

Greetings,

In your sql server stored procedures you can use Begin Transaction
Commit Transaction Rollback Transaction and raise errors as follows --
then in Access you can:

-----------------------------------------------
Create Proc...
...parameters
As
... initialize stuff

Begin Transaction

stuff to do here

Commit Transaction

GoTo Finally --Finally is just a label to go to -
--similar to placing an Exit Sub statement
--before the Error Label -
--which I call Catch:
Catch:
RaisError('Problem with Procedure, 16, 10)
Rollback Transaction

--if there were no errors in the processing of the
--procedure the GoTo Finally will bypass the Catch: block

Finally:
Return
GO

-----------------------------------------------

In the Catch block, the RaiseError function takes a custom message 'your
custom message' followed by some error codes

RaiseError('your custom message', 16, 10)
Rollback Transaction

this will undo the entire processing as if it did not happen -- then
proceeds to exit by going to Finally: Return

Sometimes Access can read these error messages which will pop up in a
messagebox. Most of the time Access can't read these error messages
because Access is heavily subclassed (since Access is really an
application and not a full blown Programming environment).

To reliably trap all sql server error messages you should create an
application from a full blown programming environment like Java or .Net.
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[lyle]

On Sep 13, 10:29 am, Bobby <[e-mail]:

Hi
I'm using Access 2003 with SQL server 2000, linked via ODBC.

Can anybody tell me how to capture SQL error codes in Access? If this
is not possible, is there any way I can simply turn off SQL errors?

Can I use docmd.setwarnings false? If so which event would I put it
on. On error doesn't seem to work.

Thanks

Colin

may depend on what you're doing

one way ...

Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Set c = New ADODB.Connection
c.Open (CurrentProject.BaseConnectionString)
On Error Resume Next
Set r = c.Execute("SELECT * FROM Phantom")
Debug.Print c.Errors(0).Description
'Invalid object name 'Phantom'.