Access97: import textfile specification error :(

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?
Who is Participating?
ptttConnect With a Mentor Commented:
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.
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.
ny971181Author Commented:
could you give me more details?
Thank you

Chris Lee
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.