unknow link specifications

[CJ_DB]

Folks, thank you in advance for your help.
I have a directory of tab delimited text data files I would like to link to
my database (programmiclly) and do some processing(programmiclly). Each text
file has a header row of field names and then the data. I have no way of
wading thru the hundreds of files and using the link wizard to manually
create a link/import specification. I need all the data types to be text. I
have used Text;;FMT=Delimited;HDR=Yes;IMEX=1;CharacterSet=437;DATABASE=xxx.md
which gives me only 1 unseparted field of data. Same for DoCmd.TransferText
acLinkDelim,xxx,xxx,xxx
The big problem is creating the link specification on the fly. Thanks for
any help.

 

[Douglas J. Steele]

Do you need all of the files linked at once?

If not, why not create a single link specification. You could then rename
each file to the appropriate name, link it, process it, unlink it, then
rename the file back.

 

[CJ_DB]

Doug, Thanks for replying.
I do not need them all at one but the problem is the single link
specification. Each file has a unique set of data fields that I exam and
process individually. I do not know the number of fields nor the names until
I do the processing but I can not process until I create the linked/imported
table.
i.e table1---- fldAN, KUP, SAP, JJJ
table2 --- xxx, yyy, sup, sap
etc.

 

[Douglas J. Steele]

You may have to read the files in using the Line Input # statement and parse
them yourself in VBA.

 

[CJ_DB]

Doug, the files are all tab separated TSV. I changed one file to CSV and the
statement: Text;;FMT=Delimited;HDR=Yes;IMEX=1;CharacterSet=437;DATABASE=xxx.md
works. Do you know of a way to have the format FMT=TabDelimited? I tried
that FMT=TabDelimited ; FMT=Delimited(" & vbTab & ") and
FMT=Delimited("<delimiter>") as suggestion from a web search but they had no
effect unlike the CSV which works completely. I could write a parser but it
seems like there should be an easy way to create a link/import specification

 

[Douglas J. Steele]

See whether Format=TabDelimited works

You might also check http://msdn2.microsoft.com/library/ms709353.aspx

 

[CJ_DB]

The article and others say that Format=TabDelimited should work but the
link still comes back to only 1 field. I have also used the
Format=Delimited(vbTab) and chr(9). These do not work thus far. Any other
suggestions.

 

[Douglas J. Steele]

You seem to have already rejected my other suggestion, which was to use VBA
to import the data.

 

[CJ_DB]

Doug,

Thanks for your help. I created a parser for the 1 field import. I still
wonder why the Format=TabDelimited does not work in a connect string as does
the default Format=Delimited(which assumes comma). Even the article you
suggested say that it should work but alas no go.

Thanks again.