capturing import error tables 1

[Loomah]

hi
i'm creating a tool that imports data from nearly 100 'input' spreadsheets accross a network. the import process works fine but there are occasionally import error tables created.

is there a way of checking at the end of each set of imports whether a import error table or tables has been created?

the data is imported through VBA, in order to enabling me to loop through the the 33 different network locations, and using DoCmd.TransferSpreadsheet etc

it's usually a case of type conversion errors. the spreadsheets have been set up with data validation designed, in theory, to eliminate data entry errors as far as is reasonable. having users input directly to access isn't (to the best of my knowledge, and time is against me exploring this) an option due to differences in available software in different network domains.

this will form part of an exceptions reporting process to inform tems that work will not be recorded in performance stats if it can't be used!

i've tried to keep this simple, but.....
thanks

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[PHV]

A starting point:

Dim td As DAO.TableDef
For Each td In CurrentDb.TableDefs
  If td.Name Like "*error*" Then
    Debug.Print td.Name
  End If
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Loomah]

thanks again ph!
now, is there a simple way to delete the table after i've extracted the errors so that i don't create a situation where i end up with multiple error tables over a period of time?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[PHV]

Something like this ?

Dim td As DAO.TableDef
DoCmd.SetWarnings False
For Each td In CurrentDb.TableDefs
  If td.Name Like "*error*" Then
    DoCmd.RunSQL "DROP TABLE [" & td.Name & "]"
  End If
Next
DoCmd.SetWarnings True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Loomah]

ah, drop table!
i get to learn about 2 things today, another little bit of (access) vba and a little sql - both of these will be immensely valuable to me when trying to manage the housekeeping in this db!

now i get to disappear back into the void that is my current employment only occasionally to resurface in tt!!

thanks again

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​