Fix transfer text automation

[Ray Mead]

Help! Below is the code to automatically import all text files. This code
works great if the files are in the same layout, but causes data shifting if
the fields change. How do I modify the code to change it to update if the
file layout is different?

If varX = "text" Then

varH = Me.cboImportFileSelect
varI = Me.cboImportFileSelect.Column(1)
varJ = Me.cboImportFileSelect.Column(2)
varK = Me.cboImportFileSelect.Column(3)
varM = Me.List11

DoCmd.TransferText acImportDelim, varM, varH, varJ & varK, True, ""

Call AddSource(varH, "Source")
Call AddFileName(varH, "SourceDatabase")
Call AddCounter(varH, "ConversionID")

MsgBox "update complete"
End If

 

[Alex Dybenko]

Hi,
i think you have to make a new import specification, and for other layouts
use it

 

[Ray Mead]

That is an option. But I was hoping to modify the existing code. I'm sure
there is somekind of refresh or requery to use. If I close the form and
reopen it, the program will import the file with the new specification.

I choose the import specification from a list, me.list11. Is there away to
clear the import specification at the very end so when it loops, it starts
out clean?

 

[Ray Mead]

Is there a way to reset the import wizard query as listed in the me.list11?
I think the field names are somehow cached. If we can clear those, then I
should be home free....

I think the specs list is maintained in table MSysIMEXSpecs

 

[Alex Dybenko]

Not quite understand what you need, but yes, you can modify import
specifications table MSysIMEXSpecs, you have to open a query based on this
table in order to make it editable

 

[Ray Mead]

Actually, I think I need to end, cancel, or close (i'm not sure of the
terminology) the DoCmd.TransferText line. At anyrate, the import wizard
needs to be closed, ie the equivalent of clicking Finish button in the Import
Text Wizard.
Do you know how I could do that?