You can use the TransferText action to import or export text between the current Microsoft Office Access 2007 database (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.) (.mdb or .accdb) or Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects, such as tables and views.) (.adp) and a text file. You can also link (link (tables): An action that establishes a connection to data from another application so that you can view and edit the data in both the original application and in Access.) the data in a text file to the current Access database. With a linked text file, you can view the text data with 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 Access.
Note This action will not be allowed if the database is not trusted. For more information about enabling macros, see the links in the See Also section of this article.
The TransferText action has the following arguments.
The type of transfer you want to make. You can import data from, export data to, or link to data in delimited (delimited text file: A file containing data where individual field values are separated by a character, such as a comma or a tab.) or fixed-width text files (fixed-width text file: A file containing data in which each field has a fixed width.) or HTML files. You can also export data to a Microsoft Office Word 2007 mail merge data file, which you can then use with the 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 Builder pane. 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).
The specification name for the set of options that determines how a text file is imported or linked. For a fixed-width text file, you must either specify an argument or use a schema.ini (schema: A description of a database that defines the attributes of the database, such as tables, fields (columns), and properties.) file, which must be stored in the same folder as the imported or linked text file.
To create a specification for importing or linking a text file:
- On the External Data tab, in the Import group, click Text File.
- In the Get External Data dialog box, enter the path of the source text file in the File name box.
- Click the option you want for storing the data (import, append, or link), and click OK.
- In the Import Text Wizard dialog box, click Advanced.
- Specify the options you want for this specification, then click Save As.
- Enter the name you want for the specification, then click OK.
- You can manage existing specifications by clicking Specs in the specification dialog box.
- Click OK to close the specification dialog box.
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, Access uses the defaults from the wizard dialog box. 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.
The name of the Access table to import text data to, export text data from, or link text data to. You can also type the name of the Access query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form or report.) 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, Access appends the text data to this table if the table already exists. Otherwise, Access creates a new table containing the text data.
You can't use an SQL statement (SQL string/statement: An expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE and ORDER BY. SQL strings/statements are typically used in queries and in aggregate functions.) 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.
The name of the text file to import from, export to, or link to. Include the full path. This is a required argument.
Access creates a new text file when you export data from Access. If the file name is the same as the name of an existing text file, 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, Access uses the names in this row as field names in the Access table when you import or link the text data. If you select No, Access treats the first row as a normal row of data. The default is No.
Access ignores this argument for Word for Windows mail merge data files because the first row must contain the field names.
When you export an Access table or select query (select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.) to a delimited or fixed-width text file, 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, 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, Access distinguishes them by adding a number to the end of each name; for example, Employees1 and Employees2.
||The name of the character set (character set: A grouping of alphabetic, numeric, and other characters that have some relationship in common. For example, the standard ASCII character set includes letters, numbers, symbols, and control codes that make up the ASCII coding scheme.) used with the code page (code page: A table that relates the binary character codes used by a program to keys on the keyboard or to the appearance of characters on the display. Code pages are a means of providing support for the languages used in different countries/regions.).
You can export the data in Access select queries to text files. Access exports the result set (result set: The set of records that results from running a query or applying a filter.) of the query, treating it just like a table.
Text data that you append to an existing Access table must be compatible with the table's structure.
This action is similar to clicking Text File in the Import or Export group on the External Data tab. The arguments of the TransferText action reflect the options in the wizard started by the Text File command.
An import/export specification stores the information 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 Access database.
Note If you query or filter a linked text file, the query or filter is case-sensitive (case-sensitive: Capable of distinguishing between uppercase and lowercase letters. A case-sensitive search finds only text that is an exact match of uppercase and lowercase letters.).
To run the TransferText action in a Visual Basic for Applications (VBA) module, use the TransferText method of the DoCmd object.