Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2006
    Posts
    3

    Unanswered: Set delete rule in Access 2000

    Hi there,

    I am a programmer and I am using Access 2000 as database. In my database I have lots of tables, with many Indices and relations. I need help in setting the relations in Access 2000 (Actually I am creating the DB by VB Code. I want to be able to create it in Access 2000 without any coding).

    I have a Table called Advertisments and a Table called Students. When a Student is registering to the school, he tells about how he had heared about the school (ist a private school which is doing advertising on the internet, on newspapers etc.). So there is a One-To-Many relation between this two tables (One advertisment can be set to many students).

    But when I delete an Advertisment from the Adverts table, the student is also deleted. But I don't want to delete the student; instead i want to set the AdvertID field of the student table be set to null.

    With ADO I can use the DeleteRule property and set it to adriSetToNull. BUT HOW CAN I SET THIS DIRECTLY IN ACCESS 2000, WITHOUT THE USE OF ANY PROGRAMMING LANGUAGE AND WITHOUT USING SQL?

    I just want to open the adverts table in Access 2000 and when I delete a record in this table, no students should be removed. Just the AdvertID of the students should set to NULL. As I said, I am able to do it in VB using ADO. I want to do it in Access 2000. It isn't enough to create the relation between the tables. I have to set the property, but I don't know how?

    Thanx for your help,
    Bye...

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by koraykazgan
    With ADO I can use the DeleteRule property and set it to adriSetToNull. BUT HOW CAN I SET THIS DIRECTLY IN ACCESS 2000, WITHOUT THE USE OF ANY PROGRAMMING LANGUAGE AND WITHOUT USING SQL?
    Short answer: You can't.

    You COULD alter your schema to include another table relating the two tables together. This would be a poor schema from a best-practice scenario but would provide the functionality you're looking for.

    In reality, you're asking a hell of a lot from access to do anything slightly unsual without using vba OR sql.

    When you set the deleterule property in ADO, it is effectively creating a sql statement...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2006
    Posts
    5
    in your tools, relationship grid when you create a relationship you have the option of setting referential integrety, cascade update related fields, and cascade delete related records. You can set your one to many relationship here but don't check any of these 3 options and you should be fine.

  4. #4
    Join Date
    Jan 2006
    Posts
    5
    I have a sample of what I described to you-the one thing I forgot to mention is that in the design of the student table you need the field adID and the type is number and below you will select the type as a combo box that looks up the ADId in the ad table. I sent the bound colum to 1 (for the autonumber set as a primary key) and the number of colums to 2, and the columns width to 0;1" so the colum that you see when you look at the student table in the adID field is actually the ad name, but it stores the adID. I have tested it by adding records to the ad table, then adding students to the student table and selecting the ads in the student table from the drop down box. Then deleting one of the ads that I used from the ads table. There is only a blank in the Student table where the adID goes. I can email this to you if you like-

  5. #5
    Join Date
    Jan 2006
    Posts
    5

    Sample attached

    I have a sample of what I described to you attached-the one thing I forgot to mention is that in the design of the student table you need the field adID and the type is number and below you will select the type as a combo box that looks up the ADId in the ad table. I sent the bound colum to 1 (for the autonumber set as a primary key) and the number of colums to 2, and the columns width to 0;1" so the colum that you see when you look at the student table in the adID field is actually the ad name, but it stores the adID. I have tested it by adding records to the ad table, then adding students to the student table and selecting the ads in the student table from the drop down box. Then deleting one of the ads that I used from the ads table. There is only a blank in the Student table where the adID goes.
    Attached Files Attached Files
    Last edited by Suellen; 01-23-06 at 20:01. Reason: Did not attach file

Posting Permissions

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