• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1686
  • Last Modified:

Export to text error: Microsoft was unable to append all the data to the table

Hello all,

I am exporting a access table with around 90 columns and they are all in text format with a field size of 255.  The export to text is in vba code: DoCmd.TransferText acExportDelim, "Qry_toExport Export Specification", "Qry_toExport", filenamepath.  My spec file is all text also, with all the same column names.  When I do export I receive the error message: "Microsoft was unable to append all the data to the table"
I click yes to proceed anyway and then it exports it and upon further review I do not see anything wrong with the text file.  All seems accruate, but I am rolling out db to other users and I need to get rid of that error message.   your help is much appreciated.
0
Oscar Rodriguez
Asked:
Oscar Rodriguez
  • 6
  • 4
  • 3
  • +2
1 Solution
 
jerryb30Commented:
How many records?
How are you checking that there is nothing wrong with text file.  With That many columns, missing one or two would be simple?
0
 
Oscar RodriguezAuthor Commented:
Yes... I double checked all columns and names.. both have exactly the same...  I have check the text output and matched them up against the table in access all is good...
0
 
OnALearningCurveCommented:
I have seen this error before when exporting queries to Excel.

It usually happens when one of the fields results in an error (like a divison by zero etc).  Does your query have any calculated fields in it?  If so it could be one of those throwing up the error.

Mark.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Oscar RodriguezAuthor Commented:
Thanks, but I have no calculations... Its an Access table being exported as a txt file.
0
 
Rey Obrero (Capricorn1)Commented:
try importing the text file to a temp table and compare to the original table.
0
 
Oscar RodriguezAuthor Commented:
I tried that... Comparison is exact between the original and export txt file.
0
 
jerryb30Commented:
Try docmd.setwarnings false
before your transferText
and docmd.setwarnings true
afterwards
0
 
Rey Obrero (Capricorn1)Commented:
does it always happen when you do the export? on the same table.

how about exporting a different table, do you also get the error?
0
 
clarkscottCommented:
What is your table-to-text delimited on?     If comma, are there commas in any of your text fields?

Just a shot-in-the-dark......

Scott C
0
 
Oscar RodriguezAuthor Commented:
The table format stays the same, its just different data that comes.  The table gets deleted and the new data gets populated, then it gets exported.
0
 
Rey Obrero (Capricorn1)Commented:
ojrod27,


i repeat

does it always happen when you do the export?  even with new data
0
 
Oscar RodriguezAuthor Commented:
Yes... for all new data.. it happens every timie
0
 
Rey Obrero (Capricorn1)Commented:
try
decompiling your db, see if this help

http://www.granite.ab.ca/access/decompile.htm
0
 
Oscar RodriguezAuthor Commented:
I'll go with jerryb30.  Even thought I get the error message, the output is not affected.  His code gets rid of the message and thats good enought for me...

Thanks all for the help
0
 
jerryb30Commented:
ojrod27
While the easy solution works, the error indicates possible corruption in db, application drivers, or data validation during import.  It is  well worth your time to make sure you have exhausted all possible avenues before deploying a possibly faulty application.
Aside from decompile/recompile,importing objects into a new database, and maybe see it problem occurs on another computer with Access 97.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now