Using TransferSpreadsheet from a form button

[slames]

I have a query that I want to export to a specific excel file. I am trying to open the query when clicking on a button and then use the transferspreadsheet function to export it to this file. I am using SQL Server. It keeps coming up with an error saying that it needs a table name, I am giving it a query name, is there a way to use this function with a view or stored procedure?

Thanks

Steph

 

[quest4]

slames, what is in your FROM clause? That is where you should have your table defined. Hope this helps.

 

[slames]

This is the query I am trying to export into Excel automatically:

SELECT tblBook.bkISBN, tblBook.bkAuthor, tblBook.bkTitle,
tblBook.bkHardback, tblBook.bkPagecount,
tblBook.bkTrimmedPageSize, tblJob.jbQuantity
FROM dbo.tblBook INNER JOIN
dbo.tblJob ON
dbo.tblBook.bkISBN = dbo.tblJob.jbISBN
WHERE (dbo.tblJob.jbDespatchedWhen < CONVERT(DATETIME,
'2003-11-01 00:00:00', 102)) AND
(dbo.tblJob.jbDespatchedWhen > CONVERT(DATETIME,
'2003-09-30 00:00:00', 102)) AND
(dbo.tblBook.bkClient = N'bsp') AND
(dbo.tblJob.jbQuantity < 100)


This is the line of VBA I am trying to get to do it - on the event of clicking a button:

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel3, &quot;TESTAutoExcelDump&quot;, &quot;C:\text&quot;

This is the error I get:
Run time error 3146. ODBC call failed, There is already an object named &quot;TESTAutoExcelDump&quot; in the database. #2714.

Can anyone help?

 

[Taff07]

Slames,

The default option for the Transferspreadsheet function is acImport.

If you just add acExport before your first , then it should be fine.

so: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, &quot;TESTAutoExcelDump&quot;, &quot;C:\text&quot;

Hope this is right

Ian

 

[slames]

Thanks for your reply, I adjusted the code as you suggested above I still can't get it to output the query to excel but it does work when exporting a table. Is there a reason why I can't export this query? or any other query?

Thanks in advance

Steph

 

[Taff07]

Sorry but I don`t know the answer to the query part. I`m only using Access 97 which allows me to use the name of a query within the function.

Maybe you could use a make table query before exporting and then delete it afterwards?

Hope someone know the answer

Ian