Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access97: import textfile specification error :(

Posted on 1997-07-07
3
Medium Priority
?
381 Views
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?
0
Comment
Question by:ny971181
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Expert Comment

by:pttt
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.
0
 
LVL 1

Author Comment

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

Chris Lee
0
 

Accepted Solution

by:
pttt earned 30 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.
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

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 describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

721 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