Import Error Table

[asingh]

Hi,

I transfer data to my access data base..using Transfer Text method, via the macros option. Since I am transfering from delimited text files, I get Import Errors. It shows up as an import errors table in the tables view. Is there any way, that this table is never ever gets generated. It just clutters up the data base....!

[The work around I have as of now...is to create a delete query, specifying the import error table to be deleted, but I feel this is not robust].

A possible solution could be, to write a VBA module, which would search all the table names, and if the table name contains "imprort errors", then I could DROP [delete]...that table. How would I sequentially "read" all table names on the data base using VBA.....?

thanks and regards,


asingh

[stanl]

A possible solution could be, to write a VBA module, which would search all the table names, and if the table name contains "imprort errors", then I could DROP [delete]...that table. How would I sequentially "read" all table names on the data base using VBA.....?



I would think you would be more concerned with eliminating the errors in the first place. Since you are using TransferText you could search for vba to list tables using DAO. However, below is a simple ADO function which accepts a connection string and can be modified to fit. Stan



Private Function Tables(byval connstring)
Dim adox, i, strTables
Set adox = CreateObject("ADOX.Catalog")
adox.ActiveConnection = connstring
for i = 0 to adox.tables.count - 1
if UCase( adox.tables(i).type ) = "TABLE" then
strTables = strTables & adox.tables(i).name & vbCrLf
end if
next
Set adox = nothing
Tables = split( strTables, vbCrLf )
End Function

[asingh]

I cannot eliminate the errors..because the data I receive in the flat text files..is from a mainframe which was designed back in 1980....! So the structures are fixed there..and internal contents of the data can be changed by the mainframe programmers [as per the production data for around 1,000,0000 people]..which I can not control.

I seem to understand the ADO module you sent...All I want is that the Import Error table not appear in my table list in MS -- Access....I know each time I use these flat files I will get errors....! :).......!..But I do not want those Import Error tables to show up...!



regards,

asingh

[asingh]

sorry i meant 1,000,000 people.....................!

[stanl]

if UCase( adox.tables(i).type ) = "TABLE" and Instr(1,UCase(adox.tables(i).name),"ERROR",1)>0 then


should get what you want

[Norie]

asingh

Why can't you eliminate the errors?

I don't think stanl is suggesting you do anything to the original data.

Could you not alter the way you are doing the import?

Perhaps you could do something with the import specification?

[asingh]

Hi have tried...that...changing the import specification..But what happens.at times..I have a column..which I have defined as "date/time", but the flat file will contain say a "number" at times..so and import error occurs...!
I will give the above mentioned code a try, and let you all know...!

thanks,
asingh

[Norie]

asingh

So why not import that field as text and then convert it later?