Access97: import textfile specification error :(

Posted on 1997-07-07
Last Modified: 2008-02-26
Hi there, I tried to create a module to import text files into tables. When I put in the textfile specification like "cm import specification", the error is "no such specification is fould".  If I leave it blank like " ", the error is "3791: field F1 is not in the destination table" and I didn't see that msg when I import the file manually. So, could anyone tell me what caused the problem and how to fix it?
Question by:ny971181
  • 2

Expert Comment

ID: 1602992
When you run the import wizard you get the chance to change the field names (the third page of the wizard), you must make sure that the first fieldnames match those in the destination table. I would imagine that when you imported the text file "manually" that you imported into a new table, and now you want to import into an existing table. When you import into a new table the field names are irrelevant.

Once you have entered all the details into the wizard you can save the import definition to use it again, select the advanced button, then select Save as.. and enter the name you want to use for the specification.

Now you can run the import from code, look at the TransferText method in the help file.

Author Comment

ID: 1602993
could you give me more details?
Thank you

Chris Lee

Accepted Solution

pttt earned 10 total points
ID: 1602994
The best way to import a text file in a module is to first import the text file manually into a new table.

First select "New" on the database page of your project, this will run the database wizard. Choose "Import Table". Select the text file you want to import. Then, select whether your text file is Fixed Width or Delimited, and click the "Next" button. This page allows you to either set the delimiting character or the width of the fields. When you have done this select "Next" again.

Choose "In a new table" and move to the next page. Now you can set the field names for the new table, name each field with a name which reflects the data contained in the field as this will make it easier to track down any problems. Click on each column in the displayed table and change the field name.

Move to the next page and set the primary key if required. On the next page enter the name for the new table, something like "Test" will do. Now run the import, by clicking the "Finish" button.

If everything went to plan you should now have a new "Test" table which has the same structure as the table you want to use. Open the table and check that all the data was imported correctly.

If the import was OK then it is time to create the import specification. Select "New" again from the database page. Again choose import and select the text file to import. Select "Fixed Width" or "Delimited". Set the delimiter or the field widths. Choose "In an existing table" and select the table you want to import to.

Click the "Next" button and you will be taken to the last page of the wizard. Click the "Advanced" button, this will allow you to save the import specification. Check the details are correct and click the "Save As.." button.

If you get an error message such as "The field 'Name' does not exist in the table or query you have chosen for your import or export operation." then it means that your table has too few fields to hold all the data in the text file. Cancel the wizard and compare your "Test" table with the one you are trying to import into and make the necessary changes to the import table.

Once you have corrected any problems run the wizard again. When prompted for a Specification Name enter something like "My Import Specification", there is no need to complete the import once you have saved the specification.

Now onto the module. To use the specification enter the following line, (or one similar)

DoCmd.TransferText acImportDelim, "My Import Specification", "MyTable", "MyTextFile"

Where:      "My Import Specification" is the name that you saved the specification to.      "MyTable" is the name of the table you wish to import into, and "MyTextFile" is the name and path of the text file to import from.

Note: I used the acImportDelim constant, if you are importing from a fixed width text file you need to use the acImportFixed constant.

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Printing calendar search results in Outlook 2010. 1 48
how to add and add vat in excel 9 46
Excel If tests 10 64
Excel 2016 formulas 5 26
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now