need case-sensitive primary keys

Hello -

We are running Access 2002 app, but our mdb files are still Access 2000.
I have imported data from our business system where the primary key is case sensitive - lowercase is unique from uppercase.

When I try to recreate this primary key in the database, it tells me I have duplication.

Is there anyway to force the primary keys to be case sensitive?

Thanks so much.......
Who is Participating?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
The only thing I can see that comes close to this only applies in code - Option Compare Binary or Text (the default is Database). Interesting question ... I've never had this come up:

Info - FYI:

Option Compare Statement

Used at module level to declare the default comparison method to use when string data is compared.


Option Compare {Binary | Text | Database}


If used, the Option Compare statement must appear in a module before any procedures.

The Option Compare statement specifies the string comparison method (Binary, Text, or Database) for a module. If a module doesn't include an Option Compare statement, the default text comparison method is Binary.

Option Compare Binary results in string comparisons based on a sort order derived from the internal binary representations of the characters. In Microsoft Windows, sort order is determined by the code page. A typical binary sort order is shown in the following example:

A < B < E < Z < a < b < e < z < À < Ê < Ø < à < ê < ø

Option Compare Text results in string comparisons based on a case-insensitive text sort order determined by your system's locale. When the same characters are sorted using Option Compare Text, the following text sort order is produced:

(A=a) < ( À=à) < (B=b) < (E=e) < (Ê=ê) < (Z=z) < (Ø=ø)

Option Compare Database can only be used within Microsoft Access. This results in string comparisons based on the sort order determined by the locale ID of the database where the string comparisons occur.

Rey Obrero (Capricorn1)Commented:
<Is there anyway to force the primary keys to be case sensitive?>
afaik, this is not possible
Employing the methods from mx and cap...I guess you've already figured out you're going to have to store these primary keys in a text field that allows duplicates, and index in a normal autonumber combined with that field.

The problem you'll have I'm guessing is one of joining that field to data that expects the Case sensitivity. That's where you're going to have to get creative and either create a binary equivalent of the field to join on, or some similar method.

An example might be to create a function that reverse engineers this logic

Chr Function Example
This example uses the Chr function to return the character associated with the specified character code.

Dim MyChar
MyChar = Chr(65)    ' Returns A.
MyChar = Chr(97)    ' Returns a.
MyChar = Chr(62)    ' Returns >.
MyChar = Chr(37)    ' Returns %.

Anyway...good luck with this. Case in Access is always a pain.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Rey Obrero (Capricorn1)Commented:
i don't see the relevance of making things complicated when you can achieve the same goal of not allowing duplicates or having a field with unique values in a simple way.

my 2¢
that's what I said Cap

>>you're going to have to store these primary keys in a text field that allows duplicates, and index in a normal autonumber combined with that field.
Rey Obrero (Capricorn1)Commented:
i am not referring about your comment.
btw, did you get any software from M$, using your mvp reward?
i got the office ultimate 2007.
I haven't had time to shop!! And I gor a copy of OU2007 through work...but haven't even had time to play with it much. I've just been buried at my new you can probably tell by my lack of presence here on EE. I'm still waiting until the desktop PC demo on that link you sent me becomes affordable...that's the ultimate!! I did get the new XBox with the latest Halo3!! So I'll be busy doing THAT for a while now :o)
Leigh PurvisDatabase DeveloperCommented:
>> case sensitive
Yes - for it to occur natively though you'd need a Binary field data type.
You can't create them through the Access UI - but Jet 4 (Access 2000 onwards) supports them, created through SQL DDL statements.
Once created they don't perform interpreted text comparisons like standard text (char/varchar) fields but store and compare *exactly* what is entered. (For example trailing spaces too - so be careful of that ;-)

I'm not convinced I'd actually use it as the Primary Key still, though to do otherwise could be considered a violation of normalisation.
If you're importing this data - but are supplementing that with other tables - then you'd need to continue creating binary fields in your other tables for PK/FK joins and such... whereas a surrogate autonumber PK in your imported data would allow you to continue as before, while maintaining your Case Sensitive imported data.

Index usage in queries behaves *slightly* differently on Binary fields (I'm thinking about comparisons using "Like" here) but that's not likely to impact on your usage I'd say.  You'll be performing exact matches for the most part yes?


(Which would give you 50 characters - hence the max size for a Binary field is 510).
Chances are you'll want to actually change that slightly - as if you make comparisons then the above Binary roughly equates to a Char data type (which Jet also supports - again just not through the UI, many likely don't realize that either) - and like Char the data is fixed length and padded with zero ANSI characters.
So you would perhaps go with

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I have seen Binary data types a couple times ... and forgot how to create them.  Cool.

Leigh ... does that connect at all with what I posted?

Leigh PurvisDatabase DeveloperCommented:
Well - that particular Help file extract relates to comparisons within the context of the VBA module who's Option Compare statement is set and so would affect, say, recordset field string value comparisons performed in that module.
AFAIK not many folks resort to changing Option Compare but just reach for StrComp as and when they need it in a given procedure.  That would certainly be my preference - I'll take the tiny performance hit of StrComp on the chin. :-)
StrComp (wrapped or otherwise) or any VBA function on the tested fields is an option for use in queries too, but you're adding overhead all the time then - and it's bye bye to any index on the field.
The extreme of that being an ASCII breakdown function as Jeff has mentioned.
tmcnab59Author Commented:
Hi all -

thanks for the wonderful feedback.
I'm going to give your suggestions a try in the next week.
thanks for also pointing out that I will need carry this through to our other tables.
tmcnab59Author Commented:
Hi All -
Thanks for all your feedback. It allowed me to analyze my problem multiple ways.
In the end, I opted to not use the binary nor the autonumber approach.

I have created an additional field in each of the tables.
Since I am using a macro to import the multiple tables into the database, this field is updated with a value (upper/lower indicator) based on the case-sensitive field (I used the ASC function on the field) thru' this macro as each table in imported.
I've also written 2 VBA scripts - one to create primary keys on all these tables, incorporating the new 'case indicator' field as part of the fields to create the primary key; one to remove these keys prior to importing the data - to avoid any import errors.
I know this may appear convoluted, but it is a solution that my users can understand, since they work directly with the tables when creating queries/reports.

Thanks again for all your help.......TMcNab
Leigh PurvisDatabase DeveloperCommented:
>> "they work directly with the tables when creating queries/reports"


But glad you're feeling sorted though. :-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.