 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / June 2006Temporarily remove referential integrity checks through VBA?
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 > Hi > [quoted text clipped - 35 lines] > 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 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.  SignatureTerry Kreft > Hi > [quoted text clipped - 35 lines] > Regards > Andrew Richards 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. > Hi > [quoted text clipped - 35 lines] > Regards > Andrew Richards > 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. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ 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. >> Jet doesn't have deferred constraints like client/server databases >> do, so you'll have to drop the constraints, not just "turn them [quoted text clipped - 8 lines] > that, then there's a horrendously bad design in place and that > should be fixed. >> 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 [quoted text clipped - 6 lines] > 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 [quoted text clipped - 11 lines] > 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 [quoted text clipped - 6 lines] > 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 [quoted text clipped - 14 lines] > 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 [quoted text clipped - 7 lines] > 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. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > 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 >>Therefore, my question is this: Is there an easy way to detect which >>field has been changed (without writing code behind each text box, [quoted text clipped - 32 lines] > 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 > 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. >>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. Well, you gave the function in response to the issue of dealing with controls that had changed their value and your function uses OldValue to do that... so, what is left to adapt? -- Bri > Well, you gave the function in response to the issue of dealing with > controls that had changed their value and your function uses OldValue to > do that... so, what is left to adapt? You know, there will always be smart-arsed know-it-alls on these groups who use their no doubt superior knowledge on a subject to try to humiliate and score cheap points from other contributors. You fall into this category. I always try to help other Access users where I can but my contributions can sometimes be a bit off the mark and I'm happy and grateful to accept corrections where necessary from those with better knowledge, this is how I learn from my mistakes, but when those corrections are offered in a holier-than-thou manner it really pisses me off. I am not so geek-like that I know about every method, every function, every facet of Access. I am also sometimes guilty of reading posts too quickly. In this instance, although I read the "unbound" part I failed to digest it fully which is why I offered the code for adaptation and not as a belt and braces solution. So I made a mistake, so what? I'm sure the OP realised that without your cheap shots. I read your exchange with David Fenton in the "Created on Access 2003, but......................." thread with interest. David is extremely knowledgeable in Access but, IMO, his bedside manner can sometimes leave a little to be desired, but it pales into insignificance compared to yours. This is not something I would be proud of if I were you, which thankfully I am not. <PLONK> >> Well, you gave the function in response to the issue of dealing with >> controls that had changed their value and your function uses OldValue [quoted text clipped - 25 lines] > > <PLONK> Whoa, now!! Back up the truck. I'm sorry if you feel that my reply was an attempt to be a smart a.s and to score cheap points at your expense. I can assure you that that was not my intent. I really was trying to point out that your solution wouldn't work and why it wouldn't for the benefit of the OP primarily and for you as well. You then replied that the OP was to adapt your code for his needs. I then responded that after you remove the OldValue part of the function that there was nothing left that could be used to solve his problem as the OldValue was the key part of that function. I was wondering if, after the OldValue part of the function was dismissed, what you thought was still there to adapt? It was a question, in case I had missed something else in there. I'm sorry you took offense to that. Perhaps I could have written it differently. I'm not sure what there is about my responses that 'pales into insignificance'. Perhaps, you would explain to me the error of my ways? I certainly don't think of myself as hard to get along with, but perhaps there is something in the way I write that comes off that way. I was unaware of it. What could/should I have done differently? In the exchange with David you refer to, he refused to acknowledge that what I said I had done was true, he said I was a lier, and even when faced with several sources of reference to back my story up, he still refused to believe it. I can't see how you would place me as the aggressor in that thread. -- Bri > Whoa, now!! Back up the truck. I'm sorry if you feel that my reply was > an attempt to be a smart a.s and to score cheap points at your expense. > I can assure you that that was not my intent. Fair enough. It did seem a tad aggressive to me, perhaps yesterday was a "bad Day". > I really was trying to > point out that your solution wouldn't work and why it wouldn't for the [quoted text clipped - 6 lines] > was a question, in case I had missed something else in there. I'm sorry > you took offense to that. Perhaps I could have written it differently. I think you could, I read that as being confrontational. As I said before I read the OP very quickly and hadn't realised the significance of the form being unbound and offered the code, warts and all, in case the OP could have made use of it in one way or another. I just thought that 'Great idea, except that ...' came across as sarcasm. > I'm not sure what there is about my responses that 'pales into > insignificance'. Perhaps, you would explain to me the error of my ways? > I certainly don't think of myself as hard to get along with, but perhaps > there is something in the way I write that comes off that way. I was > unaware of it. What could/should I have done differently? I'm not going to attempt to preach to you how you should and should not post on a public forum, I am in no position to do that. Having said that I think that this is the first time I have ranted on here like I did yesterday, perhaps I saw a red rag that wasn't really there. The 'pales' jibe was just my temper talking, please disregard it and accept my apologies. > In the exchange with David you refer to, he refused to acknowledge that > what I said I had done was true, he said I was a lier, and even when > faced with several sources of reference to back my story up, he still > refused to believe it. I can't see how you would place me as the > aggressor in that thread. It came across as two stags locking horns and I guess I thought you were attempting a similar tack with me. You have stated that that was not your intention and I believe you, I would not call you a liar or a lier (yes I did spot that typo first time around). :-) Have a good day, I'm hoping to have a better one than I did yesterday. Regards, Keith. >> Whoa, now!! Back up the truck. I'm sorry if you feel that my reply was >> an attempt to be a smart a.s and to score cheap points at your >> expense. I can assure you that that was not my intent. > > Fair enough. It did seem a tad aggressive to me, perhaps yesterday was > a "bad Day". It happens, hope you're having a better day today. > I think you could, I read that as being confrontational. As I said > before I read the OP very quickly and hadn't realized the significance > of the form being unbound and offered the code, warts and all, in case > the OP could have made use of it in one way or another. I just thought > that 'Great idea, except that ...' came across as sarcasm. Actually, it was sincere. I thought it was great code for an audit trail. In fact, I mentioned that I used something similar myself. > I'm not going to attempt to preach to you how you should and should not > post on a public forum, I am in no position to do that. Having said that > I think that this is the first time I have ranted on here like I did > yesterday, perhaps I saw a red rag that wasn't really there. The > 'pales' jibe was just my temper talking, please disregard it and accept > my apologies. Apology accepted. By pointing out where you saw sarcasm, you did what I was asking. Now I have a reference for where something I wrote was taken differently than intended, I can watch for it in the future. > It came across as two stags locking horns and I guess I thought you were > attempting a similar tack with me. You have stated that that was not > your intention and I believe you, I would not call you a liar or a lier > (yes I did spot that typo first time around). :-) Well, I'm not going to let someone call me a liar (not a typo, spelling is not my strongest suit and the spell checker didn't catch it) and not try to defend myself. If you followed the whole thread, you would have seen that the force of my writing escalated with each reply where he refused to even check the references I supplied. I didn't start out writing to him that way (or at least I didn't intend to). > Have a good day, I'm hoping to have a better one than I did yesterday. > > Regards, > Keith. Having a great day, thanks. And to you. -- Bri keith i applaud david fentons work keith this isn't gradeschool... what are you going to do 'tell on us' for talking like we would in the real world? go back to 2nd grade keith and learn how to deal with people or is keithie scared of a couple of big words?? > > Well, you gave the function in response to the issue of dealing with > > controls that had changed their value and your function uses OldValue to [quoted text clipped - 25 lines] > > <PLONK> >or is keithie scared of a couple of big words?? Aaron. Your postings are inappropriate for these newsgroups. Please leave. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony Your obsolete MDB bullshit doesn't belong on MY FORUM. Grow up and learn a real database engine kid. I'm personally sick and tired of compact and repair. tired of corruption; re-linking.. and all that crap. I have a superior solution. I can create RI through scripts whenver i want. I find it funny the 'groupthink' on this newsgroup people slam people for willing to think outside the box. I find it laughable that Keith was freaking out over nothing. This isn't Sunday school. This is WHERE LIKE-MINDED PEOPLE COME TO DISCUSS AND ARGUE. Maybe if you kids don't like people talking in uppercase letters? Then maybe you kids should have stopped using an obsolete database 5 years ago. Goddamn kids learn a real RDBMS Access Data Projects are superior to MDB in every imaginable way. -Aaron > >or is keithie scared of a couple of big words?? > [quoted text clipped - 9 lines] > Microsoft Access Links, Hints, Tips & Accounting Systems at > http://www.granite.ab.ca/accsmstr.htm That's my insomnia cured. Thanks. > 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. -- > 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. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > > 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. -- >> > There are circumstances where you need to disable DRI, CHECK >> > constraints, etc temporarily while you complete a set of [quoted text clipped - 10 lines] > 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. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > >> I don't see any situations where RI > >> should be disabled. [quoted text clipped - 5 lines] > > 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.tablesdbdesign/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. -- 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 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 > In reply to all those of you who replied - thank you. I didn't realise > that it would stir quite such a vigorous debate! :-) [quoted text clipped - 12 lines] > > Andrew >> >> I don't see any situations where RI >> >> should be disabled. [quoted text clipped - 13 lines] > 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. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > 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. -- >> If the schema necessitates regularly turning off RI, then it's >> wrong. I don't need to know anything else about it. [quoted text clipped - 4 lines] > adjacency list, a popular common/popular design choice for trees > in SQL, cannot support cascading updates in SQL Server. Well, I think cascading updates are a bad thing, as an updatable PK is a bad thing. Secondly, every N:1 relationship can be replaced by an intermediate join table. It's not quite as intuitive as your usual N:N join table (it's a degenerate case of the N:N), but it works just the same. The point is that there are ways to avoid the circular relationship problem by redesigning your schema. It will still reflect the entities being modelled. It just do it in a different fashion. > 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 [quoted text clipped - 3 lines] > and physical implementation which inevitably have been made along > the way. Cascading updates indicates to me that somebody has gone badly wrong in the first place. > 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. It's bloody stupid to have RI in place that has to be suspended in order to insert data. >> I also don't believe in cascading updates, since I am >> philosophically completely opposed to using PKs that are ever [quoted text clipped - 3 lines] > Some people choose to use natural keys in DRI. Are you saying they > are 'wrong'? Yep. Any data that can be edited shouldn't be used as a PK, because the function of the PK is to relate data, not to store information about the entities represented in the table. That doesn't mean that unique indexes shouldn't be maintained on the natural key (which may be composite), but natural keys lead to just this kind of problem. The only exception I would make is for one-column lookup tables, where there is no dependent data (i.e., the PK is the entire data for the entity). >> Sounds like an adjustment to the schema is needed. I've had >> circular relationships in Jet databases (even replicated) and [quoted text clipped - 8 lines] > I posted mine (or rather, that OP's) in the other thread. Perhaps > you could also post a 'fix' to that schema too? I've explained at least three different ways to avoid circular schema problems. None of them is complicated enough to need me to draw you a picture. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > > Some people choose to use natural keys in DRI. Are you saying they > > are 'wrong'? > > Yep. OK, so you think ON UPDATE CASCADE is wrong, should never have been invented and anyone who uses it is wrong. No offence intended but I don't think you think you can ever be open minded to the legitimacy of suspending DRI to manually perform a cascade which the engine is not smart to figure out itself if you don't think the values should *ever* be changed. I am sure you are aware that many SQL gurus use natural keys for their PKs so I won't try to convert you <g>. Jamie. -- > I am sure you are aware that many SQL gurus use natural keys for > their PKs so I won't try to convert you <g>. I think many SQL gurus are more concerned with theory than with ease of use and maintenance. One particular SQL guru strikes as a complete blowhard a.shole. As to natural PKs, well, they cause problems, and that's one of the reasons why surrogate keys are better, because you avoid precisely the kinds of problems encountered with the circular relationships. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > I think many SQL gurus are more concerned with theory than with ease > of use and maintenance. > > One particular SQL guru strikes as a complete blowhard a.shole. Can you imagine Fabian posting to this thread, 'I don't see any situations where DRI should be disabled,' and only after interrogation qualified with, 'because I consider SQL to be a bad thing'? Does this sound like a certain someone's involvement in this thread, David <g>? Jamie. -- >>I think many SQL gurus are more concerned with theory than with ease >>of use and maintenance. [quoted text clipped - 7 lines] > Does this sound like a certain someone's involvement in this thread, > David <g>? What does SQL have to do with referential integrity? > What does SQL have to do with referential integrity? http://en.wikipedia.org/wiki/SQL See the 'Concepts' section. Jamie. -- >>What does SQL have to do with referential integrity? > > http://en.wikipedia.org/wiki/SQL > > See the 'Concepts' section. I didn't see any mention of SQL in the Referential Integrity entry under that topic. f.cking retards SQL Server is more powerful anyone that uses MDB in the year 2006? you should spit on them. out of the blue; just walk up to them and spit. you can have constraints-- you can have triggers-- you can have custom dataTypes > >>What does SQL have to do with referential integrity? > > [quoted text clipped - 4 lines] > I didn't see any mention of SQL in the Referential Integrity > entry under that topic. IF EXISTS (SELECT * FROM newsgroup WHERE user_name = 'aaron') BEGIN DELETE FROM newsgroup WHERE user_name = 'aaron' RAISERROR ('Troll alert - %d talking garbage', 16, 127, 'aaron') END SignatureTerry Kreft > f.cking retards > [quoted text clipped - 15 lines] > > I didn't see any mention of SQL in the Referential Integrity > > entry under that topic. good stuff IF EXISTS (SELECT TOP 1 'X' FROM newsgroup WHERE tool_of_choice = 'MDB') BEGIN RAISERROR ('oh wait a second mdb can't handle real tsql statements; it is for babies', 16, 127, 'horsecrap') END > IF EXISTS (SELECT * FROM newsgroup WHERE user_name = 'aaron') > BEGIN [quoted text clipped - 25 lines] > > > I didn't see any mention of SQL in the Referential Integrity > > > entry under that topic. Don't trolls get bored of themselves ?? Not even sometimes ?? You are a horsecrap baby indeed ... Arno R > good stuff > [quoted text clipped - 34 lines] >> > > I didn't see any mention of SQL in the Referential Integrity >> > > entry under that topic. no i dont get tired. you pansies just look funny dancing around with your pink mdb files oh; look at the little babies playing with MDB.. how CUTE!!! -Aaron > Don't trolls get bored of themselves ?? > Not even sometimes ?? [quoted text clipped - 41 lines] > >> > > I didn't see any mention of SQL in the Referential Integrity > >> > > entry under that topic. > no i dont get tired. I'm sure that one day we'll read a news item about you that concludes "... before turning the gun on himself." How a simple question has exploded! Information AND entertainment - don't you just love newsgroups?! :-) Andrew > > no i dont get tired. > > I'm sure that one day we'll read a news item about you that concludes "... > before turning the gun on himself." > How a simple question has exploded! > > Information AND entertainment - don't you just love newsgroups?! > > :-) > Andrew You should be around when "PC Datasheet" is here. Actually, both trouble-making parties should really be ignored but sometimes some of us give in to temptation too easily ;-) Keith. Keith, Didn't your mother ever warn you not to invoke Trolls by name ? <g> SignatureTerry Kreft > > How a simple question has exploded! > > [quoted text clipped - 8 lines] > > Keith. > Keith, > Didn't your mother ever warn you not to invoke Trolls by name ? > > <g> My mistake m'lud. :-) It has been awfully quiet on that front and I quite like it. It's funny though, isn't it, how some trolls are amusing, especially when they're trying to be obnoxious and others are just plain obnoxious? SignatureTerry Kreft > > Keith, > > Didn't your mother ever warn you not to invoke Trolls by name ? [quoted text clipped - 3 lines] > My mistake m'lud. :-) It has been awfully quiet on that front and I quite > like it. > It's funny though, isn't it, how some trolls are amusing, especially when > they're trying to be obnoxious and others are just plain obnoxious? IIRC Aaron has made some helpful and informative posts in ADP groups. A year or so ago he seemed more temperate, reasoned and capable, ... even. But, he's not contributing much any more; he hasn't even introduced me to any new "words". Life goes on. > It's funny though, isn't it, how some trolls are amusing, > especially when they're trying to be obnoxious and others are just > plain obnoxious? Amusement is in the eye of the beholder. This beholder's eyes are complete devoid of any amusement. If you give a rat's a.s. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ In the eye of the beholder is Amusement. Devoid of any amusement this beholder's eyes are complete. If you give a rat's a.s. SignatureTerry Kreft > > It's funny though, isn't it, how some trolls are amusing, > > especially when they're trying to be obnoxious and others are just [quoted text clipped - 5 lines] > > If you give a rat's a.s. I love the way that you have no compunction about showing how ignorant you are of both transact SQL and Access SQL and the way that you're not afraid of displaying your stupidity in public is really marvelous. SignatureTerry Kreft > good stuff > [quoted text clipped - 34 lines] > > > > I didn't see any mention of SQL in the Referential Integrity > > > > entry under that topic. Terry; screw yourself; I am much better at Access AND Sql than anyone i've ever seen on this newsgroup. And most importantly; I dont just blindly use the first tool the comes across my desk. I use the best tool for whatever I am doing. Most of your idiots are unnecessarily biased agasint Access Data Projects. I find that laughable. -Aaron > I love the way that you have no compunction about showing how ignorant you > are of both transact SQL and Access SQL and the way that you're not afraid [quoted text clipped - 42 lines] > > > > > I didn't see any mention of SQL in the Referential Integrity > > > > > entry under that topic. > I am much better at Access AND Sql than anyone i've > ever seen on this newsgroup. We're all grateful that you have shared your expertise here in CDMA. But some of us may have missed one or two of your better contributions. Would you list the links to a few, say ten, of your creative and original posts so that we can reference them as a small library of excellence? In addition, this may help us to remember to maintain the appropriate level of deferential integrity in our discussions with you. > > I am much better at Access AND Sql than anyone i've > > ever seen on this newsgroup. [quoted text clipped - 7 lines] > In addition, this may help us to remember to maintain the appropriate > level of deferential integrity in our discussions with you. Better yet why not create a compendium of these wonderful ideas in an SQL-Server Table with code and descriptions in Text fields? You could then create and make freely available an ADP which would allow us all to access your wonderful ideas. I'm sure, that you, as El Supremo ADP/SQL can craft your application in such a way that there will be no security breaches or concerns. In that way you can not only show, but also demonstrate the magnificent qualities of the ADP. What an opportunity! for the record sql server security is a LOT better than your silly MDB bullshit. can you kids even prevent me from updating a single table? I'm sorry that you idiots try to TALK_sh.t about people just because they're willing to think OUTSIDE THE BOX. Just because I dont have to compact and repair my database every 3 days?? that makes me less of a man? Because I dont spend half my time re-syncing Tables and Queries.. is that why you call me a wimp? I use a platform that runs circles around you kids. Billions of records with sub-second response times. Don't sit around and talk sh.t just because your obsolete MDB bullshit can't scale to a million records. > > > I am much better at Access AND Sql than anyone i've > > > ever seen on this newsgroup. [quoted text clipped - 16 lines] > In that way you can not only show, but also demonstrate the magnificent > qualities of the ADP. What an opportunity! > for the record > [quoted text clipped - 16 lines] > Don't sit around and talk sh.t just because your obsolete MDB bullshit > can't scale to a million records. Is that a No? You mean you won't share your expertise? What a shame! Ha, ha, ha, it's just marvellous how you have not the slightest piece of self-respect. Please continue to show us how wonderful you are<g>; I've quite given up reading Dilbert as your contributions are so much more originally funny. You might not know much about programming (except in your world of course) but you certainly know how to amuse! I've only seen one piece of code from you and it was so laughably wrong but far too subtle I thought, most people probably thought you meant it to be like that. SignatureTerry Kreft > Terry; > [quoted text clipped - 58 lines] > > > > > > I didn't see any mention of SQL in the Referential Integrity > > > > > > entry under that topic. Terry f.ck yourself > Ha, ha, ha, it's just marvellous how you have not the slightest piece of > self-respect. [quoted text clipped - 78 lines] > > > > > > > I didn't see any mention of SQL in the Referential Integrity > > > > > > > entry under that topic. LOL, your wit almost exceeds your demonstrated technical ability. With considered demonstrable arguments such as this I don't see how anyone could possibly disagree. SignatureTerry Kreft > Terry > [quoted text clipped - 82 lines] > > > > > > > > I didn't see any mention of SQL in the Referential Integrity > > > > > > > > entry under that topic. no self respect? at least I dont use a PUSSY database engine uh compact and repair? in the year 2006? GAG grow some balls kids and stop fighting for the pink team > Ha, ha, ha, it's just marvellous how you have not the slightest piece of > self-respect. [quoted text clipped - 78 lines] > > > > > > > I didn't see any mention of SQL in the Referential Integrity > > > > > > > entry under that topic. I think you've got a spurious ? in there on the first line, wasn't that meant to be a statement? To be honest I'm not sure which database engine you do use as you've done such a good job of obfuscating your technical ability, I know which one you claim, but then we can all claim anything we want, can't we. SignatureTerry Kreft > no self respect? > [quoted text clipped - 89 lines] > > > > > > > > I didn't see any mention of SQL in the Referential Integrity > > > > > > > > entry under that topic. you guys are a bunch of a.sholes and dipshits just because I speak the truth-- that MDB is obsolete-- it doesn't mean that I'm a troll. you guys need to get a life; grow some balls and use a real database engine > I think you've got a spurious ? in there on the first line, wasn't that > meant to be a statement? [quoted text clipped - 108 lines] > > > > > > > > > I didn't see any mention of SQL in the Referential Integrity > > > > > > > > > entry under that topic. I use Access and DB2 -- ADPs are obsolete. However I am glad you like them, I used to but as I could not use them to access DB2 what was the point of them, they tied you to SQL Server, which is not the brightest kid in the class even weaker in some respects than Jet 4. BTW DB2 Express-C is free and is very very good, I remember you were abusive to me about that a few months back and I forgot to mention it did not cost thousands of dollars as you alleged. Free to develop, deploy and distribute. Your abusive posts are no doubt the result of frustration about the demise of ADPs, but you do not see me and others being abusive about people who use SQL Server 2005. Craig Alexander Morrison Crawbridge Data (Scotland) Limited > you guys are a bunch of a.sholes and dipshits > [quoted text clipped - 126 lines] >> > > > > > > > > Integrity >> > > > > > > > > entry under that topic. > I use Access and DB2 -- ADPs are obsolete. Could you expand? How do you connect? Your forms and reports are bound? How do you manage the DB2 database? What are storage limits? Can you use on a web-site and/or across the internet? What's good/not so good? > Could you expand? I will. > How do you connect? I connect via ODBC. > Your forms and reports are bound? Yes > How do you manage the DB2 database? The database is managed using the DB2 Control Center or the DB2 CLP (command line processor, this is groovy (wg) if you grew up with the DOS Prompt.) There are also a whole set of other tools such as the Development Center to design SQL PL (stored procedures), The Activity Monitor for runtime analysis, Design Analysis to check the database design, Visual Explain which displays Access Plan Graphs, Health Center can be used on the databases but this is only available in ESE environments. There are also loads of wizards, the one I think is the best is the Automatic Maintenance configuration wizard, reduces the role of DBA to a minimum. > What are storage limits? > > Can you use on a web-site and/or across the internet? Yes. Although I have not used it in this way. DB2 Express-C is based on the same core technology as the DB2 Universal DatabaseT (UDB) Express Edition V8.2.2 product. DB2 Express-C is available on Linux® and Windows® platforms (32- and 64-bit) as a no-charge download from IBM. IBM introduced DB2 Express-C, a version of DB2 Universal Database Express Edition (DB2 Express), for the community on January 30, 2006. It offers a solid base to build and deploy all applications, including C/C++, Java, .NET, PHP, and more. > What's good It's not SQL Server 2005 (g). It's rock solid. > not so good? There is little downside they only limits on the free version is it can only use 4GB or memory and up to 2 CPUs. It does not include the High Availability and Disaster Recovery functions, but this does give it a lighter footprint than DB2 Express. SignatureSlainte Craig Alexander Morrison Crawbridge Data (Scotland) Limited >> I use Access and DB2 -- ADPs are obsolete. Thanks! Something new to try, then. > What are storage limits? I am not sure but I think it is whatever you want. I know you can arrange to spread your database over different disk drives. I am still getting to know this version of DB2 so my advice to anyone reading this is check the details at the ibm website, http://www-306.ibm.com/software/data/db2/udb/db2express/ When I first saw the DB2 Express-C documents it read along the lines that unlike SQL Server 2005 Express and Oracle Express it had no abitrary limits other than the 4GB memory and 2 CPUs. As to ADPs well I used them against SQL Server 2000 but lost all hope when I saw what they were doing to SQL Server 2005 so looked around for an alternative and DB2 was it, it was just my good luck that the Express-C was released around then. SignatureSlainte Craig Alexander Morrison Crawbridge Data (Scotland) Limited >> I use Access and DB2 -- ADPs are obsolete. > > Could you expand? I use Access and DB2 -- ADPs are obsolete. However I am glad you like them, I used to but as I could not use them to access DB2 what was the point of them, they tied you to SQL Server, which is not the brightest kid in the class even weaker in some respects than Jet 4. BTW DB2 Express-C is free and is very very good, I remember you were abusive to me about that a few months back and I forgot to mention it did not cost thousands of dollars as you alleged. Free to develop, deploy and distribute. Your abusive posts are no doubt the result of frustration about the demise of ADPs, but you do not see me and others being abusive about people who use SQL Server 2005. Craig Alexander Morrison Crawbridge Data (Scotland) Limited > you guys are a bunch of ... > [quoted text clipped - 3 lines] > you guys need to get a life; grow some balls and use a real database > engine Craig; you're a f.cking idiot and a liar. SQL Server runs the worlds' largest databases. When was the last time that DB2 won a price/performance on tpc.org?? SQL Server rocks dude; you're so full of crap. I do not have abusive posts. I just deal with a bunch of close-minded people that won't give ADP a chance. ADP are the best platform anywhere. Running them against SQL 2005 is the most pleasant experience I've ever had. You don't have enough balls to talk sh.t about SQL 2005 because you're fighting for the wrong team. IBM is a bunch of dinosaurs; I mean seriously here-- Oracle has at least a little bit of punch. And for the record; you CAN use DB2 inside of ADP. It's DEAD simple kid. MUCH MUCH MUCH better platform than MDB. -Aaron > I use Access and DB2 -- ADPs are obsolete. > [quoted text clipped - 20 lines] > > you guys need to get a life; grow some balls and use a real database > > engine > just because I speak the truth-- that MDB is obsolete-- it doesn't mean > that I'm a troll. What ever truth there may be in your assertions is lost in the vitriol of your writing style. That is what makes you a troll. -- Bri Ha, ha, ha the old ones are always the best. You're not a Troll, snigger! Seriously though, you shouldn't deny your nature, that way lies deep unhappiness. Next you'll be railing against lifes little problems and blowing them up out of all proportion, even worse you might start thinking strange things like, your truth is the only truth and that anyone who doesn't agrre with you is stupid and things. Anyway you take care until we talk again. SignatureTerry Kreft > you guys are a bunch of a.sholes and dipshits > [quoted text clipped - 116 lines] > > > > > > > > > > I didn't see any mention of SQL in the Referential Integrity > > > > > > > > > > entry under that topic. Terry; it is so hilarious that you guys sit around and defend an obsolete database format.. that is TEN YEARS out of date. Grow some balls and lose the training wheels; kids. Access Data Projects _SLAUGHTERS_ MDB format. It slaughters ACCDB format also. -Aaron > I love the way that you have no compunction about showing how ignorant you > are of both transact SQL and Access SQL and the way that you're not afraid [quoted text clipped - 42 lines] > > > > > I didn't see any mention of SQL in the Referential Integrity > > > > > entry under that topic. Oh, are you back? I thought that you had possibly gone off to learn a new tune, or at least add anoher string to your bow. It's obvious that you haven't been for an eye-test, as every problem is still looking like a nail to you. SignatureTerry Kreft > Terry; > [quoted text clipped - 54 lines] > > > > > > I didn't see any mention of SQL in the Referential Integrity > > > > > > entry under that topic. > Grow some balls I shounds like you lost yoursh in a terrible shmelting accident. > > http://en.wikipedia.org/wiki/SQL > > > > See the 'Concepts' section. > > I didn't see any mention of SQL in the Referential Integrity > entry under that topic. The entry for 'SQL' references the entry for 'Referential Integrity' but not the other way around. What's your point? Jamie. -- >>>http://en.wikipedia.org/wiki/SQL >>> [quoted text clipped - 5 lines] > The entry for 'SQL' references the entry for 'Referential Integrity' > but not the other way around. What's your point? Referential integrity is not an SQL concept. It's a relational database design concept. > Referential integrity is not an SQL concept. > It's a relational database design concept. You got it in the end! To relate back your original enquiry, 'What does SQL have to do with referential integrity?' If you want declarative referential integrity, SQL's got it (Jet SQL's got it so I guess you can say Access's got it too). HTH, Jamie. -- SQL RI works across the 2gb limit and the 2gb limit in Access?? I can sneeze and make 2gb of data out of it. I've seen spreadsheets that hit the 2gb limit. i mean for christ sakes. you can't rely on RI in an MDB.. not for only 2 tables with 2 records each and only 2 users. in practical implementations; it is impossible to do. -Aaron > > Referential integrity is not an SQL concept. > > It's a relational database design concept. [quoted text clipped - 10 lines] > > -- > SQL RI works across the 2gb limit and the 2gb limit in Access?? > [quoted text clipped - 8 lines] > > -Aaron Can't you just get lost with your horsecrap ?? BTW: Horsecrap *is* your language isn't it ?? Arno R > SQL [Server] RI works across the 2gb limit File size (and other limits) aside, DRI is better implemented in Jet 4.0 than in SQL Server 2005 e.g. try this simple example: CREATE TABLE OrgChart ( employee_number INTEGER NOT NULL PRIMARY KEY, boss INTEGER REFERENCES OrgChart (employee_number) ON DELETE CASCADE ON UPDATE CASCADE ); SQL Server 2005 worries that this 'may cause cycles or multiple cascade paths' when even a moron (e.g. Jet 4.0) can figure it out. Do I choose Jet over SQL Server because its implementation is better in some areas e.g. DRI, CHECK constraints, etc? Of course not. The fact remains that I sometimes design DRI/CHECKs in Jet 4.0 before having to 'dumb down' for SQL Server 2005 e.g. a trigger with the comment 'Replace this trigger with proper functionality when the SQL Server team stop obsessing over CLR and get round finishing the implementation of the SQL-92 standard, or at least get as smarts as Access was FIVE years ago.' Jamie. -- >>>There are circumstances where you need to disable DRI, CHECK >>>constraints, etc temporarily while you complete a set of [quoted text clipped - 11 lines] > > 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|