ACC2000: Using SQL to Export to Unicode by Means of the Jet Provider and Text ISAM

ID: Q234201

The information in this article applies to:

Microsoft Access 2000

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

You can use Structured Query Language (SQL) to export a table to a Unicode text file by means of the Microsoft Jet Provider and Text ISAM.

MORE INFORMATION

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

The first two examples show you how to use queries to import and export text to and from the sample database Northwind.mdb. To set up an example, create a new query (do not add any tables), click the SQL button, and type the corresponding SQL statement. Then, to import or export, just run the query. The third example shows you how to export a table in Unicode format.

Example 1

The following SQL code shows you how to export a table to a text file:


SELECT * INTO
[Text;FMT=Delimited;HDR=Yes;DATABASE=C:\TEMP;].[OutCustomers#txt]
FROM Customers;

Example 2

The following SQL code shows you how to import a table from a text file (this is the one created in the first example):


SELECT * INTO ImpCustomers
FROM [Text;FMT=Delimited;HDR=Yes;DATABASE=C:\TEMP;].[OutCustomers#txt];

Example 3

To modify the SQL statement so that it exports the data in Unicode text format, add the following option to the connection argument:


CharacterSet=Unicode

For example, you can modify the statement in Example 1 to the following:


SELECT * INTO
[Text;FMT=Delimited;HDR=Yes;DATABASE=C:\TEMP;CharacterSet=Unicode].[OutCustomersUNI#txt]
FROM Customers;

Additional query words: inf

Keywords : kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto