"Select " in filename on DoCmd.TransferText

[bschulze]

I have code to import a .txt file into a table. I'm running into an issue where the filename begins with "Select " (note the space after Select). If there is no space after "Select" the code runs properly. This is clearly an issue with how the DoCmd.TransferText is processed and not the code.

 DoCmd.TransferText acImportFixed, msSpec, msImportTable, msFileName, False

Filename:
"Select Test.txt"

Error:
Syntax error (missing operator) in query expression 'Test#.txt'.
(3075)


I can check for "Select " in the filename and change it prior to import, but I'm wondering if there is a solution with double quotes or single quotes to get the function to read the inputs correctly. I've tried single quotes and a couple variations of double and single quotes without luck.

Anyone aware of a workaround other than checking the filename before import?

 

[Joe4]

I am pretty sure it is mistaking it for SQL Code, which often starts out "Select ...".
So I am going to say that you cannot have file names that start with the word "Select" followed by a space.
If there was no space there, or something else like an underscore, it works fine.

I am not sure where these files are coming from, but if you have any ability to tell them not to use spaces in the file names, it would solve the problem (best practice says not to do that anyway).

 

[bschulze]

Completely agree with you about "best practices". It's been discussed, but unfortunately it's out of my control. Also agree it's the VBA function processing. Thought I'd ask. If anyone else has suggestions I'm open, thanks Joe4.

 

[Norie]

Try enclosing the filename in square brackets.

 

[bschulze]

Thanks for the suggestion Norie, doesn't seem to be working.

Brackets outside quotes:
["C:\Temp\Select Test.txt"]

"Run-time error '2465':
Microsoft Access can't find the field '|1' referred to in your expression"


Brackets inside quotes:
"[C:\Temp\Select Test.txt]"

"You cannot import this file. (31519)"



Also tried with single quotes and without quotes are received the same errors.

 

[Norie]

Did you try putting the brackets around the filename, not the path and filename?

 

[bschulze]

Sorry, wasn't clear. I was testing without the variable, instead I used a text listed above. It didn't work with the variable either.

DoCmd.TransferText acImportFixed, msSpec, msImportTable, [msFileName], False

This code caused the following error.

"Syntax error in date in query expression 'Test#txt'. (3075)

 

[xenou]

It looks like you will have to avoid this unfortunately named file and give it a different name, at least temporarily. Its a weird bug I guess.

 

[Norie]

Still not quite what I mean.

Try this.

DoCmd.TransferText acImportFixed, msSpec, msImportTable, "[" & msFileName & "]", False

By the way, if you are using a Schema.ini file you can specify the filename in that.

 

[bschulze]

Still not quite what I mean.

Try this.

DoCmd.TransferText acImportFixed, msSpec, msImportTable, "[" & msFileName & "]", False

By the way, if you are using a Schema.ini file you can specify the filename in that.

Thanks Norie, this format would have been included in the earlier testing, but I've tested again in variable form with the same error.

"You cannot import this file. (31519)"


Unfortunately, it's a multiuser application and could be any file in the acceptable format. Many new files are generated every month, I'm guessing 2,000 last year would have been imported by the users. All on different servers with different file names. In this particular case, Schema.ini is not likely a workable option.

Thanks for the suggestions.