[AccessD] TransferSpreasheet Problem

Hi Rocky,

It is looking for a table name or a query name  (ie, an object and not a sql
statement)

Try  this simple function.

Private Function xx()
    Dim strQryName As String, strQryNamePath As String
    strQryName = "zzzTest"
    strQryNamePath = "C:\MyOutput"
    DoCmd.TransferSpreadsheet acExport, , strQryName, strQryNamePath, True
End Function

Where zzzTest is a Queryname for your query and the sql statement is "Select
* FROM tblAuditTrail"

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: 03 February 2010 19:07
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/>