How to convert MS Access database encoding to UTF-8?

Question

I am currently working on a legacy Classic ASP + MS-Access application. I recently converted all the .asp files to UTF-8 from ISO-8859 (Windows) encoding.

The problem I have now is that the text stored inside the database (French with accented characters) display improperly when rendered inside the web pages because the encodings are inconsistent. How do I convert my MS Access database encoding from ISO-8859 to UTF-8?

Aymara

iDevlop: doesn't quite work that way... – Aymara

Answer 1

How do I convert my MS Access database encoding from ISO-8859 to UTF-8?

You don't. Access can handle Unicode text but it DOES NOT store it as UTF-8. There are ways to directly insert UTF-8 encoded text into Access Text fields but that leads to strange behaviour as illustrated in my other answer here.

For an ASP application, simply use .asp pages encoded as UTF-8, tell IIS to produce UTF-8 output (via the <%@ CODEPAGE = 65001 %> directive), and let IIS and the Access OLEDB driver handle the conversion between "Access Unicode" and UTF-8.

For a detailed example of Access, Classic ASP, and UTF-8 see my answer here:

Capture and insert Unicode text (Cyrillic) into MS access database

Important Note

Be aware that you should NOT be using an Access database as a back-end data store for a web application; Microsoft strongly recommends against doing so (ref: here).

Perlman

Answer 2

You may be able to write an Access UPDATE statement that uses the StrConv function to convert the text to Unicode.

See http://www.techonthenet.com/access/functions/string/strconv.php

Pigskin

Answer 3

I ended up using Server.HTMLEncode for text coming out of my database. It completely broke all the HTML code I had stored in there so I has to wrote an ugly hack to avoid converting html tags to html-safe strings. It now works, but it's certainly not the most elegant solution...

Aymara

Answer 4

You can do this in Access 2000.
Right click on the table you want to convert.
Select “Export”
In the “Save as type” drop-down list choose “Text Files (.txt;.csv;.tab;.asc)”, enter a name for the exported file and press "Save"
Choose delimited and press "Advanced"
In the “Language” list choose “All”, in the “Code Page” list choose “Unicode (UTF-8)”
Change any other settings here that you want
Press “OK”, Press "Next" and select your delimiter. Check the option to Include Field Names in First Row
Press "Next", confirm the destination folder for the new file and click "Finish"

The next stage is to reimport the saved file:
Select File, Get External Data, Import
In the “Files of type” drop-down list choose “Text Files (.txt;.csv;.tab;.asc)”
Navigate to the saved text file and press Import
Specify delimited and press "Next"
specify the delimiter character and press "Next"
Press "Advanced" button and once again choose All in the “Language” list, and choose “Unicode (UTF-8)” in the “Code Page”
Select radio button to save in a new table and press "Next"
Select radio button for "No Primary Key" (otherwise, Access will insert an IDfield as the first field in the table, which you may not want) Click "Next", check the name of the imported table, and click "Finish"

NOTE:

1. You will have to edit the new table to give it field names, but these should be in the first record if you exported them.

2. Access will autoassign Number type to imported Boolean types and Date types, so you may need to change these

Fryd