|
 |
access thread: DoCmd.TransferSpreadsheet gives error with remote server
Message #1 by "Robert J. McCluskey" <rjmccluskey@h...> on Fri, 23 Nov 2001 03:09:55
|
|
I have an Access 2000 database with tables linked to a remote server thru
ODBC drivers. I have tried using the transferSpreadsheet method of the
DoCmd to transfer the results of queries to an Excel workbook. The code
fails with the message that the recordset is not updatable.
Any suggestions of what I may be ding incorrectly, or does this method not
work with a remote server linked database?
Thanks
Bob
Message #2 by "John Ruff" <papparuff@c...> on Thu, 22 Nov 2001 19:10:40 -0800
|
|
Robert,
Double-check your query and make sure it's a Select query, not an append
or update query.
John Ruff - The Eternal Optimist :-)
-----Original Message-----
From: Robert J. McCluskey [mailto:rjmccluskey@h...]
Sent: Friday, November 23, 2001 3:10 AM
To: Access
Subject: [access] DoCmd.TransferSpreadsheet gives error with remote
server
I have an Access 2000 database with tables linked to a remote server
thru
ODBC drivers. I have tried using the transferSpreadsheet method of the
DoCmd to transfer the results of queries to an Excel workbook. The code
fails with the message that the recordset is not updatable.
Any suggestions of what I may be ding incorrectly, or does this method
not
work with a remote server linked database?
Thanks
Bob
---
You are currently subscribed to access as: papparuff@c... To
unsubscribe send a blank email to $subst('Email.Unsub')
Message #3 by "Robert J. McCluskey" <rjmccluskey@h...> on Fri, 23 Nov 2001 16:37:20
|
|
> Robert,
>
> Double-check your query and make sure it's a Select query, not an append
> or update query.
>
> John Ruff - The Eternal Optimist :-)
>
>
>
> -----Original Message-----
> From: Robert J. McCluskey [mailto:rjmccluskey@h...]
> Sent: Friday, November 23, 2001 3:10 AM
> To: Access
> Subject: [access] DoCmd.TransferSpreadsheet gives error with remote
> server
>
>
> I have an Access 2000 database with tables linked to a remote server
> thru
> ODBC drivers. I have tried using the transferSpreadsheet method of the
> DoCmd to transfer the results of queries to an Excel workbook. The code
>
> fails with the message that the recordset is not updatable.
>
> Any suggestions of what I may be ding incorrectly, or does this method
> not
> work with a remote server linked database?
>
> Thanks
>
> Bob
>
> ---
> You are currently subscribed to access as: papparuff@c... To
> unsubscribe send a blank email to $subst('Email.Unsub')
>
>
>
John,
I did as you suggested - it is a select query. I still get the error
message "Run-time error '3027': Cannot Update. Database object is read
only."
Here is my code:
Private Sub CmdTrf_Click()
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, "qryIRCPostPetition", "C:/TestIRC.xls.)"
End Sub
Any other suggestions?
Thanks,
Bob
Message #4 by Walt Morgan <wmorgan@s...> on Fri, 23 Nov 2001 11:02:24 -0600
|
|
Robert,
Should your "acExport" parameter be "acImport"?
Walt
Here is my code:
Private Sub CmdTrf_Click()
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, "qryIRCPostPetition", "C:/TestIRC.xls.)"
End Sub
Message #5 by "Robert J. McCluskey" <rjmccluskey@h...> on Fri, 23 Nov 2001 17:51:56
|
|
> Robert,
>
> Should your "acExport" parameter be "acImport"?
>
> Walt
>
>
> Here is my code:
>
> Private Sub CmdTrf_Click()
> DoCmd.TransferSpreadsheet acExport,
> acSpreadsheetTypeExcel9, "qryIRCPostPetition", "C:/TestIRC.xls.)"
>
> End Sub
>
>
>
>
Walt,
I am attempting to export the query from Access to Excel. It seems the
parameter should be "acExport". Do I have my logic turned around?
Message #6 by "John Ruff" <papparuff@c...> on Fri, 23 Nov 2001 10:11:49 -0800
|
|
Ah, I think I see the problem. Change your code to:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryIRCPostPetition", "C:\TestIRC.xls"
It looks like your file name is incorrect. You have "C:/TestIRC.xls.)"
and I believe it should be "C:\TestIRC.xls"
John Ruff - The Eternal Optimist :-)
-----Original Message-----
From: Robert J. McCluskey [mailto:rjmccluskey@h...]
Sent: Friday, November 23, 2001 4:37 PM
To: Access
Subject: [access] RE: DoCmd.TransferSpreadsheet gives error with remote
server
> Robert,
>
> Double-check your query and make sure it's a Select query, not an
> append or update query.
>
> John Ruff - The Eternal Optimist :-)
>
>
>
> -----Original Message-----
> From: Robert J. McCluskey [mailto:rjmccluskey@h...]
> Sent: Friday, November 23, 2001 3:10 AM
> To: Access
> Subject: [access] DoCmd.TransferSpreadsheet gives error with remote
> server
>
>
> I have an Access 2000 database with tables linked to a remote server
> thru ODBC drivers. I have tried using the transferSpreadsheet method
> of the DoCmd to transfer the results of queries to an Excel workbook.
> The code
>
> fails with the message that the recordset is not updatable.
>
> Any suggestions of what I may be ding incorrectly, or does this method
> not work with a remote server linked database?
>
> Thanks
>
> Bob
>
> ---
> You are currently subscribed to access as: papparuff@c... To
> unsubscribe send a blank email to $subst('Email.Unsub')
>
>
>
John,
I did as you suggested - it is a select query. I still get the error
message "Run-time error '3027': Cannot Update. Database object is read
only."
Here is my code:
Private Sub CmdTrf_Click()
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, "qryIRCPostPetition", "C:/TestIRC.xls.)"
End Sub
Any other suggestions?
Thanks,
Bob
---
You are currently subscribed to access as: papparuff@c... To
unsubscribe send a blank email to $subst('Email.Unsub')
Message #7 by Walt Morgan <wmorgan@s...> on Fri, 23 Nov 2001 12:07:34 -0600
|
|
Robert,
I was not sure, just an idea since it appeared that the error message seemed
to indicate an attempt to update the access table. Sometimes these things
can wrap a person around the axle.
Walt
Message #8 by "Alan Douglas" <aland@a...> on Sat, 24 Nov 2001 02:35:28
|
|
Along the same lines ... Change the filename from "C:/..." to C:\...".
The forward slash should be a backslash.
... AL
> Robert,
>
> I was not sure, just an idea since it appeared that the error message
seemed
> to indicate an attempt to update the access table. Sometimes these things
> can wrap a person around the axle.
>
> Walt
>
>
Message #9 by "Robert J. McCluskey" <rjmccluskey@h...> on Sat, 24 Nov 2001 17:38:03
|
|
> I have an Access 2000 database with tables linked to a remote server
thru
> ODBC drivers. I have tried using the transferSpreadsheet method of the
> DoCmd to transfer the results of queries to an Excel workbook. The code
> fails with the message that the recordset is not updatable.
>
> Any suggestions of what I may be ding incorrectly, or does this method
not
> work with a remote server linked database?
>
> Thanks
>
> Bob
John, Walt and Al,
I discovered the errors of my ways. I had not created the file first.
(The typos in file name shown in the threads below were my typos in the
threads not in my code.) I had been under the mis-impression that the
code would create the file on the fly. I should know better. After
creating the file the code performed as expected. I now have experience -
experience is what you get when you don't get what you want.
Thanks Guys, for you help and patience,
Bob
Message #10 by "Alan Douglas" <aland@a...> on Sat, 24 Nov 2001 23:57:37
|
|
I'm flabbergasted ... I'm working in Access 97, and used the
TransferSpreadsheet method to CREATE a workbook file:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, rst!Manager,
strFileName, True
Do you think this is an "undocumented feature" of Access 2000?
(Is there an extra option on the command in 2000?)
... AL
> John, Walt and Al,
>
> I discovered the errors of my ways. I had not created the file first.
> (The typos in file name shown in the threads below were my typos in the
> threads not in my code.) I had been under the mis-impression that the
> code would create the file on the fly. I should know better. After
> creating the file the code performed as expected. I now have
experience -
> experience is what you get when you don't get what you want.
>
> Thanks Guys, for you help and patience,
>
> Bob
>
Message #11 by "John Ruff" <papparuff@c...> on Sat, 24 Nov 2001 18:07:47 -0800
|
|
Bob,
The file will be created by the docmd.transfershpreadsheet funtion. You
do not have to create it first. If this is what you are truly having to
do before your spreadsheet is created, then there is something else
going on. Unfortunately, I have no clue what it might be.
John Ruff - The Eternal Optimist :-)
-----Original Message-----
From: Robert J. McCluskey [mailto:rjmccluskey@h...]
Sent: Saturday, November 24, 2001 5:38 PM
To: Access
Subject: [access] Re: DoCmd.TransferSpreadsheet gives error with remote
server
> I have an Access 2000 database with tables linked to a remote server
thru
> ODBC drivers. I have tried using the transferSpreadsheet method of
> the
> DoCmd to transfer the results of queries to an Excel workbook. The
code
> fails with the message that the recordset is not updatable.
>
> Any suggestions of what I may be ding incorrectly, or does this method
not
> work with a remote server linked database?
>
> Thanks
>
> Bob
John, Walt and Al,
I discovered the errors of my ways. I had not created the file first.
(The typos in file name shown in the threads below were my typos in the
threads not in my code.) I had been under the mis-impression that the
code would create the file on the fly. I should know better. After
creating the file the code performed as expected. I now have experience
-
experience is what you get when you don't get what you want.
Thanks Guys, for you help and patience,
Bob
---
You are currently subscribed to access as: papparuff@c... To
unsubscribe send a blank email to $subst('Email.Unsub')
|
|
|
View Cart
