Results 1 to 5 of 5
  1. #1
    Join Date
    May 2006
    Posts
    38

    Unanswered: Reporting Import Errors

    I am using the following code to import a text file and and then append to an exiting table. Sometimes, due to formatting errors, incorrectly formatted imported data gets appended without any alert to user. Is there a way I can add the error routine which may stop the import and inform user of the error. Better still, if I could also show the import error table created as part of this proces?

    DoCmd.TransferText acImportDelim, "Areas Import Spec", "tblTempDeleteOldAreasR", f.Path

    'Update new data to the main table
    dbs.Execute "qryUpdateMasterAreasR"
    MsgBox "Post Areas data has been imported and appended to the Areas table."
    dbs.Close

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Well, unless you already have some error handling, like
    Code:
    On error resume next
    then I would bet you're not actually getting an error from the import, which means there's no way to handle the error, as, at least to access, the error doesn't exist. If you do have error handling already, then use the debugger to figure out what the error number is and use some error handling procedure. Otherwise, you'll have to define a bit clearer what
    Quote Originally Posted by khwaja
    incorrectly formatted imported data
    is.
    Me.Geek = True

  3. #3
    Join Date
    May 2006
    Posts
    38
    Thinks Nick. When importing data manually, you do get an alert after the import that some errors have been encoubnteerd and an error table created usually helps you to figure out what the error was. In case you are using the code to import, there is absolutely no indication whether errors have been encountered. An error table is created in the background. So I would like my code to recogise when errors has been created by way of opening error table and would like user to see the table itself. I hope you will appreciate it is rather different type of error trapping in this case.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you could do what you want by browsing the table defs but unless your users are 50x smarter than my users, they wont get far staring at the error table... and meanwhile your data is corrupted. here's one possible DAO-HOW to find your error tables:
    dim tdef as DAO.tabledef
    for each tdef in currentdb.tabledefs
    debug.print tdef.name
    next

    in real life you know the format of the name of the import error table so instead of dropping the name to debug you do something useful if you find a matching name

    my db-trolls are forever "improving" text exports and i hate trying to bring corrupt data back to life so i take an extremely cautious multi-step approach to this issue: parse the textfile looking at header line length, fieldnames, fieldname-sequence, trailing delimiter at the line-end/not(!), number of lines. if all that works out as expected, i import to a temporary table and check for import errors, rowcount vs textfile lines, missing keys, nulls where they shouldn't be, out-of-range dates, etc etc. only when temp seems correct then update the main tables.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    May 2006
    Posts
    38
    Absolutely, I agree with you. You have got by far the best approach. But dummies like me can never achieve the skill level to create code to deal with all situations as you have described. I wish I could..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •