Prev: Tabular Form - Move Cursor Down
Next: Sub forms keep turning out like tables
From: Arvi Laanemets on 21 Apr 2010 05:00
Hi

I'm at my first try to use SQL Server as back-end database for Access
front-end.

On SQL Server, I created constraints (additionally to primary key) for my
tables:
a) foreign key constraints to control data integrity (deleting entries
linked to other tables isn't allowed);
b) check constraints (certain fields or combination of fields must be
unique).

It works all fine in access, but when those constraints are triggered, the
message returned to access is a mess for an average user. Is there a way to
replace those messages with my own, p.e. like:
"Deleting isn't allowed because data are used in other tables!" or "This
name is in use!"

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.


Thanks in advance!
Arvi Laanemets

From: Stefan Hoffmann on 21 Apr 2010 05:27
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 <--
From: Arvi Laanemets on 21 Apr 2010 08:08
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" <ste5an(a)ste5an.de> kirjutas s�numis news:
OVKFdTT4KHA.4964(a)TK2MSFTNGP05.phx.gbl...
> 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 <--

From: Stefan Hoffmann on 21 Apr 2010 09:05
hi Arvi,

On 21.04.2010 14:08, Arvi Laanemets wrote:
> 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)
E.g.

http://archive.baarns.com/access/faq/ad_error.asp


mfG
--> stefan <--
From: Arvi Laanemets on 21 Apr 2010 09:23
Hi again!

I did get somewhat further. OnError event has 2 parameters, DataErr and
Response. The default value for Response is acDataErrDisplay (or 1).
Changing Response to acDataErrContinue supresses the Access error message
and allows me to write my own one. I.e. something like

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 3146
MsgBox "There was an attempt to delete data linked with other
tables, or to enter a duplicate name!"
Response = acDataErrContinue
End Select
End Sub


One problem remains. Error code 3146 forwarded by DataErr parameter refers
to general ODBC error "ODBC call failed". Really there are 2 types of ODBC
errors I want to override:
1) Violation of UNIQUE KEY constraint;
2) DELETE statement conflict with REFERENCE constraint.
All other errors must be processed normally.

It's sure Access gets this info from SQL server (error messages for both
cases contain such strings), but where can I find this info? Otherwise my
error message must be very general like in example above - I'd like specific
messages for both cases instead!


Arvi Laanemets



 |  Next  |  Last
Pages: 1 2
Prev: Tabular Form - Move Cursor Down
Next: Sub forms keep turning out like tables