[AccessD] TransferSpreasheet Problem

Rocky,

I've never tried to use a SQL string as the query but I'm guessing you
can't.  To get around the issue, you can change the SQL of a query then
use the query in the DoCmd.TransferSpreadsheet, ie:

    Dim db As Database
    Dim qdf As QueryDef
    Dim strSQL as String
    
    Set db = CurrentDb()
    strSQL = "Select * FROM tblAuditTrail"
    Set qdf = db.QueryDefs("qryYourQueryHere")
    qdf.SQL = strSQL
    qdf.Close
    Set qdf = Nothing

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

HTH,

Rusty

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

 

 

**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review 
by, and/or disclosure to, someone other than the recipient.
**********************************************************************