The database that I’m developing in Microsoft Access and SQL Server is a student tracking system, and not surprisingly, the main table is called Students. After much consideration, I decided not to use the student ID as the primary key of the table, instead I’m using an integer identity(1,1). The main reason is that the number might change. Normally, when a student is assigned an ID by the State Department of Education, that number will never change. But if they’re enrolled in a private school, of which there are two or three types, they will have a number assigned by that organization, which could theoretically collide with a number assigned by the State. My solution was to add a char(1) field that discriminates between the different types of organizations that can assign an ID, and I have an index on that combination of ID# and Org.
The main reason why I didn’t want this number to change was that I didn’t want to cascade changes to two child tables, plus, I couldn’t cluster on it because there’s no telling what order they’ll be entered in, which would cause endless page splits and I/O hits.
Access has a .Dirty event to indicate changed data, but it only operates at the record level, not at the field level. (Or row and column level, if you want to be pedantic about it) But Access does save the previous value of a changed field in .OldValue, so we can recover from an accidental field change without cancelling all edits on a record!
I don’t want the ID# to be accidentally changed, so I developed the following code tied to the LostFocus event that pops up a message box warning the user and forcing them to click Yes to change the number.
Private Sub StudentID_LostFocus() 'can't directly check for Dirty, must compare to OldValue With StudentID If .Value <> .OldValue Then retval = MsgBox("You have changed the "_ & "Student ID. Did you mean to do this? " _ & "Selecting No will reset it to the " _ & "original value.", vbYesNo) If retval = vbNo Then .Value = .OldValue End If End With End Sub
Yes, I was supposed to write about how I do DBCCs without doing maintenance plans, I’ve had to push that a bit.