case-sensitive primary keys

[TMCNAB]

Hello -

We are running Access 2000.
I am importing data from our business system where the primary keys are
case-sensitive - lowercase is unique from uppercase.

When I try to re-create the primary key in Access it is not allowing me to
do so - telling me I have duplication.

Is there any way to make the primary key case sensitive?

Any help is appreciated.

Thanks,
Tammy

 

[Douglas J. Steele]

Hello -

We are running Access 2000.
I am importing data from our business system where the primary keys are
case-sensitive - lowercase is unique from uppercase.

When I try to re-create the primary key in Access it is not allowing me to
do so - telling me I have duplication.

Is there any way to make the primary key case sensitive?

Not really.

What you could possibly do is write a function to convert the text string to
its Ascii representation and store that as the primary key (while still
storing the "proper" text for readability). Alternatively, use something
else as the primary key (such as an Autonumber) and use the StrComp function
in to search that text field.

 

[Pieter Wijnen]

or use an oracle back-end <g>

Pieter

Not really.

What you could possibly do is write a function to convert the text string
to its Ascii representation and store that as the primary key (while still
storing the "proper" text for readability). Alternatively, use something
else as the primary key (such as an Autonumber) and use the StrComp
function in to search that text field.

 

[Douglas J. Steele]

Oh, come on. Didn't Lyle recently show how to use a Byte field for
this purpose?

I wouldn't know. My ISP removed all usenet groups, so I can only see these
Microsoft groups, and Lyle seldom (if ever) posts here.

 

[Pieter Wijnen]

Haven't seen it either, but would be something like

Function Ascii2Hex(ByVal S As String) As String
Dim i As Long, L As Long
Dim R As String

L = VBA.Len(S) ' Do the Calc once
For i = 1 To L
R = R & VBA.Hex(VBA.Mid(S,i,1))
Next
Ascii2Hex = R
End Function

Pieter

 

[Pieter Wijnen]

Ommission (If you want to convert it back later)

Function Ascii2Hex(ByVal S As String) As String
Dim i As Long, L As Long
Dim R As String

L = VBA.Len(S) ' Do the Calc once
For i = 1 To L
R = R & VBA.Format(VBA.Hex(VBA.Mid(S,i,1)),"00")
Next
Ascii2Hex = R
End Function



"Pieter Wijnen"

 

[Douglas J. Steele]

"Pieter Wijnen"

Haven't seen it either, but would be something like

Function Ascii2Hex(ByVal S As String) As String
Dim i As Long, L As Long
Dim R As String

L = VBA.Len(S) ' Do the Calc once
For i = 1 To L
R = R & VBA.Hex(VBA.Mid(S,i,1))
Next
Ascii2Hex = R
End Function

That's exactly what I meant by "write a function to convert the text string
to its Ascii representation and store that as the primary key".

Perhaps David (and/or Lyle) is talking about Byte fields since
VBA.Hex(VBA.Mid(S,i,1)) technically returns a Byte (even though most people
would think of it as a one-character string)

 

[TMCNAB]

Thank-you for these suggestions.
I believe this will do what I was looking for.
When I import the data, I will create an additional field (using this
function) and use that as my primary key.
Luckily, this data is refreshed nightly, so no worries that a user will
modify any values.

Thanks again,
Tammy

 

[Pieter Wijnen]

test first, always

Public Function Ascii2Hex(ByVal S As String) As String
Dim i As Long, L As Long, t As Long
Dim R As String

L = VBA.Len(S) ' Do the Calc once
For i = 1 To L
t = VBA.Asc(VBA.Mid(S, i, 1))
R = R & VBA.Format(VBA.Hex(t), "00")
Next
Ascii2Hex = R
End Function

Public Function Hex2Ascii(ByVal S As String) As String
Dim i As Long, L As Long, t As Long
Dim R As String

L = VBA.Len(S) ' Do the Calc once
For i = 1 To L Step 2
t = VBA.Val("&H" & VBA.Mid(S, i, 2))
R = R & VBA.Chr(t)
Next
Hex2Ascii = R
End Function

Pieter

 

[RoyVidar]

Hello -

We are running Access 2000.
I am importing data from our business system where the primary keys are
case-sensitive - lowercase is unique from uppercase.

When I try to re-create the primary key in Access it is not allowing me to
do so - telling me I have duplication.

Is there any way to make the primary key case sensitive?

Any help is appreciated.

Thanks,
Tammy

Check out this http://support.microsoft.com/kb/244693, perhaps
using Binary field (bottom of the article)

You will probably need to fire the DDL on an ADO connection.