Here's a question; how long would it take to write code that works well and would achieve the same level of protection as a unique compound index?
Chris.
Thanks Chris but I am done here. I find I gain nothing from exchanges of the sort I have had on this thread. You have talked yourself into believing there is some protection to be gained from deploying an index where a small lookup function is what is needed. No proof, no analysis, no insight - just a blanket verdict which is supposed to convince me it is true. Well it doesn't and since I am not interested in argufying further on this silly topic, you will just have to excuse me.
I strongly believe in the security of implimenting the data model at the root of the system in the tables' indexes and relationships where it universally and absolutely reliably protects against any coding oversights and errors. It is considered by many as good programming practice.
Others on this thread have already provided analysis and insight far beyond what jiri has offered to back their ill-considered assertion.
I’ll copy and paste your code from the sample in post #17.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim testdate As Long, ss As String
testdate = Nz(Lookup("WO_PointsPK", "tblWO_Points", "lastname = '" & Me!LastName & _
"' AND firstname = '" & Me!FirstName & _
"' AND dateWO = #" & Format(Me!dateWO, "mm/dd/yyyy") & "#"), 0)
If testdate <> 0 Then
' the date is a duplicate for the given lastname and firstname
MsgBox "Can't Do That !"
Cancel = True
Me!dateWO.SetFocus
End If
End Sub
1.
The DLookup is looking for a match in the Table. The DLookup might return Null which means no match. The Nz function converts that Null to zero(0). Zero now inplies no match when, in fact, the primary key could have a value of zero. (Please ask more experienced people than me about the posibility of the primary key having a value of zero.)
In any case, it’s a risk not worth taking. Variable Testdate should have been of type Variant in order to accept the possible Null return from the DLookup function. Unless there is a valid reason for a variable of a different data type than the Function which feeds it then the variable data type and the Function return data type should be the same.
So:-
Scrap the Nz() conversion and:-
Dim testdate As Long
should have been:-
Dim testdate As Variant
and:-
If testdate <> 0 Then
would become:-
If Not IsNull(testdate) Then
And that would allow for the valid primary key to be zero.
2.
Both Me!LastName and Me!FirstName are enclosed in single quotes. That could work for many years or not at all. A name like O’Brien or, as was in that sample database O'Reily, would blow the Where clause out of the water. When it comes to strings, with unknown content, double quotes are more reliable. As an employee name it could work for years until, one day, an employee by the name of O'Reily is entered.
3.
AND dateWO = #" & Format(Me!dateWO, "mm/dd/yyyy") & "#"
That is a poor attempt of regional settings correction. Sure, it corrects for Day/Month -> Month/Day insertion but has no affect on the Date Separator in regional settings. USA / OK, Britain / Ok, Australia / OK, French (Switzerland) . blown out of the water.
At least three errors for which you have not accounted. Three errors in code which is supposed to match the reliability of a unique compound index. You are trying to replace the brute force of a primary unique compound index with your secondary soft coding errors. Good luck with that.
But it still requires a primary unique compound index to enforce the primary objective.
I’ll copy and paste your code from the sample in post #17.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim testdate As Long, ss As String
testdate = Nz(Lookup("WO_PointsPK", "tblWO_Points", "lastname = '" & Me!LastName & _
"' AND firstname = '" & Me!FirstName & _
"' AND dateWO = #" & Format(Me!dateWO, "mm/dd/yyyy") & "#"), 0)
If testdate <> 0 Then
' the date is a duplicate for the given lastname and firstname
MsgBox "Can't Do That !"
Cancel = True
Me!dateWO.SetFocus
End If
End Sub
1.
The DLookup is looking for a match in the Table. The DLookup might return Null which means no match. The Nz function converts that Null to zero(0). Zero now inplies no match when, in fact, the primary key could have a value of zero. (Please ask more experienced people than me about the posibility of the primary key having a value of zero.)
In any case, it’s a risk not worth taking. Variable Testdate should have been of type Variant in order to accept the possible Null return from the DLookup function. Unless there is a valid reason for a variable of a different data type than the Function which feeds it then the variable data type and the Function return data type should be the same.
So:-
Scrap the Nz() conversion and:-
Dim testdate As Long
should have been:-
Dim testdate As Variant
and:-
If testdate <> 0 Then
would become:-
If Not IsNull(testdate) Then
And that would allow for the valid primary key to be zero.
2.
Both Me!LastName and Me!FirstName are enclosed in single quotes. That could work for many years or not at all. A name like O’Brien or, as was in that sample database O'Reily, would blow the Where clause out of the water. When it comes to strings, with unknown content, double quotes are more reliable. As an employee name it could work for years until, one day, an employee by the name of O'Reily is entered.
3.
AND dateWO = #" & Format(Me!dateWO, "mm/dd/yyyy") & "#"
That is a poor attempt of regional settings correction. Sure, it corrects for Day/Month -> Month/Day insertion but has no affect on the Date Separator in regional settings. USA / OK, Britain / Ok, Australia / OK, French (Switzerland) . blown out of the water.
At least three errors for which you have not accounted. Three errors in code which is supposed to match the reliability of a unique compound index. You are trying to replace the brute force of a primary unique compound index with your secondary soft coding errors. Good luck with that.
But it still requires a primary unique compound index to enforce the primary objective.
1. This is the second stupidest objection I have seen on this board. The statement was offered as an example - a proof of concept.
2. Has no effect on the viability of the concept. Can easily be protected against.
3. This is the absolutely stupidest objection I have seen on this board. Has no merit whatsoever. If you thought about it - and that assumes carelessly you know the difference between 'thinking' and suffering buzz between the ears - you would realize that if buzz tells you that Windows / Access cannot handle the French Swiss date it is NOT a problem that originates with the module. The point of course is that Windows/Access DOES handle the French Swiss date notation. What you say is idiotic beyond belief.
First and foremost please be careful how you say things. If you disagree with something you read then by all means say so and try to give some evidence for your opinion.
Here is a general comment about your code which I posted in post #25. I really don’t care who wrote it, it is still incorrect.
1.
I have already given my thoughts on that matter and given an alternative. I do not see that as simply “proof of concept” code.
The code is looking for a non Null return. In order to determine if the return is Null or not the result must be allowed to be Null, if that is what it is. The Nz function destroys the possibility of a Null return. In effect then, the Nz function destroys the very thing which defines if the record exists and that is the possibility of a Null return from interrogating the primary key.
So I don’t think we can put this down to a simple “proof of concept” coding error.
It is oversight 1.
2.
I can not simply accept the notion that it was just “can easily be protected against” code and, as such, we should accept the error I have already pointed out. If I had to accept the code as easily protected against then I would have to ask why it was not protected against it if it is so easy.
I think we can take this idea a little further. Would the writer deliberately put errors in their code? I think not, the code was in a sample database. The code in that sample database, as in post #17, was tested to some degree by the person who wrote it.
The proof of that testing is that there are two new entries in the Table, Peter Frampton and Don Kucharski. Those two entries were entered by the person doing the testing. The other entry in the Table, Fred O'Reily, was already in the Table. So the person who did the testing ignored the entry already in the Table and created two new entries. That was a fatal error in the testing because O'Reily was deliberately put in the Table because it has an apostrophe in the name. It is that apostrophe which causes the Where clause to fail.
So I really don’t think we can put the coding error down to simply being “can easily be protected against” if the supplied test case is ignored. Let’s be kind to the tester and say that they overlooked the only entry in the Table when they started testing.
For whatever reason it occurred, it did occur; so let’s call this oversight 2.
3.
Blah, blah, blah & >>that Windows / Access cannot handle the French Swiss date it is NOT a problem that originates with the module.<<
Well, that is exactly where the problem does originate. It originates in the module as written by the writer and the writer not knowing how to test their own code. I have written about this so many times before, and in this thread gave the test method, that I really don’t want to keep doing it.
If people don’t know the difference between "mm/dd/yyyy" and "mm\/dd\/yyyy" as a format string then they need an education in regional settings immunity coding.
I’ll ignore the rest of your statement, for the moment, and just call this oversight 3.
----------
Three oversights which, while any one of them exists, indicates to me that the coder should not be trying to use their code to replace the brute force of a unique compound index.
First and foremost please be careful how you say things. If you disagree with something you read then by all means say so and try to give some evidence for your opinion.
Here is a general comment about your code which I posted in post #25. I really don’t care who wrote it, it is still incorrect.
1.
I have already given my thoughts on that matter and given an alternative. I do not see that as simply “proof of concept” code.
The code is looking for a non Null return. In order to determine if the return is Null or not the result must be allowed to be Null, if that is what it is. The Nz function destroys the possibility of a Null return. In effect then, the Nz function destroys the very thing which defines if the record exists and that is the possibility of a Null return from interrogating the primary key.
So I don’t think we can put this down to a simple “proof of concept” coding error.
It is oversight 1.
2.
I can not simply accept the notion that it was just “can easily be protected against” code and, as such, we should accept the error I have already pointed out. If I had to accept the code as easily protected against then I would have to ask why it was not protected against it if it is so easy.
I think we can take this idea a little further. Would the writer deliberately put errors in their code? I think not, the code was in a sample database. The code in that sample database, as in post #17, was tested to some degree by the person who wrote it.
The proof of that testing is that there are two new entries in the Table, Peter Frampton and Don Kucharski. Those two entries were entered by the person doing the testing. The other entry in the Table, Fred O'Reily, was already in the Table. So the person who did the testing ignored the entry already in the Table and created two new entries. That was a fatal error in the testing because O'Reily was deliberately put in the Table because it has an apostrophe in the name. It is that apostrophe which causes the Where clause to fail.
So I really don’t think we can put the coding error down to simply being “can easily be protected against” if the supplied test case is ignored. Let’s be kind to the tester and say that they overlooked the only entry in the Table when they started testing.
For whatever reason it occurred, it did occur; so let’s call this oversight 2.
3.
Blah, blah, blah & >>that Windows / Access cannot handle the French Swiss date it is NOT a problem that originates with the module.<<
Well, that is exactly where the problem does originate. It originates in the module as written by the writer and the writer not knowing how to test their own code. I have written about this so many times before, and in this thread gave the test method, that I really don’t want to keep doing it.
If people don’t know the difference between "mm/dd/yyyy" and "mm\/dd\/yyyy" as a format string then they need an education in regional settings immunity coding.
I’ll ignore the rest of your statement, for the moment, and just call this oversight 3.
----------
Three oversights which, while any one of them exists, indicates to me that the coder should not be trying to use their code to replace the brute force of a unique compound index.
Actually ChrisO demonstrated a serious flaw in your code regarding records that include apostophes.
This is exactly the kind of "oversight and error" I was talking about when I posted supporting the implimentation of the logic in the tables and relationships. I will say it again. Using an index where duplicates are to be avoided is good programming practice.
Your continued denial in the face of very sound advice and clear evidence demonstrates a profound unwillingness to learn that will inevitably limit your competency as a programmer.
On the basis of what I have seen in this thread I certainly would not employ you in any field.
I cannot for the life of me understand why you have taken this stance. Your language is not what is normally accepted here at AWF. (Please take the hint)
The bottom line is that your code does not work. I even said that it would fail before I tested it. See my post #15.
While your ability to write code is above average I would think that the code as posted by you has not been tested and most likely will fail in some situations. But then this is speculation on my part as I have not tested it either.
The idea of using indexes to do data validation (which is what the OP asks for) would seem counterintuitive to most programmers who have had their hands in real, commercially viable systems. If you made a policy of using indexes to set up business rules for your app you would be dead in water in no time.
Solo - you are new here so I am not going to respond to your personal criticism of me except to say that your arrogance will cause you nothing but trouble wherever you find yourself posting. If you insist on telling everyone how right you are – then you better be right (which you aren't) or expect a lot of challengers.
Solo - you are new here so I am not going to respond to your personal criticism of me except to say that your arrogance will cause you nothing but trouble wherever you find yourself posting. If you insist on telling everyone how right you are – then you better be right (which you aren't) or expect a lot of challengers.
Pat,
I made no personal criticism of you. I took a somewhat sharp tone with the challengers, including you, because you are making assertions which are not supported. It is not I who is arrogant. I have taken the position that keeping a compound indexes to perform validation queries is a costly proposition, which would not do as a policy. I have been challenged to provide justification for my stance and I quoted from Groh's book, which cautions not to use indexes unnecessarily as there is a performance cost associated with maintaining a large number of them. In larger databases too many indexes may cause serious issues with locking, and huge problems on real-time systems with high volume of transactions. This is well known and acknowledged by nearly everyone in the database business. There seems to be a large consensus - outside of the commenters on this thread - on the need to make judicious decisions in deploying indexes. You can google 'disadvantages of database indexing' and you will get slew of hits from every relational database there is. And they seem to agree on major issues:
* slowing down inserts, updates and deletes.
* increasing maintenance requirements (depending on the internal storage architecture)
* making some bulk database operations actually slower because both the index and data blocks need to be visited
Though considered trivial by some, disk space is also a consideration, especially with Access. It is generally acknowledged fact that most dbs will develop serious performance issues well below the the 2 Gb limit. Indexes can double and triple the size of each record (each relationship in the relationship table is kept as a separate index !!). They definitely add to the complexity of the db and increase the chance of data corruption during hardware and network crashes, power supply anomalies, etc.
So, to my mind, it is worthwhile to look at alternatives. You may feel offended because I contradict you and talk to you the way you talk to me - that is: 'down', but I remained unshaken in my conviction. As a matter of fact, I take the melodrama here as an admission that you and friends don't really have an argument. Incidentally, I would love to see some intelligent, thought-throuh chapter from a book, or hitech mag article advocating indexes as the preferred tool for data validation. I'll give two thank-yous, if you can find me one.
When are you going to bless us with code that actually works.
The idea of using indexes to do data validation (which is what the OP asks for) would seem counterintuitive to most programmers who have had their hands in real, commercially viable systems. If you made a policy of using indexes to set up business rules for your app you would be dead in water in no time.
That put me out of business. I do it in every table I create. It is called a Primary Key. The main intention of a Primary Key is to do data validation to make sure there are no duplicates.
You are in bad faith, Rain. You know that Chris' objections are trivial and the code easily fixed for the matters he brought up. I am sure you know how to do that . But you just want to hassle, right ?
SOLO712 said:
The idea of using indexes to do data validation (which is what the OP asks for) would seem counterintuitive to most programmers who have had their hands in real, commercially viable systems. If you made a policy of using indexes to set up business rules for your app you would be dead in water in no time.
The idea of using indexes to do data validation (which is what the OP asks for) would seem counterintuitive to most programmers who have had their hands in real, commercially viable systems. If you made a policy of using indexes to set up business rules for your app you would be dead in water in no time.
That put me out of business. I do it in every table I create. It is called a Primary Key. The main intention of a Primary Key is to do data validation to make sure there are no duplicates.
No, the main intention of Primary Key is to ensure that all records in a table are identified uniquely. The nominal value in the primary key is often irrelevant beoynd this function. Data validation OTOH ensures that data in a particular field, or a combination of fields, conform(s) to generally accepted conventions and/or specific application rules.
That is a pretty pointed critiicism and according to you, apps I developed should have died years ago. I do not have your hubris. I think the database engine can implement business rules better and more efficiently than I can and so I use it whenever possible. Your code sample is an excellent example of what I am talking about. I have also posted non-working code that I wrote in a hurry but I would never offer my untested code as an alternative to RI.
Your argument that the OP asked for code to implement his rules and so that is what we should give him leads me to believe you shouldn't be advising newcomers. In this forum we try to solve problems rather than riigidly answer questions. People with little experience rarely know what options they have and so we teach them to fish rather than simply giving them a meal.
Your quote about unnecessary indexes is also misguided since an index that enforces RI isn't unnecessary just because you could write code to do it yourself. If I wanted to, I could write code to replace Jet. I have a pretty good background in low-level coding so technically I could do it. It would probably only take a couple of years working alone. But why would I want to do that? To prove I'm smarter? Take a look at my original post which offers the reason I think the index is superior to code and address that if you want to continue to argue.
One thing not mentioned in this thread is the inclusion of FirstName and LastName fields in the table. As the OP said that names could occur multiple times with different dates it would suggest that they would be better to normalise the names into a related table and record an ID in the junction table.
That would not only reduce the storage requirement but significantly reduce the cost of a compound index.
We might also consider the performance of the DLookup validation. I do know for sure that the performance of a DLookup is vastly superior on an indexed field.
I don't know if the same would be true for a compound index but the developers who created the engines put a lot of effort into optimisation and I would not be at all surprised if the engine did indeed use the compound index when appropriate. If so then the performance of the validation would be enhanced by the compund index.
I do agree that indexes should be used judiciously. However the ultimate protection to data integrity offered by the index is exactly that kind of judicious use. As has already been demonstrated, the potential for oversight is real.
One thing not mentioned in this thread is the inclusion of FirstName and LastName fields in the table. As the OP said that names could occur multiple times with different dates it would suggest that they would be better to normalise the names into a related table and record an ID in the junction table
January-12-2013, in post #3 in this thread, we are advised by you to use code to replace a unique compound index.
What a difference two days make.
----------
Pat Hartman mentioned this in Post #10 and you should not ignore what she says.
(By all means question it but don’t ignore it.)
----------
Test this without a unique compound index. Run it more than once and check the Table entries:-
Code:
Option Explicit
Option Compare Text
Public Const dbFailOnError As Long = 128
Public Const conDateFormat As String = "yyyy\-mm\-dd"
Private Sub TestTableViolation()
Dim strLastName As String
Dim strFirstName As String
Dim strSQL As String
Dim datDate As Date
On Error GoTo ErrorHandler
Const conDuplicateViolation As Long = 3022
strLastName = "O'Brien"
strFirstName = "Fred's"
datDate = VBA.Date
strSQL = " INSERT INTO tblWO_Points" & _
" (" & _
" LastName," & _
" FirstName," & _
" DateWO" & _
" )" & _
" VALUES" & _
" (" & _
VBA.Chr(34) & strLastName & VBA.Chr(34) & ", " & _
VBA.Chr(34) & strFirstName & VBA.Chr(34) & ", " & _
"#" & VBA.Format(datDate, conDateFormat) & "#" & _
" )"
CurrentDb.Execute strSQL, dbFailOnError
ExitProcedure:
Exit Sub
ErrorHandler:
Select Case VBA.Err.Number
Case conDuplicateViolation
VBA.MsgBox "The combination of Last Name, First Name and Date MUST be Unique." & _
VBA.vbNewLine & _
VBA.vbNewLine & _
"This entry will NOT be saved.", _
VBA.vbCritical, _
"Data Violation in Table"
Case Else
VBA.MsgBox "An unexpected error was raised in procedure TestTableViolation." & _
VBA.vbNewLine & _
VBA.vbNewLine & _
"Error Number: " & VBA.Err.Number & VBA.vbNewLine & _
"Error Description: " & VBA.Err.Description & _
VBA.vbNewLine & _
VBA.vbNewLine & _
"This entry will NOT be saved.", _
VBA.vbCritical, _
"Unexpected Error in Procedure"
End Select
Resume ExitProcedure
End Sub
Test is again with a unique compound index. Run it again more than once.
Jiri.
When you get to the point of writing something even like the above you will start to understand that what you currently know is nothing more than sheer arrogance.
Please explain each and every line of the above code to us.
If you can not explain each and every line of the above code then you should not assume you know how to do it.
