Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Posts
    15

    Unanswered: Preventing ImportError tables?

    Is there a way to import delimited text files (with errors) without Access generating error tables?

    I'm thinking there must be a VBA argument to switch off error tables but am having trouble finding it...

  2. #2
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    For what do you want that? Do you need the errors? Why?

  3. #3
    Join Date
    Jan 2005
    Posts
    15
    I'm bringing in a slew of files containing shipping data. There's a [Billed Weight] field that either returns a number or "LTR" for letter. In the import spec, I've defined the field as Long Integer so the "LTR"'s get wiped. "LTR" is very rare, maybe 3 or 4 show up per file. I go in with an update query and replace the nulls with 0.

    As I'm bringing in several files at a time, there are several importError tables created as a result of each import. I'd like to prevent the importError tables from occurring in the first place.

  4. #4
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    Try to do this:

    set the field to text, and not to long integer.
    Import the data you want.
    With the LTR, update them to a number.
    now set the field long integer.

    this is valid if you're going to do once, of course that if you are going to import data like this too many times...

  5. #5
    Join Date
    Jan 2005
    Posts
    15
    The import takes place regularly, so my rationale was to omit the error reporting and run one query to update the field. The other method would involve two queries - one to change "LTR" to "0", one to change the designation of the field to long integer.

    If it's impossible to skip the creation of the import error tables, I will write these two queries.

    Thank you for your help!

  6. #6
    Join Date
    Feb 2005
    Location
    Peoria, IL, USA
    Posts
    9
    I don't know of a way to get rid of the import error table creation, but I've faced a similar problem. Keep your primary data table as-is.

    Set up a "slush" import table with the billed weight field as text. Create an append query to update your primary data table, converting the text in the "slush" billed weight to number for the primary data table.

    Then write a procedure or macro to
    1. Import the file(s) to the "slush" table
    2. Run the append query
    3. Delete the records from the "slush" table

    Good luck,

    Bonnie

Posting Permissions

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