Error trapping ms access

[niktsol1978]

Hello, everybody. I would like to ask some questions about error trapping in MS Access. Suppose i have a table, and i set up an index for a specific field to non duplicates. While i am in data enter mode, in a form, i use the Form Error event to trap the duplication. Is this a good method or while the database grows up, the speed will be decreased eventually? I have seen many database examples over the internet in which the duplication is checked through a dlookup function.

Which way is better and more robust in terms of speed ?

Thank you in advance

[Uncle Gizmo]

While i am in data enter mode, in a form, i use the Form Error event to trap the duplication

It's not considered good practice to rely on an error event to control something that happens in your code, something that you can can control in a different way.

Mind you it's usually the easiest way, and you will find that most people do it!

I've seen examples on Allen Brownes website that use error code to control things, which was a bit of a surprise.

Bearing in mind what I said above, the correct way to do it is to check to see if the record already exists in the table. Programmers often prefer this method because they can provide their own error message instead of the default error message supplied by MS Access which can be a bit obscure.

Sent from my Pixel 3a using Tapatalk

[niktsol1978]

Yes, to be honest i was thinking the same idea myself.I also thought the same thing in the relationships. I don't use the relationship window,i create on the fly the relationships between two tables (inner, left, right joins) and control the correct data integrity through form data entry. Besides, i think that the Enforce Referential Integrity options for delete-updates cascades are not available in linked tables are they?

Anyway, thanks for the immediate and instant answer!!

[isladogs]

The main purpose of using relationships is to enforce referential integrity.
If you do create a relationship, the join will automatically be shown in queries but it can be modified.

Relationships are created in the source database. Trying to create a relationship/RI in the FE for a linked table will not work.
I have an extended article on this topic at http://www.mendipdatasystems.co.uk/r...ps1/4594533224

[Bob Fitz]

IMHO it would be best to use the forms BeforeUpdate event to validate the data. If the proposed data is invalid then a message can be displayed to the user and the update can be cancelled.

Sorry. Posted this in the wrong thread

[pbaldy]

In my view, error trapping is for unknown/unexpected errors. Things you can anticipate, like duplicate records, I would test for in my code. One advantage is that you can alert the user right away, rather than after they've entered a bunch of other data and tried to save the record. That's not to say I wouldn't have relationships/referential integrity. I would, they're just the last line of defense.

[davegri]

Hmm. I've always thougt Referentail Integrity was my first line of defense against inconsistent data linkages.

[pbaldy]

Hmm. I've always thougt Referentail Integrity was my first line of defense against inconsistent data linkages.

For you it may be, or maybe it's semantics. I prefer to try to catch issues during data entry than let an error occur when they try to save the record. Sometimes the error messages from the database engine are too cryptic for the user to understand. I consider it the last line of defense because I'll have tried to stop things from getting to that point first.

[niktsol1978]

In generall, MS Access has a lot of guidance-wizards for many issues, including error message etc. Personally i prefer using the relationship window, mostly for graphical representation of the related tables in order to verify with the x client that the project runs to the desired destination. I had some novice friends of mine that mostly used wizards, having no idea from VBA etc. I like to make the parameteres myself. Mostly it depends from the design. Guys, i wanna thank you for the instant replies.I haven't been involved with Access since mid 90s (oh yes Access Basic the father of VBA, for the older here to recall), but i had the same principles in coding.

I am starting again the research for create a large App on Access with Jet DB Engine as a back end for now, with future purporse the migration to a SQL Server. Thanks again and i am looking forward to post new threads with my project

[niktsol1978]

The main purpose of using relationships is to enforce referential integrity.
If you do create a relationship, the join will automatically be shown in queries but it can be modified.

Relationships are created in the source database. Trying to create a relationship/RI in the FE for a linked table will not work.
I have an extended article on this topic at http://www.mendipdatasystems.co.uk/r...ps1/4594533224

What about if the tables are linked from another RDBMS like SQL Server for example?
What about the case

[isladogs]

What about if the tables are linked from another RDBMS like SQL Server for example?
What about the case

Did you mean to write more than this?

The situation is exactly the same whether it’s an Access BE or a SQL Server BE

[niktsol1978]

Did you mean to write more than this?

The situation is exactly the same whether it’s an Access BE or a SQL Server BE

I think that if i link the table from the DB created in SQL Server, to MS Access FE then in relationship window the Referential Integrity options are not available. I tried it a couple of years ago. If there is any improvements or changed something in later versions, please let me know

[isladogs]

Referential integrity is always set in the BE whether that is SQL Server or Access based