Results 1 to 3 of 3
  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.

    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?

    Thanx for your help,
    Bye...

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You would be better off posting access questions on the Access thread in this site
    Having said that your main issue is how you have defined your data model.
    Your model infrs that a single pupil wll register in response to a single advert. You have defined the link as a delete cascase

    one option is to have a category in you advert which corresponds to 'no advert' change all instances of the old advertID to the 'no advert ID' by using an update query
    eg
    update mytable set advertid=null where advertid=oldvalue
    or
    update mytable set advertid=newvaluel where advertid=oldvalue

    another option wold be to chnage your data model to use a cross reference table
    eg
    AdvertID } primary Key (fk in table adverts)
    PupilID } (fk in table pupil)
    that way round a pupil could respond to amny adverts, deleting an advert doesn't delete a pupil
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2006
    Posts
    3

    I wanna do it without programming

    Hi again,

    Quote Originally Posted by healdem
    update mytable set advertid=null where advertid=oldvalue
    or
    update mytable set advertid=newvaluel where advertid=oldvalue
    I am creating the used DB in VB (by code). So I am creating a relation with the property DeleteRule set to adriSetToNull. When I afterwards open access without any programming language and delete one record in the adverts table, no student is deleted.

    But when I create the DB in Access (not in VB by code), I have to create the relations, but I don't know, how to create such a relation, which doesn't delete the student. I think there is a setting in access which can be set to create the required relation.

    So I am asking how to do it in access? Without using any programming language and without using SQL. I just want to open the DB in access, delete the record in the adverts table. No student should be deleted. I want to be able to create such a relation in MS-Access 2000.

    So I would be pleased, if you could help me with this.
    Bye...

Posting Permissions

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