'ss' and 'ß'

[Joshuas]

Hi,

We have a problem with Acces 2003 and 2007 and the ss and sharp ß
character.
We have a database in a MySql-server. The primairy key is on
'prof_id'. whenever we make a selection of the Mysql table and store
the data into a local access table and set the primairy key on prof_id
it says the are duplicates. Actually there are no duplicates but
Access handles records like for example 'straße1234' and 'strasse1234'
as the same. How is this possible?

Kind regards

 

[Stefan Hoffmann]

hi,

We have a problem with Acces 2003 and 2007 and the ss and sharp ß
character.

'ss' and 'ß' are equal when compared or not, this depends on the
implementation.
You may take a look at StrComp() and its parameter.

There is also a sortorder:

http://support.microsoft.com/?scid=kb;en-us;279401&x=6&y=14

but I'm not sure, whether you can choose an order which can distinguish
'ss' and 'ß'.


mfG
--> stefan <--

 

[Rick Brandt]

Hi,

We have a problem with Acces 2003 and 2007 and the ss and sharp ß
character.
We have a database in a MySql-server. The primairy key is on
'prof_id'. whenever we make a selection of the Mysql table and store
the data into a local access table and set the primairy key on prof_id
it says the are duplicates. Actually there are no duplicates but
Access handles records like for example 'straße1234' and 'strasse1234'
as the same. How is this possible?

Kind regards

I can't give the technical reason but the immediate debug window gives...

?("straße1234" = "strasse1234")
True

....in all versions (97 - 2007). "ß" is just a shorthand way to write "ss"
is it not? I expect that to be the reason. The fact that the same
characters are not used matters no more than it does when comparing "A" to
"a".

 

[Stefan Hoffmann]

hi Rick,

...in all versions (97 - 2007). "ß" is just a shorthand way to write "ss"
is it not?

Originally 'ß' is a ligation between long s and short z in some Gothic
types or a ligation between long s and short s in Antiqua types.

In most cases it means 'ss'.


mfG
--> stefan <--

 

[Joshuas]

Hello Stefan,

It's not about the sort order. In the MySql table EXAMPLE we have over
450.000 unique records, with the primairy key set on prof_id.
When I do a SELECT * INTO LOCAL FROM EXAMPLE; it make a local table in
Access with all the records we have in the MySql table EXAMPLE.
Thereafter I want to alter the table with ALTER TABLE LOCAL ALTER
COLUMN prof_id TEXT PRIMARY KEY;
and it returns with error 3022 (the changes you requested to the
table, were not successful because they would create a duplicate
values in the index, primary key or relationship....) This should not
be possible since the key in MySql was allready set to the prof_id.
The query

SELECT prof_id, Count(prof_id) AS A
FROM example
GROUP BY prof_id
HAVING (Count(prof_id))>1;

learns me there are 215 'duplicates', all of them have the same
problem as the example in my first post: 'straße1234' and
'strasse1234' are duplicates. It sees ß and ss as the same.
Strange detail: If I run the same query from Access on the MySql table
with the MySql ODBC driver it doesnt give a result (so it doesnt see
these as duplicates), neither does this same query direct sql-query
from the mysqlserver-cmdline, this is correct because 'straße1234' and
'strasse1234' are definitly two different records and the 'ß' is one
character while 'ss' are two characters.

Kind regards

 

[Stefan Hoffmann]

hi Joshuas,

It's not about the sort order. In the MySql table EXAMPLE we have over
450.000 unique records, with the primairy key set on prof_id.

MySQL has a sort order where 'ss'<>'ß'. This is not the case in Access.

Take a look at link I've posted before. This may solve your problem.


mfG
--> stefan <--

 

[Joshuas]

Hi Stefan,

I followed the link you posted before, but the things mentioned didnt
solve the problem.

If you have some other tips...In the meantime I'll try to find a
working solution for this problem.

Kind regards

 

[Michael Gramelspacher]

I can't give the technical reason but the immediate debug window gives...

?("straße1234" = "strasse1234")
True

...in all versions (97 - 2007). "ß" is just a shorthand way to write "ss"
is it not? I expect that to be the reason. The fact that the same
characters are not used matters no more than it does when comparing "A" to
"a".

try this:

?("straße1234" = "strasse1234")
True
?("strasse1234" = replace("straße1234","ß",asc(223)))
False
?("straße1234" = replace("straße1234","ß",asc(223)))
False

 

[Guillermo_Lopez]

try this:

?("straße1234" = "strasse1234")
True
?("strasse1234" = replace("straße1234","ß",asc(223)))
False
?("straße1234" = replace("straße1234","ß",asc(223)))
False- Hide quoted text -

- Show quoted text -

Just a quick question out of curiosity. Why do you have a primery key
with unusual characters?

However, it seems from your comments that you are willing to modify
the data of these conflicting records but can't because the ALTER
command causes a conflict in the Keys. Try then, either creating a new
primary Key field or Use a New primery keyfield in your Access Table,
or Manually change your 250 records. Its not too much, you only have
to change 125 records manually that shouldn't take you more than one
hour once you have identified them.

- GL

 

[Joshuas]

Just a quick question out of curiosity. Why do you have a primery key
with unusual characters?

