Creating an Import Specification in Access 2003
When you want to import text files in Access it can be saved as a specification and imported using VBA code.
Here is a step-by-step process for creating an import specification. The database “Sample.accdb” is used as an example.
Import the text file named “Import.txt”. This file includes the column heading.
Open the “Sample.accdb” database in Microsoft Access.
Select “Text File” from “Import & Link” group from the “External Data” tab in Microsoft Access Ribbon.
A dialog box called “Get External Data – Text File” will open as shown in the figure below. Click the Browse button to select the file name.
Select the file from the File Open dialog box and click Open.
When clicking the OK button it will to open the “Import Text Wizard” dialog box as shown below.
Click the “Advanced” button to open the “Import Specification” dialog box.
In the Field Information section, change the field names to the original column heading. In the example below, the imported fields are labeled “Name" and "Address."
Click the “Save As” button and change the Specification Name. Click “OK”.
Click “OK” in the “Import Specification” dialog box.
Click “Next” in the “Import Text Wizard”. It shows the data in the selected text file. Check the “First Row Contains Field Names” check box if the first row is the field names row in the text file.
Click “Next”. It will ask to specify the column details.
Click “Next”. It will ask to Primary key details.
Click “Next”. It will ask the table name. Change the table name in the "Import to Table" text box.
Click “Finish”. You will get the following message.
It will show the following dialog box. Press ‘Close’ to close the dialog.
You can see the User table in the database.
Once the specification is saved it can be used later in VBA code to import additional text files. See the sample code below:
DoCmd.TransferText acImportDelim, "User Import", "User", "C:\Import.txt"
Here, “User Import” is the specification created using the above steps.
“User” is the destination table name.
“C:\Import.txt” is the source file name with path.
Similarly, an Export Specification can be created using the Import Specification steps described above.
By Admin at 1 Dec 2015, 16:35 PM