We help IT Professionals succeed at work.

need case-sensitive primary keys

tmcnab59
tmcnab59 asked
on
2,318 Views
Last Modified: 2013-11-29
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.......
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
<Is there anyway to force the primary keys to be case sensitive?>
afaik, this is not possible
Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Top Expert 2006
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2016

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ยข
Top Expert 2006

Commented:
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.
CERTIFIED EXPERT
Top Expert 2016

Commented:
j,
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.
Top Expert 2006

Commented:
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 job..as 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)
J
Leigh PurvisDatabase Developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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?

mx
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
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.

Author

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.
Tmcnab

Author

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 Developer
CERTIFIED EXPERT

Commented:
>> "they work directly with the tables when creating queries/reports"

<Faints>

But glad you're feeling sorted though. :-)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.