TransferText

[PearlGI]

If I import a tab delimited file into Access by hand it auto-detects it as tab delimited.

Why then, if I try to replicate this using VBA does it exist on thinking it's comma delimited

The line I'm using is this:

DoCmd.TransferText acImportDelim, , "Import", strPath & strFile, True


Is there any way of informing Access that the file is tab delimited?

Yes, I know usually you would use an import specification. But in this situation the structure of the import file is unknown and hence I can't use a spec.

Any ideas?

[[nateobot]]

I know when i had to work with a lot of text files before from various companies I would open them up get the headers and order and save those into code.

Then i would read line by line the text of the file and input those values into a table.

You could try something like this if you so choose to. Read in the first line as the table fields. Create a table based on those. Then read line by line through the file and insert the values.

Probably not the best way of doing it but it should work pretty well for you and that way you don't have to step through the import dialog everytime.

-nate

[PearlGI]

I've found the answer, well a reason why it's happening.

It appears that when Access is originally installed and the Text Data Source database driver is set-up. The default settings are

Format = CSVDelimited

This setting is stored in the Windows Registry and not in Access.

If anyone wants the full answer type ISAM into Access help and then find Initalizing the Text Data Source Driver.


Looks like I'll have to write my own import routine as Nateobot suggested as my IT department don't see the need to change all the window registrys involved

[[nateobot]]

Well if it is stored in the registry you could always change that key when your app is loaded and then change it back when you are done with your import.

I don't really condone messing with the registry however it is an option.

[PearlGI]

Yes, it is an option - but messy!

If it were to be done via code, the Access would have to be restarted for the registry changes to take affect.

[[nateobot]]

Yeah that was my other thought after typing that was if Access needed to be reloaded. Thanks for reading my mind.