Referential integrity question

[BeachRock]

I'm only beginning to learn Access and I'm wondering why or, for what reason would you ever not enforce referential integrity between table relationships and enable both cascade update and delete? It seems like these should be turned on automatically and you would want to turn them off for those few relationships that you don't need them for instead of vice-versa. I don't understand why you wouldn't always turn it on with both cascade update and delete enabled.



Thanks for any insight you can shine down upon me.

[ItsMe]

Access provides an RDBMS. So, open Access to behave as a management console for your database (Access does other things to). One responsibility of an RDBMS is to manage constraints on the data. Access offers some tools via the Relationships Window to maintain referential integrity. If you choose not to use the tools offered by Access, you need to manage referential integrity another way.

[BeachRock]

Thanks for the reply, ItsMe. If I understand correctly, you are suggesting that referential integrity and cascade update and delete should always be used or, they should otherwise be managed in some other way, like by using SQL or other RDBMS. So, the only reason to not let Access manage them is if they are being managed by something else?

[ItsMe]

... the only reason to not let Access manage them is if they are being managed by something else?

The simple answer is, yes. To be more specific, you can have a hybrid of ways. I look at Access as being two basic things. On one side, you have an RDBMS. On the other side, you have an Application Development tool (in the case of Access, a Rapid Application Development tool (RAD)).

If Access is not managing constraints on the RDBMS side, you need to manage them from the Application side.

I prefer to not use the Relationships window for anything at all. I diagram my entities on third party software and I manage many constraints via VBA.

You need to understand what works for you. I have my ways and others may use a different approach.

[orange]

Interesting comments on cascade update here.
I like to work from a data model and test it with sample data and scenarios before developing. I use Referential Integrity via relationships and let the database do what it was designed to do. However, I see lots of people who do not know what relationships are using Access and other database systems. But then I see lots of people who start with a form and try to develop from that.
Maintaining integrity via vba is possible, just more work in my view.

Suffice it to say --different approaches by many people.
Attitudes change with experience.
I suggest you look for best practices and use that as a guide. And be prepared to change as your skills evolve.

For good access tips see Allen Browne

There are many excellent free videos on youtube that can reduce learning time.

[BeachRock]

Thanks, ItsMe. I understand completely. I'm sure as I become more adept at the basics of Access, I'll delve into other ways to do things in order to provide some function I can't seem to make Access do for me otherwise.

[orange]

ItsMe,

What 3rd party software are you using for modelling?

[ItsMe]

I have been using this for a little over a year now.
https://www.lucidchart.com/

So it does not interact with your development environment. It is really just an aid to draft stuff and that is attractive to me. This works well as a whiteboard and for Access. Otherwise, I will rely on Visual Studio and the MVC Entity Framework.
http://www.asp.net/mvc/overview/gett...vc-application

[ItsMe]

Thanks, ItsMe. I understand completely. I'm sure as I become more adept at the basics of Access, I'll delve into other ways to do things in order to provide some function I can't seem to make Access do for me otherwise.

Good luck with your endeavors and welcome to the forum.

[orange]

I've been using ErWin Community Edition to build some draft models in some responses. I used it years ago when it was owned by Logic Works. I'm trying to learn how to properly move from model to ODBC generic database. I have some code to take generated scripts etc. and create an Access database, but the product can do much more. There are some youtube videos but most is geared to SQL Server, Oracle.... Access used to be a target database, but that has been dropped in the releases and different owners (All Fusion, Computer Associates and now ERWin a separate spinoff) since we first brought it into a government organization in the early '90s.
My use and interest now is more hobby and assistance in the forum(s).

[ItsMe]

@ orange
That might be something of interest to me. I have been using various tools via SQL Server Management Studio, Visual Studio, and custom tools that use either of the development environments.

A typical scenario for me is to generate SQL Server script from an Access file using a free utility (Microsoft SQL Server Migration Assistant for Access). So, I might start with an ERD and use Access to bring the ERD to life. Then after testing the ERD with queries, I will use the utility to generate the SQL Server script. The term, "Migration Assistant", is a bit of a misnomer because it is not useful for migrating data. It is only good for basic table structure. And even with that, you want to come back and clean things up.

[orange]

I haven't used these --- SQL Server Management Studio, Visual Studio.
Since retiring, I'm not in the environment to undertake them, but every now and then....

[ItsMe]

...
Since retiring, I'm not in the environment to undertake them, but every now and then....

I believe a prerequisite for me retiring will be working with the Universal Windows Platform (UWP) and .NET technology stacks.