DoCmd.TransferText...trap for import error table via VBA 1

[NorthNone]

VBA brings in text via the DoCmd.TransferText command successfully but sometimes the text file has an error in it and an import errors table is created.
Is there a way in VBA to detect this and notify the user? Right now we have to remind them to look for import error tables when the process is done. Not too swift...
Many thanks for assistance!



---------------------------------------
The customer may not always be right, but the customer is always the customer.

 

[Ed2020]

Not that I'm aware of.

I tend to validate the text file, in code, prior to importing then link the file, append the data and drop the link.

Ed Metcalfe.

Please do not feed the trolls.....

 

[NorthNone]

Never done much with text files except import them. How do you do your validation? TIA, JSouth

---------------------------------------
The customer may not always be right, but the customer is always the customer.

 

[psemianonymous]

Import the text into a (more forgiving) temp table, then run queries checking for 'bad lines', which you can then notify the user about. Anything more complicated than a simple table import (such as data validation) should not be handled by TransferText--it's not good at handling 'bad' data.

 

[Ed2020]

Importing unvalidated data into a temp table then deleting it causes database bloat.

No more difficult to squirrox it out before importation.

Please do not feed the trolls.....

 

[psemianonymous]

If you're just dumping every invalid entry, then yes, a simple TransferText does the job, as Ed pointed out.

If you want to do something with those invalid entries, like count them, or massage certain of them into useable records, use a temp table.


If you just want to see if there were import errors, add some VBA code to check to see if the table "yourtablename" & "_ImportErrors" exists. Of course you would have to include a routine that deleted any _ImportErrors table before you started the import.

 

[NorthNone]

foolio12: it would be a boon if you would post an example of how to do the following:
"If you just want to see if there were import errors, add some VBA code to check to see if the table "yourtablename" & "_ImportErrors" exists."
I would be MOST appreciative...

---------------------------------------
The customer may not always be right, but the customer is always the customer.

 

[psemianonymous]

[tt]public function tableexists(strname as string) as boolean
on error goto Sub_Error
dim str as string

str = Currentdb.Tabledefs(strname).Connect
tableexists = true
exit function

Sub_Error:
tableexists = false
exit function
end function[/tt]

 

[NorthNone]

Thanks and a star!

---------------------------------------
The customer may not always be right, but the customer is always the customer.