DoCmd.TransferText bug

[Seth Schrock]

Has anyone else run into a situation where DoCmd.TransferT ext doesn't work when the .CSV file name has extra periods in it (i.e. "1102141908270. FE010.CSV")? If I import it with that name, I get the following error: The Microsoft Access database engine could not find the object '1102142003010. FE010.CSV'. Make sure the object exists and that you spell its name and the path name correctly. If '1102142003010. FE010.CSV' is not a local object, check your network connection or contact the server administrator. If I remove the first period, then it imports fine.

These files are downloaded from the Internet, so I have no control over the file names. Is there anything that I can do besides renaming the file using code?

[twinnyfo]

Seth,

My solution would be to rename the files using code. I haven’t experienced that exact problem, but have experienced other file name issues. So, if I know there “might” be an issue, I find my list of files, cycle through all of them, searching for rascally naming issues, correct them, and then use the file.

It is an additional step that may only apply to some of my files, but in the end, it prevents other hang ups in the code.

Hope that hepps!

[NeoPa]

Hi Seth.

As Twinny says, renaming seems to be a workable approach.

Find the error number for that problem and handle it in code either by :

• Copy; Import; Delete.
• Rename; Import; Rename back.

[Seth Schrock]

I hadn't thought of the option to rename it back to the original name after I was done importing the file or of copying the file with a usable name and then deleting it. I think the later will be my choice as I can always fall back to the unchanged file if an error causes a failure before I can change it back.

[Brilstern]

Hi Seth,

Unfortunately this is a known bug in MS Access. Some of those little bugs are irritating, but at least this one has an easy work around.

[Seth Schrock]

One would think that it would be an easy fix for Microsoft as well.

[Brilstern]

"one would think"

#microshaftwind blows

[NeoPa]

Bear in mind that a Rename is far quicker to execute on any large file. Also, I would say just as easy to recover from if it errors. You just have to make sure you rename it back regardless.

OTOH both methods work, so go with what suits you best.

[Seth Schrock]

Usually, the files are very small (about 3KB), so I don't think that copying will be a problem, but that is a good consideration.