How to set a delimiter when the data source is a text file

[felipe]

Hi all,
I am making a query from a text file using the following statement:
SELECT * FROM
[Text;FMT=Delimited;HDR=Yes;DATABASE=C:\DirectoryPa th\;].[FileName#txt];

I want to set the delimiter of the file, for instance a semicolon, and I do not know how to do it. I have tried with the following statements, but without good results.

1. SELECT * FROM
[Text;FMT=Delimited(;HDR=Yes;DATABASE=C:\DirectoryPa th\;].[FileName#txt];

2. SELECT * FROM[Text;FMT=Delimited";";HDR=Yes;DATABASE=C:\DirectoryPa th\;].[FileName#txt];

Any advice will be very helpful.

Thanks in advance,

Felipe.

 

[jdraw]

Why are you trying to query the text file directly?
Why not use the import wizard and import your data to a temporary table, then use a standard Access query to get the data you need?

Search Access help for Import Wizard

There is some info here http://www.databasedev.co.uk/import-data-with-vba.html that may help.

 

[Galaxiom]

The delimiter cannot be set in the connection properties.
Use a schema.ini file in the same folder as the text file.

Put this in the schema.ini file

[filename.txt]
ColNameHeader=True
Format=Delimited(;)

The FROM statement can be cut back to:
[Text;DATABASE=C:\path].[filename.txt]