TransferText - Permission Denied

[Tim Marshall]

IN trying to export a Jet query (an actual saved Jet querydef which is
written against a pass through query - not that that should be a
problem) to text via either the File->Export... menu item or using
Transfertext, I get an error to the effect that permission is denied.

The VBA line shown below - I tried using the specification

DoCmd.TransferText acExportDelim, "QryOracleView Export Specification",
"qryOracleView", strSaveFile, -1

I get an error 3051 for the above line or when doing it via the menu is
as follows:

The Microsoft jet database engine cannot open the file
'qryOracleView-2006-08-24.txt'. It is already opened exclusively by
another user , or you need permission to view its data.

Yet, I have no problem doing the menu method and exporting to Excel.

What is going on?

Thanks in advance for any help on this...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Allen Browne]

IME, Windows often gets itself confused about file permissions, so the issue
might be that the file exists and it is not being overwritten. Try:
If Dir(strSaveFile) <vbNullString Then Kill strSaveFile

If that also fails there is an issue with the file.
If it solves the problem, you're home.
If it doesn't solve the problem, then the issue is elsewhere, such as with
the import/export spec.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumwrote in message
news:ec**********@coranto.ucs.mun.ca...

IN trying to export a Jet query (an actual saved Jet querydef which is
written against a pass through query - not that that should be a problem)
to text via either the File->Export... menu item or using Transfertext, I
get an error to the effect that permission is denied.

The VBA line shown below - I tried using the specification

DoCmd.TransferText acExportDelim, "QryOracleView Export Specification",
"qryOracleView", strSaveFile, -1

I get an error 3051 for the above line or when doing it via the menu is as
follows:

The Microsoft jet database engine cannot open the file
'qryOracleView-2006-08-24.txt'. It is already opened exclusively by
another user , or you need permission to view its data.

Yet, I have no problem doing the menu method and exporting to Excel.

What is going on?

Thanks in advance for any help on this...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Wayne Gillespie]

On Thu, 24 Aug 2006 17:15:22 -0230, Tim Marshall
<TI****@PurplePandaChasers.Moertheriumwrote:

>IN trying to export a Jet query (an actual saved Jet querydef which is
written against a pass through query - not that that should be a
problem) to text via either the File->Export... menu item or using
Transfertext, I get an error to the effect that permission is denied.

The VBA line shown below - I tried using the specification

DoCmd.TransferText acExportDelim, "QryOracleView Export Specification",
"qryOracleView", strSaveFile, -1

I get an error 3051 for the above line or when doing it via the menu is
as follows:

The Microsoft jet database engine cannot open the file
'qryOracleView-2006-08-24.txt'. It is already opened exclusively by
another user , or you need permission to view its data.

Yet, I have no problem doing the menu method and exporting to Excel.

What is going on?

Thanks in advance for any help on this...

I have seen this error on occasions if the filename exceeds 12 characters.
(Isn't it obvious from the error message :)

Try shortening the file name to OracleView rather than qryOracleView.

Wayne Gillespie
Gosford NSW Australia

[Tim Marshall]

Tim Marshall wrote:

The Microsoft jet database engine cannot open the file
'qryOracleView-2006-08-24.txt'. It is already opened exclusively by
another user , or you need permission to view its data.

Wayne and Allen, thanks for your responses, but neither is working.

What's particularly odd is that once I specify a file name and get my
error, the file has actually been created... you can go to the folder
and see the text file. however, when you try to open it when your app
is still open, I get an error message in notepad telling me the file is
in use. Only when I turn off the mdb does Wondows allow me to open the
file and then it's a small 1kb thing with just the headers, but no data.

Odd. I've contacted MS Support - regardless of what anyone says, I've
found the expense worth it in the past when I've been hit with something
I can't google or news sleuth! 8) I'll post results if I get anything
to work.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Allen Browne]

Post the answer when you get it sorted out, Tim.

I suspect I did run into this once, but can't recall the detail. Seems like
there was some reason why the export was failing after it started, such as a
bad import/export spec.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumwrote in message
news:ec**********@coranto.ucs.mun.ca...

Tim Marshall wrote:

>The Microsoft jet database engine cannot open the file
'qryOracleView-2006-08-24.txt'. It is already opened exclusively by
another user , or you need permission to view its data.

Wayne and Allen, thanks for your responses, but neither is working.

What's particularly odd is that once I specify a file name and get my
error, the file has actually been created... you can go to the folder and
see the text file. however, when you try to open it when your app is
still open, I get an error message in notepad telling me the file is in
use. Only when I turn off the mdb does Wondows allow me to open the file
and then it's a small 1kb thing with just the headers, but no data.

Odd. I've contacted MS Support - regardless of what anyone says, I've
found the expense worth it in the past when I've been hit with something I
can't google or news sleuth! 8) I'll post results if I get anything to
work.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

Tim Marshall wrote:

Tim Marshall wrote:

>The Microsoft jet database engine cannot open the file
'qryOracleView-2006-08-24.txt'. It is already opened exclusively by
another user , or you need permission to view its data.

Post script:

It turns out, to my great chagrin and 3 weeks of serious inconvenience
for the space planners my app is used by, that the specification name
used by my docmd.transfertext line was referring to an export spec that
was not appropriate for what I wanted to do - in this case, it was comma
delimited and my data was full of commas.

I do find the dialog for saving specs a bit tricky to work with and one
has to be careful that one verifies that what you've saved is indeed
what you want to be actually saved.

Anyway, there you go, for Google posterity... 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Allen Browne]

Thanks for posting the solution, Tim.

Agreed that the error messages associated with bad import/export specs are
not always helpful.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumwrote in message
news:ee**********@coranto.ucs.mun.ca...

Tim Marshall wrote:

>Tim Marshall wrote:

>>The Microsoft jet database engine cannot open the file
'qryOracleView-2006-08-24.txt'. It is already opened exclusively by
another user , or you need permission to view its data.

Post script:

It turns out, to my great chagrin and 3 weeks of serious inconvenience for
the space planners my app is used by, that the specification name used by
my docmd.transfertext line was referring to an export spec that was not
appropriate for what I wanted to do - in this case, it was comma delimited
and my data was full of commas.

I do find the dialog for saving specs a bit tricky to work with and one
has to be careful that one verifies that what you've saved is indeed what
you want to be actually saved.

Anyway, there you go, for Google posterity... 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me