You can use the TransferText action to import or export text between the current Microsoft Access database (.mdb) or Access project (.adp) and a text file. You can also link the data in a text file to the current Access database. With a linked text file, you can view the text data with Microsoft Access while still allowing complete access to the data from your word processing program. You can also import from, export to, and link to a table or list in an HTML file (*.html).
Note If you link to data in a text file or an HTML file, the data is read-only in Microsoft Access.
Setting
The TransferText action has the following arguments.
| Action argument | Description |
|---|---|
| Transfer Type | The type of transfer you want to make. You can import data from, export data to, or link to data in delimited or fixed-width text files or HTML files. You can also export data to a Microsoft Word mail merge data file, which you can then use with the Microsoft Word mail merge feature to create merged documents such as form letters and mailing labels. |
| Select Import Delimited, Import Fixed Width, Import HTML, Export Delimited, Export Fixed Width, Export HTML, Export Word for Windows Merge, Link Delimited, Link Fixed Width, or Link HTML in the Transfer Type box in the Action Arguments section of the Macro window. The default is Import Delimited. | |
| Note Only Import Delimited, Import Fixed Width, Export Delimited, Export Fixed Width, or Export Word for Windows Merge transfer types are supported in an Access project (.adp).. | |
| Specification Name | The specification name for the set of options that determines how a text file is imported, exported, or linked. For a fixed-width text file, you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file. |
| You can use the Import or Link Tables subcommand of the Get External Data command or the Export command on the File menu to create a specification for a particular type of text file. For example, a delimited text file that uses tabs to separate fields and has an MDY format for dates. When you click one of these commands and select a type of text file to import, export, or link, the Import Text Wizard, Export Text Wizard, or Link Text Wizard runs. You can click the Advanced button in the wizard and define and save a specification in the dialog box that appears. You can then type the specification name in this argument whenever you want to import or export the same type of text file. | |
| You can import, export, or link delimited text files without typing a specification name for this argument. In this case, Microsoft Access uses the defaults from the wizard dialog box. Microsoft Access uses a predetermined format for mail merge data files, so you don't ever need to type a specification name for this argument when you export these types of files. You can use import/export specifications with HTML files, but the only part of the specification that applies is the specification for data type formatting. | |
| Table Name | The name of the Microsoft Access table to import text data to, export text data from, or link text data to. You can also type the name of the Microsoft Access query you want to export data from. This is a required argument. |
| If you click Import Delimited, Import Fixed Width, or Import HTML in the Transfer Type box, Microsoft Access appends the text data to this table if the table already exists. Otherwise, Microsoft Access creates a new table containing the text data. | |
| In Microsoft Access 2000, you can't use an SQL statement to specify data to export when you are using the TransferText action. Instead of using an SQL statement, you must first create a query and then specify the name of the query in the Table Name argument. | |
| File Name | The name of the text file to import from, export to, or link to. Include the full path. This is a required argument. |
| Microsoft Access creates a new text file when you export data from Microsoft Access. If the file name is the same as the name of an existing text file, Microsoft Access replaces the existing text file. | |
| If you want to import or link a particular table or list in an HTML file, you can use the HTML Table Name argument. | |
| Has Field Names | Specifies whether the first row of the text file contains the names of the fields. If you select Yes, Microsoft Access uses the names in this row as field names in the Microsoft Access table when you import or link the text data. If you select No, Microsoft Access treats the first row as a normal row of data. The default is No. |
| Microsoft Access ignores this argument for Microsoft Word for Windows mail merge data files because the first row must contain the field names. | |
| When you export a Microsoft Access table or select query to a delimited or fixed-width text file, Microsoft Access inserts the field names of your table or select query into the first row of the text file if you've selected Yes for this argument. | |
| If you are importing or linking a fixed-width text file and select Yes in this box, the first row containing the field names must use the field delimiter set in the import/export specification to separate the field names. If you are exporting to a fixed-width text file and select Yes for this argument, Microsoft Access inserts the field names into the first row of the text file with this delimiter. | |
| HTML Table Name | The name of the table or list in the HTML file that you want to import or link. This argument is ignored unless the Transfer Type argument is set to Import HTML or Link HTML. If you leave this argument blank, the first table or list in the HTML file is imported or linked. |
| The table or list name in the HTML file is determined by the text specified by the <CAPTION> tag, if there's a <CAPTION> tag. If there's no <CAPTION> tag, the name is determined by the text specified by the <TITLE> tag. If more than one table or list has the same name, Microsoft Access distinguishes them by adding a number to the end of each name; for example, Employees1 and Employees2. | |
| Code Page | The name of character set used with the code page. |
Remarks
You can export the data in Microsoft Access select queries to text files. Microsoft Access exports the result set of the query, treating it just like a table.
Text data that you append to an existing Microsoft Access table must be compatible with the table's structure.
This action is similar to pointing to Get External Data or Export on the File menu of the Database window and clicking Import or Link Tables. You can use these commands to select a source of data, such as Microsoft Access or a type of database, spreadsheet, or text file. If you select a delimited or fixed-width text file or an HTML file, a wizard prompts you to select the name of the file and other options. The arguments of the TransferText action reflect the options in these wizards.
Tip An import/export specification stores the information Microsoft Access needs to import, export, or link a text file. You can use stored specifications to import, export, or link text data from or to similar text files. For example, you might receive weekly sales figures in a text file from a mainframe computer. You can create and save a specification for this type of data and then use the specification whenever you add this data to your Microsoft Access database.
Note If you query or filter a linked text file, the query or filter is case-sensitive.
To run the TransferText action in Visual Basic, use the TransferText method of the DoCmd object.