change character set

  • Thread starter Thread starter mitec
  • Start date Start date
M

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
 
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
 
Hi Gordon,

thanks a lot for your replay!

gllincoln said:
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.
Click to expand...

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, ...)?
 
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
 
Hi Gordon,

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

gllincoln said:
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).
Click to expand...

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.
Click to expand...

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.
Click to expand...

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
 
Hi Gordon,

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

gllincoln said:
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).
Click to expand...

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.
Click to expand...

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.
Click to expand...

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
 
Back
Top