change character set

[mitec]

Hi,

I' d like to insert data from MS access 2000 table to mysql table with utf8
character set. Problem is, that access table has a different character set
(coding) then utf8. Is there a way, how to change between character sets?
SQL statement character set does not work in access.
BTW: what kind of character set has access 2000 - ANSI?

Could anyone help please?

Mitec

 

[gllincoln]

Hi Mitec,

Access 2000 uses Unicode - I've had pretty good success sending an exported
tab delimited text to MySQL, importing the file using PHPMyAdmin.for a one
time or very occasional requirement. (updating the category assignment(s)
for a lengthy list of new products, for instance). A sort of roll your own
CMS add-in for a specific task.

If it's an ongoing process then I would create a function to export the data
a a file and on the MySQL side, create a PHP or Perl applet to insert the
file. Typical way this is put together for daily (or even hourly transfers)
is to create an incoming folder - you upload the data to the incoming
folder. A chron runs every xx minutes, if it finds a file, then based on the
name of the file, a particular processing takes place - then the file is
moved to the processed and/or backup folder.

Hope this helps,
Gordon

 

[mitec]

Hi Gordon,

thanks a lot for your replay!

Access 2000 uses Unicode - I've had pretty good success sending an exported
tab delimited text to MySQL, importing the file using PHPMyAdmin.for a one
time or very occasional requirement. (updating the category assignment(s)
for a lengthy list of new products, for instance). A sort of roll your own
CMS add-in for a specific task.

Could you answer me a few more questions?
What kind of coding uses access 2000? It should be a 16bit coding, shouldn't
it?
Anyway, why has this problem with translation between unicodes occured? Is
that because the mySQL database has utf8 coding?
Wrong translated characters are only special ones like: "ň, ľ, Ä, ...".

Between access and the mySQL database is a straight connection via ODBC
driver 3.51, so I can access mySQL database directly from access 2000 and I
could repare wrong translated characters with a script. But I don't like this
kind of solution very much.
But bigger problem is, when trying to download data from mySQL database to
access. This translation isn't uniquely determined and I can't write a script
to repleace.

Isn't there some command in acces to change characterset (like in mySQL, ...)?

 

[gllincoln]

Hi Mitec,

I'm not an expert on this subject - but with Access, a lot depends on your
computer's region and language options. To change these may require a
service pack specific to the language/region (depending on which
language/region we are attempting to use).

This gets more complicated when you consider that some 'problems' aren't
actually problems with Access or MySQL at all - they may be due to the
respective fonts you are viewing. A lot of the characters you gave as
examples are upper ascii - not necessarily unicode. I would try changing the
default font in Access to MS Arial Unicode and see what that looks like -
this font is well regarded for behaving nicely in a multilingual
environment.

It would be good to know the ascii value of any characters that aren't
appearing correctly. Try to find out what actual ascii is showing up on
both sides - if it's the same value, then the problem isn't on the database
side of things, it's on the display side i.e. good chance it's a font issue.

Hope this helps,
Gordon

 

[mitec]

Hi Gordon,

I' ve got some other stuff to do, that's why took me so long to answer.

I'm not an expert on this subject - but with Access, a lot depends on your
computer's region and language options. To change these may require a
service pack specific to the language/region (depending on which
language/region we are attempting to use).

Yes, looks like so.

This gets more complicated when you consider that some 'problems' aren't
actually problems with Access or MySQL at all - they may be due to the
respective fonts you are viewing. A lot of the characters you gave as
examples are upper ascii - not necessarily unicode. I would try changing the
default font in Access to MS Arial Unicode and see what that looks like -
this font is well regarded for behaving nicely in a multilingual
environment.

I am already using Arial Unicode.

It would be good to know the ascii value of any characters that aren't
appearing correctly. Try to find out what actual ascii is showing up on
both sides - if it's the same value, then the problem isn't on the database
side of things, it's on the display side i.e. good chance it's a font issue.

Problem is, in Access I can see only '?' instead of those bad translated
characters. The ASCII values of those bad translated characters appear then
as ASCII of '?'.

But you have brought me to conclusion, I will send the text as ASCII numbers
It is quite crazy way how to solve this problem, but if it will work...

Thank you very much for your help!

Mitec

 

[mitec]

Hi Gordon,

I' ve got some other stuff to do, that's why took me so long to answer.

I'm not an expert on this subject - but with Access, a lot depends on your
computer's region and language options. To change these may require a
service pack specific to the language/region (depending on which
language/region we are attempting to use).

Yes, looks like so.

This gets more complicated when you consider that some 'problems' aren't
actually problems with Access or MySQL at all - they may be due to the
respective fonts you are viewing. A lot of the characters you gave as
examples are upper ascii - not necessarily unicode. I would try changing the
default font in Access to MS Arial Unicode and see what that looks like -
this font is well regarded for behaving nicely in a multilingual
environment.

I am already using Arial Unicode.

It would be good to know the ascii value of any characters that aren't
appearing correctly. Try to find out what actual ascii is showing up on
both sides - if it's the same value, then the problem isn't on the database
side of things, it's on the display side i.e. good chance it's a font issue.

Problem is, in Access I can see only '?' instead of those bad translated
characters. The ASCII values of those bad translated characters appear then
as ASCII of '?'.

But you have brought me to conclusion, I will send the text as ASCII numbers
It is quite crazy way how to solve this problem, but if it will work...

Thank you very much for your help!

Mitec