Docmd.TransferSpreadsheet Error 1

[jes]

Hi Folks,

I'm running some code that runs a query and outputs the result to excel using the docmd.transferspread command. This process takes place for a number of queries each sending to a different spreadsheet.

When using access 97 there was no problem, but having upgraded to 2000, the first one runs with no problems but thereafter I get a warning message saying:

Cannot Update. Database or object is read only.
The error number is 3027.

I've got full on write permissions, none of the files are open.

Has anyone come across this problem?

Thanks,

Jes

 

[jgoodman00]

Are you using a standard Access database, & can you manually open, then export the query results?

I had this problem when connecting to a SQL server database. It is not possible to use the TransferSpreadsheet method on an SQL view/sp.

Try manually exporting the query. If the export is successful, check the file opens...
James Goodman

 

[jes]

Hi,

It's a standard access database and the info is quite happy to manually export. The difficulty is that it works for the first one in the list but not for the rest.

Hmmm

Jes

 

[jgoodman00]

I have not encountered this. Have you considered contacting Microsoft Support? You are probably entitled to free support, depending on which version of Office you have... James Goodman

 

[jes]

Hi James,

I think I'm going to have to as I cannot understand it. I've just tried it again after having manually exporting the second query and it worked - this time for the first two queries. So it seems like it's unable to create new files! Doubly weird in that 1)the first time it created the file happily and 2)it doesn't expect a file to be there!

I don't know, perhaps a vodka will help.

Cheers mate

Jes

 

[jgoodman00]

lol, vodka is the best solution

James Goodman

 

[kramerica]

Heads up!

I was getting the same error: Can't Update .. blah blah blah.

DoCmd.TransferText acImportDelim, , tblstrimportspec2, strimportspec2, -1

DoCmd.TransferSpreadsheet acImportDelim, , tblstrimportspec1, strimportspec1, -1

During the course of testing the import of XLS vs. CSV
I didn't change my variable STRIMPORTSPEC1 back to the CSV file.

If you try to do a TRANSFERTEXT using a XLS file you will get this error. If you use a CSV file it works fine.

Kramerica

 

[SadOldGoth]

Hi Kramerica,

Thanks for that - I'll give it a try,

Later,

Jes

 

[JayZ755]

I had this problem recently and found a fluky solution.

I was creating a number of Excel files in a for next loop. The file name was a fairly long path plus a file name variable. I got the "can't update" error when there were spaces in the file name, even though there were spaces in the directory names. I wound up mapping a drive to a subdirectory on the path, which shortened the path name. After this everything worked.

 

[itybity49]

I just ran into this problem on Export, and I wanted to update the thread. The solution to the problem I had was simply adding the .csv extension to the file name. The name of the actual file, path, etc didn't matter but if I didn't have the .csv it would give me the "Can't Update . .. " error. Once I added the .csv extension it worked great.