The TransferText method allows you to import from, link to, and export to text files. It has the following syntax:
DoCmd.TransferText TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage
As you would expect, various parameters can be used to specify how the method should execute. These parameters are similar to the TransferDatabase and TransferSpreadsheet methods you have already seen. The following table explains the use of each parameter.
Parameter | Description |
---|---|
TransferType | Type of transfer to be performed. The default is acImportDelim. See the Access help documentation for a complete list and explanation. |
SpecificationName | Name of import or export specification you have saved in the current database. This argument can be left blank for text files that are delimited with certain characters or spacing to indicate the location of the fields. The example text file in the next section is a comma-delimited file. |
TableName | String expression that contains a table name you want to work with or a query you want to export. |
FileName | File name and path of the text file you want to work with. |
HasFieldNames | Use True to use the contents of first row of the spreadsheet as field names and False to treat the first row as data. False is the default. |
HTMLTableName | Use with acImportHTML or acLinkHTML. Name of table or list in HTML file you want to work with. If blank, the first one is assumed. |
CodePage | Long value indicating the character set of the code page. |
Next, we’ll jump right into importing data from a text file.
Try It Out-Importing Data from a Text File
Now, you import data from a text file into a new table, called tblEmails, in your Access database.
Create a text file as shown in Figure 7-4 and save it in C:\temp.
Figure 7-4
Add the following procedure to the module in your database:
Sub TestTransferText() DoCmd.TransferText acImportDelim, , _ "tblEmails", "C:\Temp\emails.txt", True End Sub
Run the procedure from the Immediate Window in Visual Basic Editor.
Return to the database window and you should see a screen similar to that shown in Figure 7-5.
Figure 7-5
How It Works
First, you created a text file that contained comma-delimited records. You then created a procedure to import the comma-delimited file to your database:
Sub TestTransferText() DoCmd.TransferText acImportDelim, , _ "tblEmails", "C:\Temp\emails.txt", True End Sub
The TransferText method imported the comma-delimited file into a new table called tblEmails, as shown in Figure 7-5. Note that the parameter for the SpecificationName was left blank because it is not required when working with delimited files.