TransferSpreadsheet Problem

[RudiK]

I am trying to export records based on a query.

DoCmd.TransferSpreadsheet
acExport, , "Query", "C:\test.xls", True

The Query "query" works fine but it does not seem to be
accepted by the "TransferSpreadsheet"-method. I get an
error 7874 which says that the object "query" can not be
found ? Am i doing someting wrong ???

 

[JohnFol]

is the query called "query" a select query?

 

[RudiK]

What do you mean with "select query" ?
It is a query to create a view from the data of two tables
which are on a sql2k server. If i am using te
transferspreadsheet with table names (dbo.tablename)
instead of query names it works ?

 

[Gary Walter]

I am trying to export records based on a query.

DoCmd.TransferSpreadsheet
acExport, , "Query", "C:\test.xls", True

The Query "query" works fine but it does not seem to be
accepted by the "TransferSpreadsheet"-method. I get an
error 7874 which says that the object "query" can not be
found ? Am i doing someting wrong ???

Hi RudiK,

This works for me in Access 2000:

Dim strSQL As String

strSQL = "SELECT * " _
& "INTO [Excel 8.0;database=C:\test.xls].somewksname " _
& "FROM Query;"
CurrentDb.Execute strSQL, dbFailOnError

Good luck,

Gary Walter

 

[Gary Walter]

Hi RudiK,

What is the SQL?

There have been several posts
with this error using DoCmd...
from ADP.

If you can open a recordset to your view,
you might be able to adapt:

http://www.mvps.org/access/modules/mdl0035.htm

 

[Gary Walter]

I wonder if the error does not come from
one or more of the following (i.e., DoCmd
getting confused by RecordCount or "other
stuff" that gets or does not get returned):

http://www.able-consulting.com/ADO_FAQ.htm

Q2) I keep on getting a "-1" for the my Recordset's RecordCount property. Why?

By default, ADO creates a server-side recordset with a CursorType of forward-only
(adOpenForwardOnly). A forward-only cursor does not support the RecordCount
property, even if you move to the last record in the recordset after you've opened
it.

Q25) Using the SQLOLEDB provider, whenever I call a stored procedure that uses a temp
table, I get the error message "3704 - The operation requested by the application is
not allowed if the object is closed." Why?

The SQL Server OLE DB provider (SQLOLEDB) has a new behavior designed to provide more
accurate information about what happened in the stored procedure.

Each SQL statement within a stored procedure returns a "result", either a count of
rows affected, or a resultset. You can walk through these result sets in ADO using
the NextRecordset method on the Recordset object.

If you don't want this more accurate information, then put the "SET NOCOUNT ON"
statement at the beginning of the stored procedure.

Q40) When calling a Stored Procedure, I'm getting null values for the output
parameter(s). Why?

When using a server-side (adUseServer) cursor location, ADO doesn't fill in the out
parameter(s) for a recordset until the returned recordset is closed.

If you need the value of an output parameter(s) before closing the recordset, then
use a client-side (adUseClient) cursor.

 

[Nikos Yannacopoulos]

RudiK,

Calling a query "Query" is not a good idea, it's probably a reserved keyword
for Access. Change the query name to something else.

HTH,
Nikos

 

[Jamie Collins]

By default, ADO creates a server-side recordset
with a CursorType of forward-only

Only true if the connection has a server-side cursor. If the
connection uses a client-side cursor, then by default ADO creates a
recordset with a server-side cursor of type static (adOpenStatic) and
the RecordCount property should be available.

Jamie.

--

 

[Jamie Collins]

By default, ADO creates a server-side recordset
with a CursorType of forward-only

Ahem! I'll try that again.

If the connection uses a client-side cursor, then by default ADO
creates a
recordset with a *client-side* cursor of type static (adOpenStatic)
and the RecordCount property should be available.

Jamie.

--