Convert Microsoft Access database files in legacy encodings to Unicode
Be sure to back up your Access database files (.mdb) before proceeding.
- Export the tables to comma-separated value (CSV) text file:
- Open the database, select and right-click on the table to be exported.
- On the context menu, click Export...
- 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.
- Accept the default settings in the Export Text Wizard and click
Finish.
- 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.
- Delete all the rows (records) of the exported table:
- Under Objects panel, select Queries. Double-click
on Create query in Design view.
- Click Close in the Show Table dialog box.
- Choose View > SQL View.
- 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)
- Click Query > Run.
- In the Microsoft Access message box, click Yes to
delete all the rows from the specified table.
- Import the converted CSV text file back to Access database:
- On the File menu, point to Get External Data, and
then click Import.
- 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.
- In the Import Text Wizard dialog box, click the
Advanced... button.
- In the tablename Import Specification dialog box, select
Unicode (UTF-8) for Code Page, and then click OK.
- In the Import Text Wizard dialog box, click Finish
button.
- 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.
References:
About import/export specifications and Schema.ini files
Automate the import or export process
UnicodeConverter