Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Temporarily remove referential integrity checks through VBA?

Discussion in 'Information Technology' started by Andrew R, Jul 28, 2006.

  1. Andrew R

    Andrew R
    Expand Collapse
    Guest

    Hi

    I'm creating a series of forms, each with with around 15-20 text boxes.
    The text boxes will show data from tables, but are unbound to make them
    more flexible.

    I want the form to be used for both adding new data and modifying
    existing data. I have created a save button on the form.

    When the user clicks the save button, the code checks to see if there
    is a value in the ID text box. If not, it assumes that the record is
    new and uses an "Insert into..." SQL statement as the ADODB command
    text. This works fine.

    However, if there is already an ID value present, the logic is that the
    record already exists, and therefore we are doing an update. Creating a
    SQL statement along the lines of "UPDATE tblCustomers SET strFirstName
    = " & txtFirstName.text & ", strLastName=" & txtLastName.text
    ...... and so on seems to be a lot of coding to update perhaps 14 fields
    which haven't changed in addition to the one that may have.

    Therefore, my question is this: Is there an easy way to detect which
    field has been changed (without writing code behind each text box,
    given that Access doesn't allow control arrays... It probably seems
    like I'm being lazy, but with probably 8 or so forms, each with 15-20
    text boxes, that would be a lot of work!)

    Assuming not, my other thought was that it would be much simpler to
    simply execute 2 commands -
    "Delete from tblCustomers where ID=" & txtID.text
    and then run the insert statement as before. However, this will of
    course be prevented by the referential integrity constraints on the
    table, thus my question - is there a way through the code to turn off
    checks, run the delete and insert statements, then turn them on again?

    Apologies for being long-winded! Any help gratefully received!

    Regards
    Andrew Richards
     
  2. Loading...


  3. Arno R

    Arno R
    Expand Collapse
    Guest

    "Andrew R" <andrew.richards@cashette.com> schreef in bericht news:1150354744.619090.134670@p79g2000cwp.googlegroups.com...
    > Hi
    >
    > I'm creating a series of forms, each with with around 15-20 text boxes.
    > The text boxes will show data from tables, but are unbound to make them
    > more flexible.
    >
    > I want the form to be used for both adding new data and modifying
    > existing data. I have created a save button on the form.
    >
    > When the user clicks the save button, the code checks to see if there
    > is a value in the ID text box. If not, it assumes that the record is
    > new and uses an "Insert into..." SQL statement as the ADODB command
    > text. This works fine.
    >
    > However, if there is already an ID value present, the logic is that the
    > record already exists, and therefore we are doing an update. Creating a
    > SQL statement along the lines of "UPDATE tblCustomers SET strFirstName
    > = " & txtFirstName.text & ", strLastName=" & txtLastName.text
    > ..... and so on seems to be a lot of coding to update perhaps 14 fields
    > which haven't changed in addition to the one that may have.
    >
    > Therefore, my question is this: Is there an easy way to detect which
    > field has been changed (without writing code behind each text box,
    > given that Access doesn't allow control arrays... It probably seems
    > like I'm being lazy, but with probably 8 or so forms, each with 15-20
    > text boxes, that would be a lot of work!)
    >
    > Assuming not, my other thought was that it would be much simpler to
    > simply execute 2 commands -
    > "Delete from tblCustomers where ID=" & txtID.text
    > and then run the insert statement as before. However, this will of
    > course be prevented by the referential integrity constraints on the
    > table, thus my question - is there a way through the code to turn off
    > checks, run the delete and insert statements, then turn them on again?
    >
    > Apologies for being long-winded! Any help gratefully received!
    >
    > Regards
    > Andrew Richards


    Removing RI is *not* the way to go
    Deleting the tblCustomers-record is *not* the way to go

    These are both very bad and dangerous idea's IMO

    What is wrong with bound textboxes??
    You could edit, add, delete, cancel changes and so on.
    You could save yourself a lot of coding indeed! and... you would not have these problems.

    Arno R
     
  4. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi, Andrew.

    > The text boxes will show data from tables, but are unbound to make them
    > more flexible.


    That extra flexibility comes at the risk of compromising data integrity and
    at a cost of extra work on the database developer's part. Are you sure that
    you can't do what you need to do with bound forms?

    > Is there an easy way to detect which
    > field has been changed (without writing code behind each text box,


    Yes. You can do a visual inpection of the records first. But SQL (or even
    VBA code) to update these records would be far more efficient if you ask me.

    > It probably seems
    > like I'm being lazy, but with probably 8 or so forms, each with 15-20
    > text boxes, that would be a lot of work!)


    Uh, . . . isn't that what you're getting paid for? To do hours and hours of
    work? Every day you come to work? ;-)

    > is there a way through the code to turn off
    > checks, run the delete and insert statements, then turn them on again?


    Jet doesn't have deferred constraints like client/server databases do, so
    you'll have to drop the constraints, not just "turn them off." Honestly, an
    update query is the best way to handle this, but if you want to do it the
    hard way, you can delete, then insert the appropriate records, but you'll
    have to take special precautions.

    First, back up the database in case something goes wrong. Next, create a
    table level write lock on these related tables, because you don't want other
    users to be changing data while referential integrity isn't being enforced.
    (Alternatively, you can open the database in exclusive mode if the users
    won't tar and feather you for blocking them from doing their work.) Drop
    the foreign key constraints between these tables. Delete the record and
    insert a new one with the same ID for the primary key. Repeat for as many
    records as needed, then recreate the foreign key constraints again.

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
    http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
    info.


    "Andrew R" <andrew.richards@cashette.com> wrote in message
    news:1150354744.619090.134670@p79g2000cwp.googlegroups.com...
    > Hi
    >
    > I'm creating a series of forms, each with with around 15-20 text boxes.
    > The text boxes will show data from tables, but are unbound to make them
    > more flexible.
    >
    > I want the form to be used for both adding new data and modifying
    > existing data. I have created a save button on the form.
    >
    > When the user clicks the save button, the code checks to see if there
    > is a value in the ID text box. If not, it assumes that the record is
    > new and uses an "Insert into..." SQL statement as the ADODB command
    > text. This works fine.
    >
    > However, if there is already an ID value present, the logic is that the
    > record already exists, and therefore we are doing an update. Creating a
    > SQL statement along the lines of "UPDATE tblCustomers SET strFirstName
    > = " & txtFirstName.text & ", strLastName=" & txtLastName.text
    > ..... and so on seems to be a lot of coding to update perhaps 14 fields
    > which haven't changed in addition to the one that may have.
    >
    > Therefore, my question is this: Is there an easy way to detect which
    > field has been changed (without writing code behind each text box,
    > given that Access doesn't allow control arrays... It probably seems
    > like I'm being lazy, but with probably 8 or so forms, each with 15-20
    > text boxes, that would be a lot of work!)
    >
    > Assuming not, my other thought was that it would be much simpler to
    > simply execute 2 commands -
    > "Delete from tblCustomers where ID=" & txtID.text
    > and then run the insert statement as before. However, this will of
    > course be prevented by the referential integrity constraints on the
    > table, thus my question - is there a way through the code to turn off
    > checks, run the delete and insert statements, then turn them on again?
    >
    > Apologies for being long-winded! Any help gratefully received!
    >
    > Regards
    > Andrew Richards
    >
     
  5. Terry Kreft

    Terry Kreft
    Expand Collapse
    Guest

    You either use a bound form and save yourself a bunch of coding but you are
    tied to the bound way of doing things

    or

    You use unbound and have to do a bunch of coding but you have the
    flexibility to do exactly what you want.

    You've chosen unbound and now you have to bite the bullet and write the
    code.

    Try thinking about writing a SQL builder function which you can use from any
    of your forms. You pass the tablename and the values from your fields and
    let the SQL builder function build the SQL for the update. Do it once, get
    it right and then just use it.

    --

    Terry Kreft


    "Andrew R" <andrew.richards@cashette.com> wrote in message
    news:1150354744.619090.134670@p79g2000cwp.googlegroups.com...
    > Hi
    >
    > I'm creating a series of forms, each with with around 15-20 text boxes.
    > The text boxes will show data from tables, but are unbound to make them
    > more flexible.
    >
    > I want the form to be used for both adding new data and modifying
    > existing data. I have created a save button on the form.
    >
    > When the user clicks the save button, the code checks to see if there
    > is a value in the ID text box. If not, it assumes that the record is
    > new and uses an "Insert into..." SQL statement as the ADODB command
    > text. This works fine.
    >
    > However, if there is already an ID value present, the logic is that the
    > record already exists, and therefore we are doing an update. Creating a
    > SQL statement along the lines of "UPDATE tblCustomers SET strFirstName
    > = " & txtFirstName.text & ", strLastName=" & txtLastName.text
    > ..... and so on seems to be a lot of coding to update perhaps 14 fields
    > which haven't changed in addition to the one that may have.
    >
    > Therefore, my question is this: Is there an easy way to detect which
    > field has been changed (without writing code behind each text box,
    > given that Access doesn't allow control arrays... It probably seems
    > like I'm being lazy, but with probably 8 or so forms, each with 15-20
    > text boxes, that would be a lot of work!)
    >
    > Assuming not, my other thought was that it would be much simpler to
    > simply execute 2 commands -
    > "Delete from tblCustomers where ID=" & txtID.text
    > and then run the insert statement as before. However, this will of
    > course be prevented by the referential integrity constraints on the
    > table, thus my question - is there a way through the code to turn off
    > checks, run the delete and insert statements, then turn them on again?
    >
    > Apologies for being long-winded! Any help gratefully received!
    >
    > Regards
    > Andrew Richards
    >
     
  6. Keith Wilby

    Keith Wilby
    Expand Collapse
    Guest

    "Andrew R" <andrew.richards@cashette.com> wrote in message
    news:1150354744.619090.134670@p79g2000cwp.googlegroups.com...
    >
    > Therefore, my question is this: Is there an easy way to detect which
    > field has been changed (without writing code behind each text box,
    > given that Access doesn't allow control arrays... It probably seems
    > like I'm being lazy, but with probably 8 or so forms, each with 15-20
    > text boxes, that would be a lot of work!)
    >


    I wrote a function to do this with any form for an audit trail, you just
    pass the form to the function along with the record's unique ID. Here's an
    extract from the code, you may be able to adapt it and probably make it more
    elegant in the process:

    Calling code:
    Call libHistory(Me, Me.txtID)

    Public Function libHistory(frmForm As Form, lngID As Long)

    Dim ctl As Control

    For Each ctl In frmForm
    'Ignore controls such as labels
    If ctl.Name Like "txt*" Or ctl.Name Like "cbo*" Or ctl.Name Like
    "ogr*" Or ctl.Name Like "chk*" Then
    'Record null to value, value to null, and value changes
    If ((IsNull(ctl.OldValue) And Not IsNull(ctl.Value)) Or
    (IsNull(ctl.Value) And Not IsNull(ctl.OldValue)) _
    Or ctl.OldValue <> ctl.Value) Then
    'DO STUFF
    End If
    End If
    Next

    End Function

    Regards,
    Keith.
    www.keithwilby.com
     
  7. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Andrew R wrote:
    > Creating a
    > SQL statement along the lines of "UPDATE tblCustomers SET strFirstName
    > = " & txtFirstName.text & ", strLastName=" & txtLastName.text
    > ..... and so on seems to be a lot of coding to update perhaps 14 fields
    > which haven't changed in addition to the one that may have.


    Under the covers, in the engine will perform an update by first
    deleting the old row then inserting a new row with the new values (it
    does it in a more controlled way than you could 'by hand'). Trying to
    detect which column values have changed or otherwise is most likely a
    waste of your time and effort. The engine won't care so why should you?
    Just send all the values across in one hit.

    I can appreciate why you don't like creating a dynamic UPDATE
    statement. As you are using ADO, you could create a PROCEDURE with
    (optional) parameters, using default parameter values to detect values
    'missing' from the call. Post back if you'd like to see an example.

    There are circumstances where you need to disable DRI, CHECK
    constraints, etc temporarily while you complete a set of operations.
    You obviously need to do this in a transaction in case the new data
    prevents you switching them back (i.e. constraints have been violated)
    so you can rollback the changes. However, yours does not sound like one
    of those circumstances.

    Jamie.

    --
     
  8. David W. Fenton

    David W. Fenton
    Expand Collapse
    Guest

    "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@Spameater.orgZERO_SPAM>
    wrote in news:OYrv#WFkGHA.456@TK2MSFTNGP05.phx.gbl:

    > Jet doesn't have deferred constraints like client/server databases
    > do, so you'll have to drop the constraints, not just "turn them
    > off."


    I can't imagine a situation in which this would be advisable. What
    happens if someone else adds non-conformant data to the database
    during the time when the constraints have been turned off?

    Changes to the schema should *never* happen incidentally as a part
    of daily operation of an application. If the application depends on
    that, then there's a horrendously bad design in place and that
    should be fixed.

    --
    David W. Fenton http://www.dfenton.com/
    usenet at dfenton dot com http://www.dfenton.com/DFA/
     
  9. David W. Fenton

    David W. Fenton
    Expand Collapse
    Guest

    "Jamie Collins" <jamiecollins@xsmail.com> wrote in
    news:1150360905.216967.31720@u72g2000cwu.googlegroups.com:

    > There are circumstances where you need to disable DRI, CHECK
    > constraints, etc temporarily while you complete a set of
    > operations.


    I don't agree with this. I don't see any situations where RI should
    be disabled.

    --
    David W. Fenton http://www.dfenton.com/
    usenet at dfenton dot com http://www.dfenton.com/DFA/
     
  10. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    David W. Fenton wrote:
    > > There are circumstances where you need to disable DRI, CHECK
    > > constraints, etc temporarily while you complete a set of
    > > operations.

    >
    > I don't agree with this. I don't see any situations where RI should
    > be disabled.


    Perhaps 'need' was the wrong word; substitute 'may find it conducive'.

    What about the situation where you need to alter a key's value and
    can't use ON UPDATE CASADE on all you tables because there are
    potential cycles or multiple update paths that the engine isn't smart
    enough to resolve?

    Jamie.

    --
     
  11. David W. Fenton

    David W. Fenton
    Expand Collapse
    Guest

    "Jamie Collins" <jamiecollins@xsmail.com> wrote in
    news:1150381949.731547.187990@p79g2000cwp.googlegroups.com:

    >
    > David W. Fenton wrote:
    >> > There are circumstances where you need to disable DRI, CHECK
    >> > constraints, etc temporarily while you complete a set of
    >> > operations.

    >>
    >> I don't agree with this. I don't see any situations where RI
    >> should be disabled.

    >
    > Perhaps 'need' was the wrong word; substitute 'may find it
    > conducive'.
    >
    > What about the situation where you need to alter a key's value and
    > can't use ON UPDATE CASADE on all you tables because there are
    > potential cycles or multiple update paths that the engine isn't
    > smart enough to resolve?


    Then your schema is wrong.

    Fix that and the problem goes away.

    --
    David W. Fenton http://www.dfenton.com/
    usenet at dfenton dot com http://www.dfenton.com/DFA/
     
  12. Bri

    Bri
    Expand Collapse
    Guest

    Keith Wilby wrote:
    > "Andrew R" <andrew.richards@cashette.com> wrote in message
    > news:1150354744.619090.134670@p79g2000cwp.googlegroups.com...
    >
    >>Therefore, my question is this: Is there an easy way to detect which
    >>field has been changed (without writing code behind each text box,
    >>given that Access doesn't allow control arrays... It probably seems
    >>like I'm being lazy, but with probably 8 or so forms, each with 15-20
    >>text boxes, that would be a lot of work!)
    >>

    >
    >
    > I wrote a function to do this with any form for an audit trail, you just
    > pass the form to the function along with the record's unique ID. Here's an
    > extract from the code, you may be able to adapt it and probably make it more
    > elegant in the process:
    >
    > Calling code:
    > Call libHistory(Me, Me.txtID)
    >
    > Public Function libHistory(frmForm As Form, lngID As Long)
    >
    > Dim ctl As Control
    >
    > For Each ctl In frmForm
    > 'Ignore controls such as labels
    > If ctl.Name Like "txt*" Or ctl.Name Like "cbo*" Or ctl.Name Like
    > "ogr*" Or ctl.Name Like "chk*" Then
    > 'Record null to value, value to null, and value changes
    > If ((IsNull(ctl.OldValue) And Not IsNull(ctl.Value)) Or
    > (IsNull(ctl.Value) And Not IsNull(ctl.OldValue)) _
    > Or ctl.OldValue <> ctl.Value) Then
    > 'DO STUFF
    > End If
    > End If
    > Next
    >
    > End Function
    >
    > Regards,
    > Keith.
    > www.keithwilby.com


    Great idea, exept that OldValue only works with Bound controls which
    implies Bound Form and this is an Unbound Form. I use something very
    similar myself in my Audit routine.

    --
    Bri
     
  13. Bri

    Bri
    Expand Collapse
    Guest

    Jamie Collins wrote:
    > David W. Fenton wrote:
    >
    >>>There are circumstances where you need to disable DRI, CHECK
    >>>constraints, etc temporarily while you complete a set of
    >>>operations.

    >>
    >>I don't agree with this. I don't see any situations where RI should
    >>be disabled.

    >
    >
    > Perhaps 'need' was the wrong word; substitute 'may find it conducive'.
    >
    > What about the situation where you need to alter a key's value and
    > can't use ON UPDATE CASADE on all you tables because there are
    > potential cycles or multiple update paths that the engine isn't smart
    > enough to resolve?
    >
    > Jamie.


    I assuming (hoping) that you are referring to a one time maintenance
    issue vs a regular process. In this case you would have kicked everyone
    out of the db, so as long as everything is back in place before you let
    them back in then, yes, you might want to do this.

    I agree with David that you do NOT want to have constraints modified in
    an ongoing process. Its better to define constrains that suit your needs
    and then leave them alone. The OP has been shown several alternatives
    (Terry's function idea is the one I would use), so he shouldn't need to
    consider the constraint option further.

    --
    Bri
     
  14. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi, David.

    > I can't imagine a situation in which this would be advisable.


    For data migrations, legacy data often doesn't conform to the structure of
    the new tables. Client/server databases offer the ability to enable and
    disable constraints, create deferrable constraints, defer constraints
    initially, and validate/not validate existing data when the constraints are
    enabled. In general, it's more efficient to use deferred/non-validated
    constraints to allow non-conforming data to be imported into tables, because
    fixing the data before importing into the tables is often more cumbersome
    and time-consuming without the database engine to manipulate large data
    sets.

    > What
    > happens if someone else adds non-conformant data to the database
    > during the time when the constraints have been turned off?


    As I mentioned in my previous post, special precautions need to be taken:
    "create a table level write lock on these related tables, because you don't
    want other users to be changing data while referential integrity isn't being
    enforced." With a write lock, the users can't alter the data in these
    locked tables. They can only read the data. And if Andrew forgets to lock
    the tables before beginning these operations, he may find that he can't add
    the constraints to the tables again when he's done because the users have
    added data that doesn't comply with the constraints. Andrew won't forget
    the table locks more than once if he takes this approach.

    > Changes to the schema should *never* happen incidentally as a part
    > of daily operation of an application.


    Agreed. This is a DBA function for database maintenance, not something
    that's executed as part of the normal business logic of the application.

    > If the application depends on
    > that, then there's a horrendously bad design in place and that
    > should be fixed.


    Of course it's a bad design for a normal user application. However, if it's
    a DBA tool for migrating data where no users are in the database yet, then
    it's not half as bad as it sounds, because it's a one-time (or occasional)
    utility function. But I think it's much more likely that Andrew's approach
    is intended for the actual day-to-day operations, not a one-time data
    migration.

    As for why I gave the instructions I did, Andrew asked whether or not it
    could be done, so I provided a response in that context. I can give my
    advice that this approach is ill-advised along with instructions on how to
    do it, because I know that:

    1. Some people will consider the steps outlined and, based upon their
    experience, see that this approach is not in their best interest for daily
    operations and therefore consider the other, more viable, options.

    2. Some people will ignore the advice that this is ill-advised since
    they're focused on the ultimate outcome, not the wisest path to reach that
    outcome. Those are the ones that will implement the instructions and get to
    see the consequences first hand. Depending upon how much experience they
    have with relational database applications and how much traffic the database
    gets, the consequences might not be evident to them immediately, but they'll
    eventually see that this approach is not in their best interest.

    And while I'd rather people not make mistakes when it comes to working with
    data, I believe that we need to allow people to make small mistakes so that
    they can learn from them, which will later help them to figure out how to
    avoid many of the collosal mistakes. I know that when I was new to
    relational databases, the experts who trained me let me make mistakes,
    because they were confident that they could fix anything that I screwed up,
    and I'd learn valuable troubleshooting skills along the way. Like most
    people, I made many mistakes, but seeing the results of "this is why we
    _never_ do such-and-such" hammered home the lessons I learned. I got to
    learn how to fix those mistakes myself, so I never need to rely on others to
    notice -- and fix -- my mistakes for me. I don't want to prevent others
    from gaining this valuable experience.

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
    http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
    info.


    "David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message
    news:Xns97E35BD32907Df99a49ed1d0c49c5bbb2@127.0.0.1...
    > "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@Spameater.orgZERO_SPAM>
    > wrote in news:OYrv#WFkGHA.456@TK2MSFTNGP05.phx.gbl:
    >
    >> Jet doesn't have deferred constraints like client/server databases
    >> do, so you'll have to drop the constraints, not just "turn them
    >> off."

    >
    > I can't imagine a situation in which this would be advisable. What
    > happens if someone else adds non-conformant data to the database
    > during the time when the constraints have been turned off?
    >
    > Changes to the schema should *never* happen incidentally as a part
    > of daily operation of an application. If the application depends on
    > that, then there's a horrendously bad design in place and that
    > should be fixed.
    >
    > --
    > David W. Fenton http://www.dfenton.com/
    > usenet at dfenton dot com http://www.dfenton.com/DFA/
     
  15. David W. Fenton

    David W. Fenton
    Expand Collapse
    Guest

    "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@Spameater.orgZERO_SPAM>
    wrote in news:#uJgGYLkGHA.4104@TK2MSFTNGP04.phx.gbl:

    >> I can't imagine a situation in which this would be advisable.

    >
    > For data migrations, . . .


    That's a one-time operation. The question was about having it be a
    regular thing to turn it off and turn it on in order to input
    certain kinds of data. That was also what someone else suggested.

    There is simply on situation that I can think of where turning off
    RI and then turning it back on should be a regular part of the
    process.

    If you need to regularly import data that needs to be massaged to
    remove violations of your RI, then you need temp tables to operate
    on it before it actually gets imported into the live database.

    > . . . legacy data often doesn't conform to the structure of
    > the new tables. Client/server databases offer the ability to
    > enable and disable constraints, create deferrable constraints,
    > defer constraints initially, and validate/not validate existing
    > data when the constraints are enabled. In general, it's more
    > efficient to use deferred/non-validated constraints to allow
    > non-conforming data to be imported into tables, because fixing the
    > data before importing into the tables is often more cumbersome and
    > time-consuming without the database engine to manipulate large
    > data sets.


    I don't see it. Import buffer tables are the way to go, seems to me.

    In any event, I can certainly see doing it once during a massive
    import process. I can't see doing it ever for a live database.

    >> What
    >> happens if someone else adds non-conformant data to the database
    >> during the time when the constraints have been turned off?

    >
    > As I mentioned in my previous post, special precautions need to be
    > taken: "create a table level write lock on these related tables,
    > because you don't want other users to be changing data while
    > referential integrity isn't being enforced." With a write lock,
    > the users can't alter the data in these locked tables. They can
    > only read the data. And if Andrew forgets to lock the tables
    > before beginning these operations, he may find that he can't add
    > the constraints to the tables again when he's done because the
    > users have added data that doesn't comply with the constraints.
    > Andrew won't forget the table locks more than once if he takes
    > this approach.


    This all sounds completely insane to me. I would never pay a
    developer who produced such a monstrosity.

    >> Changes to the schema should *never* happen incidentally as a
    >> part of daily operation of an application.

    >
    > Agreed. This is a DBA function for database maintenance, not
    > something that's executed as part of the normal business logic of
    > the application.


    That's not what the original question was about, though.

    >> If the application depends on
    >> that, then there's a horrendously bad design in place and that
    >> should be fixed.

    >
    > Of course it's a bad design for a normal user application.
    > However, if it's a DBA tool for migrating data where no users are
    > in the database yet, then it's not half as bad as it sounds,
    > because it's a one-time (or occasional) utility function. But I
    > think it's much more likely that Andrew's approach is intended for
    > the actual day-to-day operations, not a one-time data migration.


    I was responding to the question asked, which was about a regularly
    used procedure, not a one-time import. I don't consider a one-time
    removal of RI to import certain data to be part of the regular
    operation of a database. It's an operation done before the schems is
    finished and initialized with data.

    > As for why I gave the instructions I did, Andrew asked whether or
    > not it could be done, so I provided a response in that context. I
    > can give my advice that this approach is ill-advised along with
    > instructions on how to do it, because I know that:
    >
    > 1. Some people will consider the steps outlined and, based upon
    > their experience, see that this approach is not in their best
    > interest for daily operations and therefore consider the other,
    > more viable, options.
    >
    > 2. Some people will ignore the advice that this is ill-advised
    > since they're focused on the ultimate outcome, not the wisest path
    > to reach that outcome. Those are the ones that will implement the
    > instructions and get to see the consequences first hand.
    > Depending upon how much experience they have with relational
    > database applications and how much traffic the database gets, the
    > consequences might not be evident to them immediately, but they'll
    > eventually see that this approach is not in their best interest.


    I don't see why you'd give the advice, given that you knew the
    contemplated scenario was one that you'd never recommend.

    > And while I'd rather people not make mistakes when it comes to
    > working with data, I believe that we need to allow people to make
    > small mistakes so that they can learn from them, . . .


    This is no small mistake, in my opinion. It's a fundamental error in
    the understanding of how the schema should be established and how
    data should be entered into that schema.

    > . . . which will later help them to figure out how to
    > avoid many of the collosal mistakes. I know that when I was new
    > to relational databases, the experts who trained me let me make
    > mistakes, because they were confident that they could fix anything
    > that I screwed up, and I'd learn valuable troubleshooting skills
    > along the way. Like most people, I made many mistakes, but seeing
    > the results of "this is why we _never_ do such-and-such" hammered
    > home the lessons I learned. I got to learn how to fix those
    > mistakes myself, so I never need to rely on others to notice --
    > and fix -- my mistakes for me. I don't want to prevent others
    > from gaining this valuable experience.


    I don't see why one would answer a question as though a different
    question had been asked, which is what it seems to me that you did.

    --
    David W. Fenton http://www.dfenton.com/
    usenet at dfenton dot com http://www.dfenton.com/DFA/
     
  16. Keith Wilby

    Keith Wilby
    Expand Collapse
    Guest

    "Bri" <not@here.com> wrote in message news:rzhkg.32696$iF6.28271@pd7tw2no...
    >
    >
    > Great idea, exept that OldValue only works with Bound controls which
    > implies Bound Form and this is an Unbound Form.


    Hence the phrase "you may be able to adapt it".

    Keith.
     
  17. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    David W. Fenton wrote:
    > >> I don't see any situations where RI
    > >> should be disabled.

    > >
    > > What about the situation where you need to alter a key's value and
    > > can't use ON UPDATE CASADE on all you tables because there are
    > > potential cycles or multiple update paths that the engine isn't
    > > smart enough to resolve?

    >
    > Then your schema is wrong.


    What about where the schema is 'right', in that logically all the
    cascade paths can be resolved, but the engine isn't smart enough to
    work this out. There's a live in another of the Access newsgroups:

    http://groups.google.com/group/micr...sdbdesign/browse_frm/thread/b806c09e1a9cc11e/

    You earlier comment was probably more correct in that you are not
    seeing the situation (how can you tell me my schema is wong when I
    haven't posted one <g>?!)

    Jamie.

    --
     
  18. Andrew R

    Andrew R
    Expand Collapse
    Guest

    In reply to all those of you who replied - thank you. I didn't realise
    that it would stir quite such a vigorous debate! :)

    It's given me some things to think about, and to those who offered more
    advice, I may get in touch again in the next few days.

    Incidentally, a note to Gunny - no, that's not what I'm paid for! I'm
    developing this db for my own needs and to further my knowledge of and
    interest in Access. I'm actually an IT trainer, rather than a database
    developer....

    Thanks again for all your thoughts and contributions....

    Regards

    Andrew
     
  19. Andrew R

    Andrew R
    Expand Collapse
    Guest

    Oh, and incidentally, the database is only for my own use in managing
    clients and projects, so although it is fairly complex, the issues
    which arise in a multi-user environment are not so relevant to this
    particular case (although obviously they *do* impinge on the quality of
    the design as a whole).

    Thanks

    Andrew



    Andrew R wrote:
    > In reply to all those of you who replied - thank you. I didn't realise
    > that it would stir quite such a vigorous debate! :)
    >
    > It's given me some things to think about, and to those who offered more
    > advice, I may get in touch again in the next few days.
    >
    > Incidentally, a note to Gunny - no, that's not what I'm paid for! I'm
    > developing this db for my own needs and to further my knowledge of and
    > interest in Access. I'm actually an IT trainer, rather than a database
    > developer....
    >
    > Thanks again for all your thoughts and contributions....
    >
    > Regards
    >
    > Andrew
     
  20. David W. Fenton

    David W. Fenton
    Expand Collapse
    Guest

    "Jamie Collins" <jamiecollins@xsmail.com> wrote in
    news:1150447283.501036.13850@r2g2000cwb.googlegroups.com:

    > David W. Fenton wrote:
    >> >> I don't see any situations where RI
    >> >> should be disabled.
    >> >
    >> > What about the situation where you need to alter a key's value
    >> > and can't use ON UPDATE CASADE on all you tables because there
    >> > are potential cycles or multiple update paths that the engine
    >> > isn't smart enough to resolve?

    >>
    >> Then your schema is wrong.

    >
    > What about where the schema is 'right', in that logically all the
    > cascade paths can be resolved, but the engine isn't smart enough
    > to work this out. There's a live in another of the Access
    > newsgroups:
    >
    > http://groups.google.com/group/microsoft.public.access.tablesdbdesi
    > gn/browse_frm/thread/b806c09e1a9cc11e/


    Sounds like an adjustment to the schema is needed. I've had circular
    relationships in Jet databases (even replicated) and have never had
    a problem. Perhaps one solution is changing the FK to allow Null and
    not be required is all that's needed, since I've never had a
    circular relationship with a required FK value in the chain.

    Of course, I also don't believe in cascading updates, since I am
    philosophically completely opposed to using PKs that are ever
    updated (I'm against natural keys in all but the most trivial cases,
    such as lookup tables).

    Either of those alterations to the schema should make it work.

    > You earlier comment was probably more correct in that you are not
    > seeing the situation (how can you tell me my schema is wong when I
    > haven't posted one <g>?!)


    If the schema necessitates regularly turning off RI, then it's
    wrong. I don't need to know anything else about it.

    --
    David W. Fenton http://www.dfenton.com/
    usenet at dfenton dot com http://www.dfenton.com/DFA/
     
  21. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    David W. Fenton wrote:
    > If the schema necessitates regularly turning off RI, then it's
    > wrong. I don't need to know anything else about it.


    We're fortunate in Access/Jet that the engine is quite good at
    resolving cascade paths. Much better, for example, than SQL Sever: as
    soon as it encounters two paths it chokes i.e. the classic adjacency
    list, a popular common/popular design choice for trees in SQL, cannot
    support cascading updates in SQL Server.

    It would be a complex design in Access/Jet that would cause the engine
    to choke and therefore there would be a lot of scope for alternative
    design choices. The more complex the design the more difficult it is to
    say for sure that the design is 'wrong' because of the many design
    choices and compromises between logical model and physical
    implementation which inevitably have been made along the way.

    Temporarily suspending DRI in Access/Jet is not desirable (e.g. can I
    create a serialized transaction that would prevent others making schema
    changes and allow me to rollback mine? I really don't know) and I've
    never had to do it myself but I stop short of saying that doing so is
    always wrong.

    > I also don't believe in cascading updates, since I am
    > philosophically completely opposed to using PKs that are ever
    > updated (I'm against natural keys in all but the most trivial cases,
    > such as lookup tables).


    Some people choose to use natural keys in DRI. Are you saying they are
    'wrong'?

    > Sounds like an adjustment to the schema is needed. I've had circular
    > relationships in Jet databases (even replicated) and have never had
    > a problem. Perhaps one solution is changing the FK to allow Null and
    > not be required is all that's needed, since I've never had a
    > circular relationship with a required FK value in the chain.


    I've having trouble envisaging what you mean. Could you post your
    schema and some test data, please.

    I posted mine (or rather, that OP's) in the other thread. Perhaps you
    could also post a 'fix' to that schema too?

    TIA,
    Jamie.

    --
     

Share This Page