Convert Microsoft Access database files in legacy encodings to Unicode

Be sure to back up your Access database files (.mdb) before proceeding.

  1. Export the tables to comma-separated value (CSV) text file:
    1. Open the database, select and right-click on the table to be exported.
    2. On the context menu, click Export...
    3. In the Export Table 'tablename' As dialog box, in the Save as type box, select Text Files (*.txt;*.csv;*.tab;*.asc). In the File name box, give the export file a name or accept the given file name, and click Save.
    4. Accept the default settings in the Export Text Wizard and click Finish.
  2. Convert to Unicode the CSV file, using UnicodeConverter or some other conversion tools that you're familiar with. Review the UTF-8-encoded output CSV file on a Unicode-compatible text editor to verify correct conversion.
  3. Delete all the rows (records) of the exported table:
    1. Under Objects panel, select Queries. Double-click on Create query in Design view.
    2. Click Close in the Show Table dialog box.
    3. Choose View > SQL View.
    4. In the Query 1: Select Query dialog box, type in the SQL command:

          DELETE FROM tablename;

      (or TRUNCATE TABLE tablename; for other DBMSes that support TRUNCATE command, which is faster than DELETE)
    5. Click Query > Run.
    6. In the Microsoft Access message box, click Yes to delete all the rows from the specified table.
  4. Import the converted CSV text file back to Access database:
    1. On the File menu, point to Get External Data, and then click Import.
    2. In the Import dialog box, in the Files Of Type box, select Text Files (*.txt;*.csv;*.tab;*.asc). Select the export CSV file and click Import button.
    3. In the Import Text Wizard dialog box, click the Advanced... button.
    4. In the tablename Import Specification dialog box, select Unicode (UTF-8) for Code Page, and then click OK.
    5. In the Import Text Wizard dialog box, click Finish button.
    6. In the Import Text Wizard message box, click OK to overwrite existing table or query 'tablename'. The message box will now display "Finish importing file...".

You may want to import to a new, different table and then compare with the original one. If the result looks good, delete the original table, and then rename the new table with the old name.

Keep in mind that the datatype for Unicode text fields is normally nchar, nvarchar, or ntext for common DBMS; therefore, you may need to modify the datatype of existing text fields so that they could hold Unicode text.


About import/export specifications and Schema.ini files
Automate the import or export process