[AccessD] TransferSpreasheet Problem

Rusty's solution leaves you with a query hanging around, which might cause a runtime error next time you run the process. Here is another way which creates a query on the fly in one line, exports it and the gets rid of the temporary query.


Dim strSQL As String
Dim qd As QueryDef
strSQL = "Select * FROM tblAuditTrail" ' plus you can add all the filter statements you need here

CurrentDb.CreateQueryDef "zTemp", strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "zTemp",  strFrontEndPath & "ExportedAuditTrail", True
DoCmd.DeleteObject acQuery, "zTemp" 


Lambert
-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Wednesday, February 03, 2010 2:07 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] TransferSpreasheet Problem

Dear List:

In the line:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strSQL, strFrontEndPath & "ExportedAuditTrail", True


strSQL = "Select * FROM tblAuditTrail" where tblAuditTrail is a linked table.

I get an error message The Microsoft Jet Database engine could not find the object 'Select * FROM tblAuditTrail'.  

Can you not use a SQL statement as the query for export?

I can't put this into a query because the user can filter on several fields so I build those filters into the SQL statement using WHERE...AND...AND...etc.

MTIA

 

Rocky Smolin

Beach Access Software

858-259-4334

www.e-z-mrp.com <http://www.e-z-mrp.com/> 

www.bchacc.com <http://www.bchacc.com/>