Update/Append Query key violation

[kbrown]

I am importing data from excel into a temporary table (tblImportClientInfo). Then I am running an update and append query with tblClientInfo and tblImportClientInfo linked on ClientID:

UPDATE  tblClientInfo RIGHT JOIN tblImportClientInfo ON tblClientInfo.ClientID = tblImportClientInfo.ClientID SET tblClientInfo.ClientID = tblImportClientInfo.ClientID, tblClientInfo.EnrolledLastName = tblImportClientInfo.EnrolledLastName, tblClientInfo.EnrolledFirstName = tblImportClientInfo.EnrolledFirstName, tblClientInfo.DateOfBirth = tblImportClientInfo.DateOfBirth, tblClientInfo.LangID = tblImportClientInfo.LangID, tblClientInfo.SpecialNeeds = tblImportClientInfo.SpecialNeeds;

I get key violations for all records that already have existing clientid's in tblClientInfo, even if there is a spelling change in the name, or change in DateOfBirth. If I say yes, I want to continue I will get new information appended, but no updates.

My understanding from the Northwind example is that I should be able to update the name changes, etc using this method. Is it possible that there is something in the import method I am using that is interfering?

Dim strTable As String
Dim strRange As String

strTable = "tblImportClientInfo"
strRange = "a1:f23"

DoCmd.TransferSpreadsheet acImport, , strTable, "c:\mstrenrl.xls", True, strRange

I have tried manually adding information to tblImportClientInfo. I then run the update/append query to add the new info to tblClientInfo. Once the information is added, if I go back and make a change to that entry in tblImportClientInfo and rerun the update/append query, I still get the key violation on the original entries, but not on the new information. The query will update the changes to the new entry in tblClientInfo. It just doesn't work on the data imported from Excel.

All help is greatly appreciated!

 

[kbrown]

ClientID is not an autonumber, but when I tried running the query without it the query did update information. So, my only concern is if we have two clients with the same first and last name and birthdate. Doesn't happen often, but it could. And in that case, the only thing that makes the entries unique is the clientID (which is their social security number or a made up social security number where the first three characters are letters). I am reluctant to remove the clientID from this query for that reason.

Can you think of another reason that it might not be working with the ClientID?
Thank you