ms access - Handle TransferText Errors

Question

I have an Access-2007 application, I use Visual Basic to export/import tables from text file.

DoCmd.TransferText acExportDelim, "MySpec", "Table1", "c:able1.txt", True

DoCmd.TransferText acImportDelim, "MypSpec", "Table1", "c:able1.txt", True

I want to trap all errors that this method can raise. I want to have the list of this method's error numbers, I searched on MSDN but I didn't find any thing.

Also I want to prevent access from creating ImportErrors table if the import fails.

Any idea ?

Answer 1

You can use SQL if you want to pre-test the data.

Use a schema.ini file and a straight import to MS Access. A schema.ini file is comparable to a specification.

[imp.txt]
ColNameHeader=False
Format=FixedLength
Col1=ID  Char Width 8
Col2=AName  Char Width 10
Col3=Mark Char Width 2

SQL

SELECT * INTO Imp FROM [Text;DATABASE=Z:docs].[imp.txt]

In VBA:

Dim db As Database
Set db = CurrentDB

sSQL = "SELECT * INTO Imp FROM [Text;DATABASE=Z:docs].[imp.txt]"
db.Execute sSQL, dbFailOnError

References:
Errors Collection (DAO)
Error Object (DAO)
Schema.ini File (Text File Driver)