Access 2007 importing data with extended character sets.

[Steve]

I am trying to move over to Access large data sets. This data has extended
ascii characters in them like ç, é etc...

The characters are intact in the source data pull (txt file from a SQL 7
qry) but when I import into an Access 2007 table all of those characters get
switched to odd characters. Like é gets switched to Θ and so on.

Ideas or suggestions?

Thanks,
Steve

 

[Sylvain Lafontaine]

The first thing to check would be to make sure that nchar, nvarchar and
ntext fields have been used on SQL-Server 7 and not char, varchar and text.

 

[Steve]

Thank you. THe database was developed by a third party and I cannot change
the field types. I have confirmed that they are char, varchar, text, etc.

Is there something that can be done during the import process to recognise
the characters?

Steve

 

[Sylvain Lafontaine]

How unlucky you are, you have just felt into the codepage hell, which is
exactly why everyone using international characters have stopped using ANSI
(char, varchar and text) and are using UNICODE (nchar, nvarchar and ntext)
instead.

Using codepages is a continuously moving target; everytime your correct the
situation at one point, you will create another one at another point. If
you are lucky, this second problem won't bite you; if you're not, well, you
know the drill.

A first possibility would be to look at the automatic translation parameter
for the DSN or ODBC connection string.

Other possibilities would be to try setting the Windows (not the OEM)
codepage of your machine to the same Windows codepage as SQL-Server *OR* to
set your Windows codepage to the same Windows codepage as another client
machine's which is working correctly against this SQL-Server.

Instead of setting the codepage of your machine, you could also try to
create new Select statements and add a Collate statements against these
fields in the Select query. However, I never tried using Collate statements
with ANSI field; so I cannot tell you if this will work or not.

Another solution would be to simply translate each wrong character with the
right one, either after the importation of by using a Select query that will
make the conversion on the fly (or that will use Collate statements, if this
works).

However, probably that your only real long term solution is to replace all
these ANSI fields with UNICODE fields and even then, you might have problem
for data already there if the codepage for the machine that has been used to
import the data into the sql-server is not the same as the codepage of the
sql-server.

 

[Charax]

Steve,

After creating a new database with nvarchar, etc., perhaps data conversion
to Unicode will be the best route. I suggest looking at SIL Converters
(free) at http://scripts.sil.org/EncCnvtrs I've used it with success.

One other point. Don't use Unicode outside the usual ASCII character set in
your primary keys as there is a bug in ODBC drivers for SQL Server. In an
SQL Server table connected to an Access MDB through ODBC, there can be an
erroneous redundant translation between Unicode and ASCII characters, and
the translation only happens on primary key. The result is that every column
in the record will show #Deleted.

Cheers,

Charax

 

[A a r o n K e m p f]

you need A c c e s s D a t a P r o j e c t s

Steve,

After creating a new database with nvarchar, etc., perhaps data conversion
to Unicode will be the best route. I suggest looking at SIL Converters
(free) at http://scripts.sil.org/EncCnvtrs I've used it with success.

One other point. Don't use Unicode outside the usual ASCII character set
in your primary keys as there is a bug in ODBC drivers for SQL Server. In
an SQL Server table connected to an Access MDB through ODBC, there can be
an erroneous redundant translation between Unicode and ASCII characters,
and the translation only happens on primary key. The result is that every
column in the record will show #Deleted.

Cheers,

Charax