Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Referential integrity (Access 2000)

    I cannot make a referential integrity with cascading Delets
    for 2 tables, tbl offers and tbl offerdetails.I can create this only when the tables are blank.But i am importing these tables from 10 sources, then update and append them, and then i want to make the referential integrity again, but i fail then.How can i avoid this?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referential integrity (Access 2000)

    You can't enforce referential integrity if the data in the tables violates it, and the sample you posted has a record in tblOfferDetails that does not have a match in tblOffers. If you enforce referential integrity on the empty tables and then append data to tblOffers, you will only be able to append records to tblOfferDetails that have a parent record in tblOffers. Isn't this what you want to accomplish?
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referential integrity (Access 2000)

    i have 10 offers in the tblOffers and also 10 offers in the table tblofferdetails.But in one offer there are several offer details under one and the same
    number, one and the same offer.if i have blank tables, then i can enforce the integrity and then i get the results you have seen on the tables.
    I think all the details in the table tblofferdetails have the parent record in the tbloffers.Otherwise i will not be able to make the integrity with blank tables.
    You have asked me what i am trying toi accmplish.I am trying to delete some offers from a given date, and if i have enforced referential integrity then through cascading i will delete all the related records in the table tbloffer details.For example i i want to delete offer number 1, all the offers with number 1 in the tbloffer details will be deleted too.
    I am sure after i have told you what i am aiming to you will be able to help me

    regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referential integrity (Access 2000)

    You write
    <hr>i have 10 offers in the tblOffers<hr>
    but there are only 9: there is no record with OfferID = 9. But tblOfferDetails contains a record with OfferID = 9. This is the one that prevents you from setting referential integrity. Before you can set referential integrity on the existing tables, you must either append a record with OfferID = 9 to tblOffers, or delete the record with OfferID = 9 from tblOfferDetails.

    Since OfferID is an AutoNumber field in tblOffers, you can't add a record with OfferID = 9 manually, but you can use an append query for this. The SQL for this query is

    INSERT INTO TblOffers ( offerid )
    SELECT 9 AS offerid;

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referential integrity (Access 2000)

    Thank you so much to both of you !! I see now clearly the way how shall i proceed! Thank you

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referential integrity (Access 2000)

    You've got it the wrong way round in the SQL: you're trying to delete records from tblOffers that have no match in tblOfferDetails. Try this:
    <code>
    SQL = "DELETE tblofferdetails.*, tblOffers.offerid" & _
    " FROM tblOffers RIGHT JOIN tblofferdetails ON tblOffers.offerid = tblofferdetails.offerid" & _
    " WHERE (((tblOffers.offerid) Is Null));"
    </code>
    Note that this is a right join instead of a left join, and that we check that the value in tblOffers is null, instead of the value in tblOfferDetails.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referential integrity (Access 2000)

    Dear Hans,

    I understand now that i have to remove all the records form the table tblofferdetails
    that have no match in the table tblOffers.In my case this is the record with offerid = 9
    I have built the folloiwng function
    Public Function DeleteWithoutMatchingOffers()
    Dim SQL As String
    SQL = " DELETE [tblofferdetails].OfferID, tblOffers.* " & _
    " FROM tblOffers LEFT JOIN [tblofferdetails] ON tbloffers.offerid = [tblofferdetails].OfferID " & _
    " WHERE ((([tblofferdetails].OfferID) Is Null));"
    CurrentDb.Execute SQL
    End Function

    Why the record with ooferid = 9 is not deleted from tblofferdetails?
    I apply the db again

  8. #8
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referential integrity (Access 2000)

    Thank you. I should have seen that.
    I wish you all the best

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •