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.

Step 1:

Import the text file named “Import.txt”. This file includes the column heading.

import details notepad

Open the “Sample.accdb” database in Microsoft Access.

Select “Text File” from “Import & Link” group from the “External Data” tab in Microsoft Access Ribbon.

saved imports option


Step 2:

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.

saving import settings

Select the file from the File Open dialog box and click Open.

searching external data

Press OK.

confirming file path

Step 3:

When clicking the OK button it will to open the “Import Text Wizard” dialog box as shown below.

import text wizard

Click the “Advanced” button to open the “Import Specification” dialog box.

Step 4:

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."

import specification

Click the “Save As” button and change the Specification Name. Click “OK”.

saving import specification

Click “OK” in the “Import Specification” dialog box.

Step 5:

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.

import text with details

Step 6:

Click “Next”. It will ask to specify the column details.

import text with details

Step 7:

Click “Next”. It will ask to Primary key details.

import text with details 2

Step 8:

Click “Next”. It will ask the table name. Change the table name in the "Import to Table" text box.

final steps

Step 9:

Click “Finish”. You will get the following message.

confirmation alert

Click “Yes."

It will show the following dialog box. Press ‘Close’ to close the dialog.

saving import changes

You can see the User table in the database.

details displayed in database

Step 8:

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

Comments

 

Post a comment

Please correct the following: