Problem running DoCmd.TransferSpreadsheet

[davidm]

Hi Folks,

I'm running the following command:

DoCmd.TransferSpreadsheet acExport, 8, "qryDatabaseExportMain",
"DatabaseExport.xls", False, ""

When the command is encountered I get an error 3197, implying the object is
shared with another user, which is not the case.

The query is valid and produces an output when I look at it.

Any help would be most appreiciated.

Thanks,

Dave

 

[JimBurke via AccessMonster.com]

The only thing I see is that you don't have a path name specified for your
file - you should include the drive and directory in the filename, e.g. C:\
MyDir\DatabaseExport.xls.

 

[davidm]

Hi Jim,

Yes I take your point, at the moment it places the fie in "my documents".
But it doesn't really make a difference whether there is a path or not, I get
the error either way.

I have double checked my code to ensure that the query is not in use.

I have tried importing all the assets into a new file to rule out a
corruption issue. I did that and the same error ocurred.

Still no joy

Dave

 

[JimBurke via AccessMonster.com]

I don't know - I've used queries for exports and not had a problem. Mine
export to text files,but I don't think it should matter whether it's for text
or Excel. It is a simple SELECT query, isn't it? I'm wondering if there's an
option you have set in Access that is somehow causing this, but don't know
what it would be. The only thing I can think of trying is to create a table
that has all of the query fields, create an append query from the select
query using that table as the table you're appending to, then doing the
export using the table, but this may just give you the same error.

From what you said, I guess if you specify a file name only then it defaults
to My Documents - I figured you'd get an error if you didn't specify a fully
qualified file name.

Hi Jim,

Yes I take your point, at the moment it places the fie in "my documents".
But it doesn't really make a difference whether there is a path or not, I get
the error either way.

I have double checked my code to ensure that the query is not in use.

I have tried importing all the assets into a new file to rule out a
corruption issue. I did that and the same error ocurred.

Still no joy

Dave

The only thing I see is that you don't have a path name specified for your
file - you should include the drive and directory in the filename, e.g. C:\

[quoted text clipped - 17 lines]

 

[davidm]

Hi Jim,

In answer to your question it's a standard select query. I have tried
messing with the path, the error message does change if you give it an
invalid path.

The product I'm working on is an existing product, I'm just updating some
features on it. In the past, export to a spreadsheet has worked. So, I went
back to the original codeset and tried it there, on my machine, that fails
also. So now I'm thinking that its a local environmental issue on my machine
thats causing this, any ideas in that direction?

Thanks for your help on this,

Dave

I don't know - I've used queries for exports and not had a problem. Mine
export to text files,but I don't think it should matter whether it's for text
or Excel. It is a simple SELECT query, isn't it? I'm wondering if there's an
option you have set in Access that is somehow causing this, but don't know
what it would be. The only thing I can think of trying is to create a table
that has all of the query fields, create an append query from the select
query using that table as the table you're appending to, then doing the
export using the table, but this may just give you the same error.

From what you said, I guess if you specify a file name only then it defaults
to My Documents - I figured you'd get an error if you didn't specify a fully
qualified file name.

Hi Jim,

Yes I take your point, at the moment it places the fie in "my documents".
But it doesn't really make a difference whether there is a path or not, I get
the error either way.

I have double checked my code to ensure that the query is not in use.

I have tried importing all the assets into a new file to rule out a
corruption issue. I did that and the same error ocurred.

Still no joy

Dave

The only thing I see is that you don't have a path name specified for your
file - you should include the drive and directory in the filename, e.g. C:\

[quoted text clipped - 17 lines]

Dave