text driver problem 1

[taupirho]

Im using XL97 vba on NT 4 to implement a simple database based on text files. Using OLEDB text driver and everything is OK except when I try using the IN SQL keyword with certain filenames. For example
ado_data.open "select stock_name from stock.csv where _
stock_id in (select id from stock_lookup.csv)"

works fine, however if stock_lookup.csv is changed to
stock lookup.csv (ie with a space character in the middle)
I get an error message. Enclosing the filename in square brackets, single or double quotes does not work either. Same problem occurs if instead of a space the character is a tilda, dash and many others special characters. I know I can get around this by re-writing as a join or re-naming the file on the fly but does anyone know of if there's some kind of way of telling ADO to allow special characters in filenames so that I can use original code.

 

[Zathras]

Try using quotes around the file name:

"select stock_name from ""stock lookup.csv"" where..."

If that doesn't work, then try using the MS-DOS name for the file. Typically for "stock lookup.csv" the MS-DOS file name would be STOCKL~1.CSV. You can get the MS-DOS name from the file properties.

 

[taupirho]

zathras, did you not read my original post?

I said I already tried using single and double quotes around the filename. Also your suggestion of the MSDOS file wouldn't work because as I say in my original message -again - special characters like tilda don't work either.

 

[ETID]

Hmmmmm

I say this boy's SOL on the rudeness factor....

 

[taupirho]

What's rude about asking people to read a post before replying to it? Saves everyone a lot of time, poster AND postee (if there is such a word). Anyway, original question still stands.

 

[xlbo]

Well stick to SQL naming conventions then - spaces and special characters are hated by SQL in equal measure - use _ or suffer the consequences ;-) and BTW it isn't rude to ask someone to read a post before replying but the way you wrote the response could very easily be interpreted as brusque or dismissive - suggest you have a look at people's profiles before dismissing their answers. Zathras has helped an inordinate amount of people on this site and the one or 2 times he may misread a question, I would've thought would pale into insignificance. Remember
1: You are not the only person here asking for help
2: No-one here gets paid to give any help
3: Politeness doesn't cost anything


Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[taupirho]

OK xlbo, I'll just go and tell all my customers not to bother creating files with spaces, dashes, tildas or any other special characters then. I'm sure when I explain it's because SQL hates them they'll be very understanding and let me off with it.

 

[xlbo]

Hmmm - someone once said that sarcasm is the lowest form of wit - I say that's only the case when used by the low - you need to sort out your arrogance problem. I'll be ignoring your posts from now on then
Oh - and another BTW - I see you've posted a fair number of questions here on Tek Tips and not once awarded a star for an answer (it is the generally accepted way of saying thanks round here). Maybe that and your general rudeness and arrogance are why no-one else seems to want to help you. I think you answered your own question in the original post anyway - RENAME THE FILES - and don't blame me for the way SQL operates

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[ETID]

I'm with you XLBO....

 

[Zathras]

Ok, just for the record:

1. I did read the post. tomreid stated that he tried ...square brackets, single or double quotes... in response to which I suggested trying DOUBLE DOUBLE quotes (because of the way Excel handles double quotes in literal strings).

2. I am not familiar with using text files with an OLEDB text driver, and the only reason I posted a response is that a day had passed and no one else has responded. (I think now I know the reason why.)

3. It's spelled t-i-l-d-e.