Hi

I think, the problem is that the error doesn't occur in Access, so Access
error indicators get nothing to show - the only exception are parameters for
forms OnError event DataErr and Response. (P.e. when trying to delete a
record linked to other tables, DataErr=3146 and Response=1)

And checking all this in Acccess - it nullifies most of reasons to move data
to SQL server! I have 10+ tables, and Access query isn't good enough on
every change in data to check all other tables for existecne of related
records. So there must be some other way :-((

Let's look what happens now.
1. Access: In some form, which is based on some table on SQL server, I edit
the record, or I delete record(s);
2. Access: Access sends a transaction query request to SQL server to save
changes or to delete row(s) ;
3. SQL Server: SQL Server starts transaction to save changes or to delete
row(s) ;
4. SQL Server: Check constraint(s) for this table, and foreign constraints
for all tables are checked;
5. SQL Server: Whenever any checked constraint returns False, the
transaction is rolled back, otherwise the transaction is made;
6. SQL Server: Some information is sent to Access about transaction result;
7. Access: Access gets the information from SQL server about transaction
result;
(8. Access: Somehow I have here to catch this information, and override the
next step!)
9. Access: When transaction was rolled back, an error message is displayed.

An example of error message:

"Microsoft Office Access

ODBC -- call failed.

[Microsoft] [SQL Native Client] [SQL Server] The DELETE statement conflicted
with the REFERENCE constraint 'FK_MyTable'. The conflict occurred in
database 'MyDB', table 'dbo.MyTable', column 'MyField'. (#547) [Microsoft]
[SQL Native Client] [SQL Server] The statement was terminated (#3621)"

And now imagine, that some user who hardly knows a couple words of english
gets such message - some months after he was warned about it. And to
further confusion, there is a help button in message window, where is
explained, that probably the connection to network was lost :-)))


Arvi Laanemets



"Stefan Hoffmann" kirjutas sõnumis news:
...
hi Arvi,

On 21.04.2010 11:00, Arvi Laanemets wrote:
It looks like I can catch the error in OnError event for form, but how
can I override the error message - usual On Error routine doesn't work
(Err.Number=0), and I'm afraid whatever message I'm programming,
standard transaction error message gets displayed too.
While you can catch them, you still need to translate them. btw, the
Err.Number should not be 0.

The problem is, that you need error handling on all forms - the On Error
event you've mentioned - and in every VBA prodedure or macro.

I would prefer a more defensive approach. Before executing such actions
check whether these actions are allowed.


mfG
-- stefan --