MS Access ignores trailing space(s) in primary key validation

Question

I have an Access table with a TEXT primary key. If I use an INSERT statement to add a row where the primary key value is

'PART'

and then try to INSERT another row where the primary key is

'PART '

(note the trailing space) then I get

Microsoft Access ... didn't add 1 record(s) to the table due to key violations

If there is no primary key on the field all is fine, and the second row is indeed added with the trailing space in the field value. But if primary key is set on the field then it causes problems with the key violation error.

How can I avoid this issue?

Answer 1

Funky behavior. It is normal that Access removes trailing spaces when entering data into a textbox or datasheet field. But that 'PART' and 'PART ' cannot coexist in a primary key column is weird.

@Gord Thompson: You can first insert 'PART ', that works and the trailing space is saved. But then you cannot insert 'PART'.

If this is the only occurrence of spaces in your data, you could replace the spaces by Chr(160), which also looks like a space, and doesn't violate the PK.

CurrentDb.Execute "INSERT INTO tblTextPK (TextPK) VALUES('part')", dbFailOnError
CurrentDb.Execute "INSERT INTO tblTextPK (TextPK) VALUES('part" & Chr(160) & "')", dbFailOnError

In your import code you would use Replace([Fieldname], " ", Chr(160)).

(Note that to type Chr(160), you use Alt+255 )

Answer 2

You could add an additional field, DatebaseID, and fill this during import with A, B, C, etc. Then create a compound primary key of this field and your current ID.

That would also allow you to get rid of the trailing space(s) which, in general, is a very bad idea.

Answer 3

Very simple : suppose that your field is : BEN SALEM so you have to write your commande as below (example Update query) : "update datatablename set [BEN"+" "+"SALEM]='"+textbox1.text+"' where [ID]='"+textbox2.text+"'";