Change default delimiter in Access [Resolved via impossibility]

[salvelinus]

Is it possible to change the default delimiter from a comma to a pipe in Access? I want to create tables on the fly based on text files with different numbers and names of fields, but they're delimited by the pipe symbol. I don't want to create these by hand and then create import specifications for each one. Thanks.

[RobDog888]

One way to do it would be to create a macro that would do a 'Transfer Text'
action with 'Import Delimited' as the transfer type. Also, create and save
a 'Specification' for importing the file (this is the same steps you go through
when setting up a file import. You get to specify the delimiter, etc.). Then
when you run the macro it can import your files using your pre-choosen
delimiter.

HTH

[salvelinus]

How would I create a specification that isn't linked to a pre-existing table? I tried creating a macro, but it wanted the specification name, the table name to import to (there isn't one yet - it gets created, and it's different for each text file), and it wanted the name of the text file to import from (this is user chosen each time). Maybe I don't understand macros that well, rarely if ever use them. This would work - if commas were the delimiter in the text file:

'this will create a table named tblAnyNameIGiveIt from whatever comma delimited
'text file the user chooses
DoCmd.TransferText acImportDelim, , "tblAnyNameIGiveIt", "c:\UserSelected.txt", True

but I can't get the delimiter argument after acImportDelim to accept the pipe symbol.

[RobDog888]

Yes, since you dont have the table ahead of time you can not create a specification
either. Looks like you may just need to create a loop reading in the file and
adding records to the table all in code.

[salvelinus]

Well, thanks anyway. I don't know why MS doesn't give this option. It should be trivial to allow any character as a delimiter in TransferText.
At least Split() is available.