If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Overriding constraint violation message
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 |
#2
|
|||
|
|||
Overriding constraint violation message
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 -- |
#3
|
|||
|
|||
Overriding constraint violation message
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 -- |
#4
|
|||
|
|||
Overriding constraint violation message
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 -- |
#5
|
|||
|
|||
Overriding constraint violation message
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 |
#6
|
|||
|
|||
Overriding constraint violation message
Hi
Thanks! This link refers to almost same solution I found myself. But DBEngine.Errors doesn't work for me. There exists only one item DBEngine.Errors(0) with Number=3001 - "Invalid argument". And it doesn't depend on my actions which caused the error (i.e. deleting linked data or entering some duplicate value). "Stefan Hoffmann" kirjutas sõnumis news: ... 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 -- |
#7
|
|||
|
|||
Overriding constraint violation message
hi Arvi,
On 21.04.2010 15:53, Arvi Laanemets wrote: This link refers to almost same solution I found myself. But DBEngine.Errors doesn't work for me. There exists only one item DBEngine.Errors(0) with Number=3001 - "Invalid argument". And it doesn't depend on my actions which caused the error (i.e. deleting linked data or entering some duplicate value). Ah, I see, take a look at http://support.microsoft.com/default...b;en-us;206175 mfG -- stefan -- |
#8
|
|||
|
|||
Overriding constraint violation message
Thanks!
It was what I was searching for! (Almost.) The function works fine when I'm adding or editing a record. Unfortunatelly it looks like MS has forgotten the case, when records are deleted - so probably I have to find the solution myself. I haven't worked much with recordsets per VBA, so there is a lot of reading to be done. It looks like form's Delete event will be a right place to call a function which will delete ODBC records. And probably I'll write a separate function to be called from Delete event Maybe I can get some hints: 1) how to get information, which records were marked for deletion on form (as all my forms are continous, unlike adding/editing the record, the user can delete several records at once)?; 2) when some records marked for deletion pass the check, and some not, is it better to rollback the whole operation? Or is it better to handle every marked record separately? Arvi Laanemets "Stefan Hoffmann" kirjutas sõnumis news: ... hi Arvi, On 21.04.2010 15:53, Arvi Laanemets wrote: This link refers to almost same solution I found myself. But DBEngine.Errors doesn't work for me. There exists only one item DBEngine.Errors(0) with Number=3001 - "Invalid argument". And it doesn't depend on my actions which caused the error (i.e. deleting linked data or entering some duplicate value). Ah, I see, take a look at http://support.microsoft.com/default...b;en-us;206175 mfG -- stefan -- |
#9
|
|||
|
|||
Overriding constraint violation message
hi Arvi,
On 22.04.2010 09:41, Arvi Laanemets wrote: It was what I was searching for! (Almost.) The function works fine when I'm adding or editing a record. Unfortunatelly it looks like MS has forgotten the case, when records are deleted - so probably I have to find the solution myself. I haven't worked much with recordsets per VBA, so there is a lot of reading to be done. Use the On Delete event of the form. Set Cancel to True and delete the record manually using CurrentDb.Execute "DELETE FROM ...", dbFailOnError mfG -- stefan -- |
Thread Tools | |
Display Modes | |
|
|