error trapping import error

[Laoballer]

I'm using docmd.transfertext to import tab delimited text files into
an access 2002 table. Some times the file doesn't get imported
completely because some fields don't pass validation rules for the
table. What I would like to be able to do is trap this error so I can
display a message box with the file name and some message for the
user. Is this possible?

Thanks,

 

[Allen Browne]

There are several ways to address this.

Closing the stable door after the horse has already bolted, you may discover
a new table with a name such as Import Errors or Paste Errors. Assuming you
already deleted any such tables before hand, you could look for them and
show them to the user if the exist after the import.

A better approach would be to test the data before you import it into the
real tables. Typically this means you import it into a temporary table. This
table has mostly Text fields, and no validation rules, no required fields,
no foreign keys, etc., so the data won't fail. You can then run a series of
tests to ensure the data is the correct type, required fields have data in
all rows, foreign key fields can be matched to the lookup table, and so on.
Any records that don't pass, you show in a form where the user can correct
them, and then run the validation again. Only after all records pass do you
try to import them into the real tables.

Ultimately, the import can be executed in a transaction, so you get an
all-or nothing approach (rolling back if an unforseen error occurs.) For a
discussion and example of executing a transaction, see:
Archive: Move records to another table - copy + delete in a transaction
at:
http://allenbrowne.com/ser-37.html

 

[Laoballer]

Allen,
Thanks for the responses, the data that I'm checking and will archive
has a lot of records. I'm following your guide for transaction. If I
want to archive all the records, do I have to specify every single
field in my table? which has over 10 fields. That's a lot of fields
and would get messy, is there an alternative to doing a transaction
for all data in a table?

Thanks,

 

[Allen Browne]

You can use the wildcard * in the SQL statements.

But it's easy enough to mock up an Append query in the query design window,
switch it to SQL View (View menu), and copy the SQL statement into your
code. To handle any embedded quotes and add the line breaks to long SQL
statements, what I do is use this utility:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html
Essentially, you paste the SQL statement into a form, click a button, and it
places the SQL string into the clipboard ready for you to paste into your
code.

 

[Laoballer]

You can use the wildcard * in the SQL statements.

But it's easy enough to mock up an Append query in the query design window,
switch it to SQL View (View menu), and copy the SQL statement into your
code. To handle any embedded quotes and add the line breaks to long SQL
statements, what I do is use this utility:
    Copy SQL statement from query to VBA
at:
   http://allenbrowne.com/ser-71.html
Essentially, you paste the SQL statement into a form, click a button, andit
places the SQL string into the clipboard ready for you to paste into your
code.

I was able to do a select * to append all the records. However I'm
running into a case where one of my field is empty, and that field
happens to be part of a primary key set for the table. When I try to
append the dataset it gives an error 3058 and doesn't append any of
the records. Is there a way I can append the dataset anyways and skip
the record that has the missing field?

Thanks,

 

[John W. Vinson]

I was able to do a select * to append all the records. However I'm
running into a case where one of my field is empty, and that field
happens to be part of a primary key set for the table. When I try to
append the dataset it gives an error 3058 and doesn't append any of
the records. Is there a way I can append the dataset anyways and skip
the record that has the missing field?

Can you use a criterion of IS NOT NULL on the offending field?

 

[ROMANO LAPINCE]

Oui mais aussi

 

[Laoballer]

Can you use a criterion of IS NOT NULL on the offending field?

ideally I would like to include the record even if it's missing a
field that's a primary key because I will do some statistically work
that would count the number of records and I don't want to lose any of
the data. Right now I do a query to see if any records are returned
that has the offending field(s) that are null, and if there are it
gives a warning message then skips the entire import process and moves
on to the next dataset. Does this sound like a reasonable thing to
do?

Thanks,

thanks,

 

[Allen Browne]

It should not be too hard to list the fields of the table rather than use
the wildcard.
Loop through the Fields of the TableDef (or QueryDef), using the Name of
each one to build up the string.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Can you use a criterion of IS NOT NULL on the offending field?

ideally I would like to include the record even if it's missing a
field that's a primary key because I will do some statistically work
that would count the number of records and I don't want to lose any of
the data. Right now I do a query to see if any records are returned
that has the offending field(s) that are null, and if there are it
gives a warning message then skips the entire import process and moves
on to the next dataset. Does this sound like a reasonable thing to
do?

Thanks,

thanks,