Extension mistyped causes Read Only error

[Gasman]

Hi all,
In trying to assist another member in this thread https://www.access-programmers.co.uk/forums/threads/error-msg-resolution.327161/#post-1871024 I inadvertently solved the problem, by mentioning that the extension of the output file did not appear to be correct.

However I was curious as to why an extension would make all that difference, after all, one should be able to to use whatever you want?

Anyway, I duplicated the code with

Sub TestText()

DoCmd.TransferText acExportDelim, , "TestTransactions", "F:\Temp\TestText.cvs"

End Sub

and I too get error 3027 File is Read Only.
Change cvs to csv and it works.

Anyone have any thoughts?

 

[arnelgp]

what is cvs extension:
CVS File Extension - What is a .cvs file and how do I open it? (fileinfo.com)

 

[Gasman]

Well it was just a typo, but what if I wanted to use vsc so that it did not come up in an excel file open dialog, and then associated that extension with another program I had written?

Just curious as to how a file extension can generate that message. Perhaps if no program associated with that extension?

 

[CJ_London]

Perhaps if no program associated with that extension?

that would be my guess - the example in transfertext documentation uses .doc

 

[ebs17]

The registry contains file extensions that can be read by Jet by default. There are: txt, csv, tab, asc
TransferText in all likelihood uses Jet internally to read the external data sources.

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\ISAM Formats\Text => ExportFilter

Sure you can add your own creations, but you have to do it throughout. Renaming the file will be easier on a regular basis.

 

[Gasman]

The registry contains file extensions that can be read by Jet by default. There are: txt, csv, tab, asc
TransferText in all likelihood uses Jet internally to read the external data sources.

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\ISAM Formats\Text => ExportFilter

Sure you can add your own creations, but you have to do it throughout. Renaming the file will be easier on a regular basis.

I thought you had it there to be honest.
When I looked at that reg key, it had what is in the pic, but mius the vsc of course which I then added.
I rebooted the computer and tried again, but still get the error.

Text Files (*.txt;*.csv;*.tab;*.asc;*.vsc)

 

[ebs17]

One registry key was only mentioned as an example. One could also think of ACE instead of Jet, other Access versions, etc.

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\ISAM Formats\Text

I see an in-depth study of the topic as academic. A file extension can be renamed, which is particularly uncritical for a text file.

 

[Pat Hartman]

Just curious as to how a file extension can generate that message.

It has to do with file extensions that Windows considers to be dangerous. I think Access takes the other approach and only accepts file extensions known to be safe. I've run into this before. Quite a few years ago ~2000, when Windows security was changed regarding this, I got a call from a panic stricken client that he could no longer import files create by his scanner. His scanner created files with a .dat extension and he didn't have any way to control that. Up until the day he called me, Access had been quite OK treating these as csv files which is what they were.

I had to make an emergency fix to the software to rename the file from .dat to .csv prior to import. Luckily, I just guessed this to be the problem and when he renamed the file manually, it imported just fine. I later found the documentation regarding the change to Windows and by default, Access.

 

[The_Doc_Man]

If anyone cares to find the list of known file extensions, it is called "associations" in older versions of Windows. In Win10, you use Start >> Settings and then in the search box, type in "Choose a default app for each type of file" to see the list, which will be alphabetic by file type.

In Gasman's referenced thread, the problem started by using a DoCmd.TransferText for which a .CSV format was chosen but not a .CSV file type. While we know and love Access for its many little nice features, in this case there is a foible. It would appear that if you try to name a file type that doesn't match the chosen output format, it ain't gonna work. BUT it might not be Access doing it... it COULD be the file system tripping an error but the .TransferText doesn't know how to handle the error so decides to not allow the file to be modified. It's a safety factor because it doesn't know that file type.

I think if you were creating a file via the OPEN FILE #1 FOR OUTPUT AS "C:\Users\Foobar.POO" you might be able to get away with it because the Open File mechanism is fairly generic. Remember, doing a RENAME of a file to an arbitrary type (naming it away from a known type) will frequently get you a warning that doing so could make the file unusable. Which means the file system is looking at what you do. The DoCmd.TransferText appears to be less forgiving than Open File.