In the German Language the ß is not a unusual character.
While the primairy key field 'prof_id' is a field which returns in a
few other tables which are related.
Changing the ß to a different character is no option, to much related
tables/records whichs has to be updated in that case.
It is a pretty complex thing we do at our office, too much to explain
here.
Because not everybody in our company is familiar with (My)Sql we use
Access as front-end to make analyses, edit data ect.
The table i am talking about contains the raw address information of
the participants of our online surveys.
We take the daily data locally into a access table. The data will be
analysed, cleaned (participants with wrong emailadresses, wrong zip-
codes ect will be deleted).
The key is combined out of some fields the participant filled in our
surveys and contains the lastname, (which often contains a ß in
Germany, and also stored as ß in the MySql table...charset Latin1 =
iso8859-1)
After cleaning the data we set the primairy key back to the prof_id in
Access. Now Access says it has duplicates, while there are actually no
duplicates. (MySql handles them as it should, these records are
unique.). After this step we insert the cleaned data we allready had.
By doing this we always have the up to date address-information of our
participants. But with the exception we loose some records were for
example we have to ID's one 'straße1234' and one 'strasse1234'.
I really can not understand why Access sees them as the same. while ß
is one character, ss are two characters.

Joshua

 

[Michael Gramelspacher]

In the German Language the ß is not a unusual character.
While the primairy key field 'prof_id' is a field which returns in a
few other tables which are related.
Changing the ß to a different character is no option, to much related
tables/records whichs has to be updated in that case.
It is a pretty complex thing we do at our office, too much to explain
here.
Because not everybody in our company is familiar with (My)Sql we use
Access as front-end to make analyses, edit data ect.
The table i am talking about contains the raw address information of
the participants of our online surveys.
We take the daily data locally into a access table. The data will be
analysed, cleaned (participants with wrong emailadresses, wrong zip-
codes ect will be deleted).
The key is combined out of some fields the participant filled in our
surveys and contains the lastname, (which often contains a ß in
Germany, and also stored as ß in the MySql table...charset Latin1 =
iso8859-1)
After cleaning the data we set the primairy key back to the prof_id in
Access. Now Access says it has duplicates, while there are actually no
duplicates. (MySql handles them as it should, these records are
unique.). After this step we insert the cleaned data we allready had.
By doing this we always have the up to date address-information of our
participants. But with the exception we loose some records were for
example we have to ID's one 'straße1234' and one 'strasse1234'.
I really can not understand why Access sees them as the same. while ß
is one character, ss are two characters.

Joshua

I do not know why 'ss' would ever equal 'ß". They are two distinct characters.

My mistake yesterday.

Sub test()

Dim s As String
Dim s1 As String

s = "Straße"
s1 = Replace("Straße", "ß", Asc("ß"))

MsgBox IIf(s = s1, "equal", "not equal")

End Sub

?call test

I think this strongly suggests that 'ss' is not "ß"

 

[Joshuas]

I do not know why 'ss' would ever equal 'ß". They are two distinct characters.

My mistake yesterday.

Sub test()

Dim s As String
Dim s1 As String

s = "Straße"
s1 = Replace("Straße", "ß", Asc("ß"))

MsgBox IIf(s = s1, "equal", "not equal")

End Sub

?call test

I think this strongly suggests that 'ss' is not "ß"

Yep, your example shows ss != ß, nevertheless in my table, Access
still sees straße1234 and strasse1234 as duplicates in the local
table.
A simple select query: SELECT prof_id FROM local WHERE id =
'straße1234'; returns me to rows:
straße1234
and
strasse1234

while these are two different records.

 

[John W. Vinson]

Yep, your example shows ss != ß, nevertheless in my table, Access
still sees straße1234 and strasse1234 as duplicates in the local
table.
A simple select query: SELECT prof_id FROM local WHERE id =
'straße1234'; returns me to rows:
straße1234
and
strasse1234

while these are two different records.

I expect you're both right!

In the same way, "A" and "a" are indeed different characters with different
ASCII representations; but Access/JET indexes treat them as being the same. If
the Primary Key of a table contains "CA" then Access will not allow "Ca" in a
new record. However, the VBA comparison will show that "CA" and "Ca" are in
fact different (depending on the setting of Option Compare of course).

 

[Joshuas]

I expect you're both right!

In the same way, "A" and "a" are indeed different characters with different
ASCII representations; but Access/JET indexes treat them as being the same.. If
the Primary Key of a table contains "CA" then Access will not allow "Ca" in a
new record. However, the VBA comparison will show that "CA" and "Ca" are in
fact different (depending on the setting of Option Compare of course).

Ok I see Access 'A' and 'a' as the same, they are both one byte
characters, but how I really can not understand that it handle 'ß' and
'ss' as one as 'ß' is one byte and 'ss' are two bytes. Is there anyway
to avoid this problem?

 

[Rick Brandt]

Ok I see Access 'A' and 'a' as the same, they are both one byte
characters, but how I really can not understand that it handle 'ß' and
'ss' as one as 'ß' is one byte and 'ss' are two bytes. Is there anyway
to avoid this problem?

I suppose the designers of Access/Jet made a decision that if 'ß' is a
common and completely acceptable way to represent 'ss' that they should then
treat them the same in text comnparisons.

Based on the description of your process it seems to me that you have no
real reason to use the MySQL PK as the PK in Access. Just use a surrogate
key for the updates in Access.