Linked SQL Tables don't report Cascading Updates and Deletes

Discussion in 'Access General' started by bdp@gmail, Jul 31, 2013.

  1. bdp@gmail

    bdp@gmail Guest

    In the 'old days' when it was only Access, and no other server engine, whenyou deleted linked tables with cascading updates turned on it would warn you, "You're about delete this record, and a bunch of records in these othertables" or something like that. But now, with the same tables, but the relationships defined on the server, you hit delete, it deletes the record, all the associated linked records, and boom it's done! No warning! No thanks, no nothing!

    How can I have Access raise a flag on linked SQL tables that have a cascading updates/deletes relationship setup on the SQL backend?

    Seems like when I'm in the SQL Management Studio, whatever it's called, andI issue a query to delete like that it tells you that there are related tables, are you sure?

    Any ideas?

    Thanks.

    -Brian P.
     
    bdp@gmail, Jul 31, 2013
    #1
    1. Advertisements

  2. bdp@gmail

    Phil Hunt Guest

    I am not sure about this.

    You may have to set up a relationship INSIDE Access also ?


    In the 'old days' when it was only Access, and no other server engine, when
    you deleted linked tables with cascading updates turned on it would warn
    you, "You're about delete this record, and a bunch of records in these other
    tables" or something like that. But now, with the same tables, but the
    relationships defined on the server, you hit delete, it deletes the record,
    all the associated linked records, and boom it's done! No warning! No
    thanks, no nothing!

    How can I have Access raise a flag on linked SQL tables that have a
    cascading updates/deletes relationship setup on the SQL backend?

    Seems like when I'm in the SQL Management Studio, whatever it's called, and
    I issue a query to delete like that it tells you that there are related
    tables, are you sure?

    Any ideas?

    Thanks.

    -Brian P.
     
    Phil Hunt, Jul 31, 2013
    #2
    1. Advertisements

  3. bdp@gmail

    Tony Toews Guest

    I didn't like that message because it was so easy for the user to not
    notice the "a bunch of records" part. Instead I handled it all
    myself in code. It only takes about two lines per child table.

    DELETE * FROM <table name> WHERE <ForeignID>=" & <ParentID>
    Currentdb.execute strsql, dbfailonerror.

    That said if there were child records I might display a count of the
    number of records and get a confirmation from the user. Or I would
    just flag the parent record as inactivated.

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files
    updated see http://www.autofeupdater.com/
     
    Tony Toews, Aug 1, 2013
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.