Solved

Put Import Errors into one table

Posted on 2004-09-07
8
301 Views
Last Modified: 2008-02-01
I have a small VBA macro which imports data from over 350 text files into a table.  It seems to work fine, except that almost all the text files have at least one error in them, so I end up with over 300 tables of Import Errors.  What's the easiest way to make all those import errors go into one table?  I'll need to add a field to that table and populate it with the file that caused the error.
0
Comment
Question by:DanR
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 125 total points
ID: 11999597
This behavior is by design, you can't change the name of the table that Import Errors go to.

What you can do, however, is use temporary tables (I prefix these with timp_), that have all of your fields as Text, and import all your tables into them.  Then you can run whatever queries/code you like to 'clean up' the data before appending them to the final destination table.

Hope this helps.
-Jim
0
 
LVL 2

Expert Comment

by:PendragonZero
ID: 12000178
Not sure how comfortable with code you are.
But after each import, I'd just check to see if an error occured and append them to an error table with the file name.

Let me know your setup, and I can write it for you.
Pen
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 125 total points
ID: 12002133
on command button click

Private Sub Command1_Click()
Dim tbl As TableDef, db As Database, aSQL As String
Set db = CurrentDb

For Each tbl In db.TableDefs
    If tbl.Name Like ("*" & "ImportErrors") Then
    aSQL = "Insert Into tblErrors Select " & tbl.Name & ".* From " & tbl.Name & ""
    db.Execute (aSQL), dbFailOnError

   'delete import errors table
   DoCmd.DeleteObject acTable,  tbl.Name
    Else
   
    End If
    Next tbl

End Sub

this will copy all the importerrors to tblErrors


0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 3

Author Comment

by:DanR
ID: 12002313
Thanks for the responses so far, but I don't think I was clear on the problem.  

I am only slightly interested in the content  of the Import Errors tables; it will be basically the same every import, and the errors are generally not critical.  Still, it would be nice to have the errors in a useful form.

But the real problem is that every time I import, I get 300 more useless tables.  What I want is to have the errors go only into a single table, not generate a separate table for each text file.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 12002587
(AFAIK), you cannot avoid this, if there is an error Access generates the table for errors.

Here are the most typical reasons for encountering errors:

In a field, there is data that can't be stored in the data type that Microsoft Access has assigned to that field. For example, you might have accidentally included a text value in a field that should only contain numbers or dates. Or perhaps a row in your text file or spreadsheet contains summary information or extraneous characters.
If you think Microsoft Access assigned the correct data type for this field, edit your text file or spreadsheet to correct errors, and then import again. Otherwise, import again and specify the appropriate data type.

Microsoft Access has assigned an incorrect data type to a field. Microsoft Access assigns the data type for each field based on the data in the first row it imports. For example, if a field that contains mostly text values has a number in the first row, then, in an Access database, Access assigns the Number data type. In an Access project, Access assigns a data type for numeric data. In both cases, Access cannot import the rest of the records.
Import again and specify the appropriate data type when importing.

One or more rows in the text file or spreadsheet contain more fields than the first row. For example, the second row in a file might have an extra field delimiter character that is followed by a value that Microsoft Access can't fit in the new table.
Edit your text file or spreadsheet so that each row has the same number of fields, and import again.

The date fields that are imported from a Microsoft Excel spreadsheet are off by four years. Microsoft Excel for Windows uses the 1900 Date System, in which serial numbers range from 1 to 65,380, which correspond to the dates January 1, 1900 through December 31, 2078. However, Microsoft Excel for the Macintosh uses the 1904 Date System, in which serial numbers range from 0 to 63,918, which correspond to the dates January 1, 1904 through December 31, 2078.


0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 12002605
What you are asking for is not possible.  Microsoft dicates that all import errors be designated in a signle format with a prefix of 'ImportErrors' and a suffix of the file name you are importing, and developers can't change that.

The best you can hope for, if your problem is defined as having 300+ useless tables, would be Capricorn's code, which at least automates the moving of all records from the 300+ tables into one table, then delete all 'ImportErrors' tables, so you in effect have one table with all errors.  From there, you can build a form on top of it for easier viewing.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12002628
http://office.microsoft.com/en-gb/assistance/HP051885461033.aspx

The second link (I get errors when I import a spreadsheet or text file.).
0
 
LVL 3

Author Comment

by:DanR
ID: 12006529
Thanks to all for the input.  I must accept that those tables will be created.  I know what causes all the errors, and I am intrigued by jimhorn's suggestion of a pre-import and cleanup (though I won't have time to look at that for a while), so I gave him half the points.

capricorn1 gave me a way to create a single table with all the errors, which will be useful in the future, so half the points to her/hem.

The rest of you have my thanks.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

713 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