Case sensitive data (Access 2000 SR1)

Topic

New Reply

Not a problem I usually need to worry about, but … I’m dealing with some case-sensitive data at present (i.e. fields containing codes identical bar case e.g. C100a and C100A) – is there any way of telling Access that these are different so that e.g. a primary key can be set on this, and joins properly implemented with other similar fields, or is this just not possible in Access?

Can SQL Server handle case sensitive fields – I have the option of transferring the data to that environment?

Thanks

Jeremy

Reply | Quote

Replies

At the data level, Access is case-insensitive, so no, you won’t be able to set a primary key on that field. I can’t speak to SQL Server, which isn’t running on my machine at the moment, but I suspect the same thing holds true there. One workaround would be to create an additional field and populate it with the ASCII value of the field in question. Capital and lower case letters have different ASCII values, so you could use that field as a primary key. You would then need to propagate those ASCII values as foreign keys to related tables and make your joins on those fields or else use a calculated expression in your query to render the ASCII values on the fly and match them between the tables. The latter approach will result in slower queries though.

Reply | Quote

I don’t think there is a way in Access (without resorting to tricks) to do what you ask. SQL Server does have the ability to sort that data logically, as does Access, but I’m not sure it will create a unique index that way either. If your field is relatively short, you could trick either system by storing it as a number rather than a string, but it would be limited to 8 bytes (unless you want to try messing with GUIDs).

Reply | Quote