none
Import error in one table RRS feed

  • Question

  • Hi,

    I have an utility wherein I import text file (fixed Width) into a table of MS Access. While Importing the one Import error table is created. I have to delete this table every time I import.

    I want to develop a system wherein error records are appended in a table instead of creation of new table for each import.

    Grateful if I get the solution.

    Thanks

    Manoj S

    Thursday, May 23, 2013 3:26 AM

All replies

  • Hi Manoj
    What are the errors in the import error table? You have to fix your import routine (for example by using an ImportExport Specification) and then it should work.
    To create an ImportExport Specification start the Import wizard and then when you have defined the fields switch to Advanced window and save the specification under a name of your choice.
    If you later import it by code specify this ImportExport Specification and your import will do what you expect.
    HTH
    Henry

    Thursday, May 23, 2013 7:46 AM
  • Hi Henry,

    Thanks for your suggestion. In fact my text file is a general report file which contains lots of blank rows or characters like ============== to create the line. These are invalid records hence are not imported in the table but an record is created in the import error table (created a fresh one for each import) for the error. I want to avoid the creation of fresh error table.

    Is it possible, if i can validate these record before importing So that only valid records are imported in the table. My understanding is that we can skip the column not row.

    Grateful if I get the method / sample code to validate the record before importing.

    Regards

    Manoj

    Thursday, May 23, 2013 5:38 PM
  • Henry,

    many thnaks. I am not very good in VBA, however one way of doing in VBA could be to import all records in vba recordset (array). After that insert into the table based upon the criteria.

    Similarly I have tried following in access

    1. Import all records in a temp table where in all fields are text type or variant to avoid any error while importing.

    2. Append the desired table from temp table on the basis of the criteria

    3. Delete all records from temp table

    In above case I have to prepar a query to change the type of the field from text to date or text to number or text to currency. And also I don not have any check on the invalid records

    I understand VBA would be a better option wherein I can validate each record and append the same in related table (desired table / error table).

    grateful if a sample VBA code of importing the data in a recordset (array) from text file is provided to me.

    Regards

    Manoj

    Friday, May 24, 2013 5:52 PM