Solved

Could you point how to deal with this intricated Relational Integrity when using MS-Access ?

Posted on 2016-08-23
16
32 Views
Last Modified: 2016-08-24
Hi Experts

Could you point how to deal with this intricated Relational Integrity when using MS-Access ?

Accordingly to
 img001
Three columns of tblBooks must have relational integrity with tblAutores at same time, but just one (AutorEncarnadoIndex)  or two(AutorEspiritualIndex and AutorEncarnadoIndex) must be filled at same time.

If only one of  the 03 columns has "0" value the a insertion could not be performed.

If the relational integrity isn't flagged  to all the 03 columns an author that is still been used in a not relationed column, it can be deleted.

Any suggestion?

Thanks in advance.
0
Comment
Question by:Eduardo Fuerte
  • 8
  • 8
16 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 41767736
From what I can understand, you need RI to the three columns in tblBook. Each Author index need to related to the Author index - correct?

In that case you must create three separate RI relationships. So you add the Author table three times and create a relationship to each of the three Author columns in your tblBooks.


Kelvin
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 41767741
In addition to my post above, you should not use a default of 0 for no value. RI accepts the use of NULL for columns with no value. 0 Implies that it is a value - which in this case it technically incorrect.


Kelvin
0
 

Author Comment

by:Eduardo Fuerte
ID: 41767850
Hi


So I put 03 copies of tblAutores, each one with a RI to one of the tblBooks columns.
Accordingly to:

img002
And changed the default values to null for the 03 columns in tblBooks.

If all the three columns in tblBooks are filled, Ok.

But if I fill just one column, the error arises (using C#).

img003
0
 

Author Comment

by:Eduardo Fuerte
ID: 41767856
Exception translated:

It's not possible to add ou modify register, since it's necessary it have a register related in tblAutores
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 41767869
Are you able to edit the row directly in Access? Are you running some sort of data layer between Access and C# (Entity Framework or similar)?


Kelvin
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 41767873
Can you also open tblBooks in design and check that the three Index values do not have required set to Yes.


Kelvin
0
 

Author Comment

by:Eduardo Fuerte
ID: 41768563
Hi


I'm using C# with OleDb - not using data layer.

I can edit the lines directly inside Access.

(The Access MDB file  is attached here, in case you want to have a look)
MasterFile.mdb
0
 

Author Comment

by:Eduardo Fuerte
ID: 41768566
... and the columns have the required clause set to no.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Eduardo Fuerte
ID: 41768581
and just in case, RI is fired when editing the line directly inside Acceess

(better MDB file)
MasterFile.mdb
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 41769203
If you can edit the records directly in the database exactly as you try via your application, then I believe that the problem lies in your application and not the database. I am not a C# developer, so have a limited ability to assist you here.


Kelvin
0
 

Author Comment

by:Eduardo Fuerte
ID: 41769227
@Kelvin

If I edit the line record directly in MS-Access the same error arises, so the problem isn't related to C# itself.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 41769269
Ah, OK I'll check out your db shortly and advise
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 41769287
Database password?
0
 

Author Comment

by:Eduardo Fuerte
ID: 41769299
Sorry

lib2006
0
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 500 total points
ID: 41769320
OK, have edited the database. I removed all DEfault values from the fields in question - just leave blank for NULL. I have also edited the relationships diagram to remove duplicate relationships to these.
MasterFile.mdb
0
 

Author Closing Comment

by:Eduardo Fuerte
ID: 41769412
Perfect!

Thank you very much.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now