This post is a break from the recent code snippet entries to relate something I have too much experience to not know, but didn't know anyway.
I have been developing MS Access databases for nearly two decades, now, but I have rarely had occasion to have a Primary Key that consisted of multiple fields, one of which might consistently be null or blank. My PKs have typically been very specific, so the blank field in a PK is uncommon, to say the least. Not unheard-of, but definitely uncommon.
First of all, SQL Server does not like Nulls in a Primary Key. MS Access doesn't care, but when build tables in SQL Server that you will link to MS Access, and one of them needs to be able to hold a blank, you'll have to make it an empty string, instead of a Null.
And now to the point of this blog: DO NOT RELY ON DEFAULT VALUES IN A PRIMARY KEY!
I discovered that you cannot rely on default values in SQL Server to supply a value for the Primary Key, especially if you plan to link the table via ODBC to MS Access.
Weirdnesses begin to happen at that point. Not good ones. Bad. Very bad.
Briefly - I had a table with a 5-field PK, and one of those fields is consistently blank. I had a default value in the SQL Server definition of an empty string for the field in question and everything seemed peachy until the users complained that when they entered in a new row, the data changed - visibly changed to something completely different - when they committed the new row. It fixed itself with a refresh, but it was seriously causing confusion. If they edited the row to "fix" it - change it back to what they'd just entered - they discovered that it was actually changing some other row in the database! Worse, if they deleted it because it was suddenly wrong, it would delete the other row!!
Holy sheep dip!!! This was NOT a good weirdness! It was actually displaying data from some other seemingly random row in the database.
After a significant amount of research, I discovered that entering in a value - either manually or programmatically - resolved the issue. Allowing the default value to derive from the table definition is not good.
In SQL Server, when entering data manually in edit mode WITHOUT entering a value in EVERY key field (relying on default values), you'll get an exclamation point in the record selector with a control tip that states the following:
This row was successfully committed to the database. However, a problem occurred when attempting to retrieve the data back after the commit. Because of this, the displayed data in this row is read-only. To fix this problem, please re-run the query.
This does not seem like a significant issue until you try adding a new record in MS Access. THAT is when the weirdness begins and data starts to go wiggy.
It is imperative that you physically (or programmatically) supply a value for every element of the Primary Key.