[AccessD] DoCmd.OutputTo acOutputQuery with Temporary QueryDef

Charlotte and Lambert,

Thanks for the assistance, I really appreciate it.

Here is what I am trying to do.

We have a report that has a filter that varies (or can be on/off).
We would like to Export this report to Excel.

We can easily export to Excel from the underlying query (the report's record source).
The catch is that if we do this, it doesn't take into account the filter which may or may not be in affect when the report is displayed.

I have been working on creating a new Query by combining the original Query with the selection criteria contained in the filter.  I then use this new query to export to Excel.

I have this working with a permanent query, but I thought that it would be best of employ a temporary query.  I believe that I once read that if you are adding and deleting QueryDefs a lot, problems may arise.  I am not sure if this is really true or not.

Anyway, this is what I am trying to do.  I am still learning new things everyday.

Thanks Again,
Brad     





-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Wednesday, September 01, 2010 12:28 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] DoCmd.OutputTo acOutputQuery with Temporary QueryDef

No you cannot do that. OutputTo requires the name of the object being output. So you would think that this would work...


    Dim db As Database
    Dim qdfTemp As QueryDef

    Set db = fnThisDb

    With db
        ' Create temporary QueryDef.
        Set qdfTemp = .CreateQueryDef("", _
            "SELECT * FROM SomeTable")
        DoCmd.OutputTo acOutputQuery, qdfTemp.Name, acFormatRTF, "H:\test.trf"
        .Close
    End With



If you code Debug.Print qdfTemp.Name  then you will see the name is #Temporary QueryDef#, but if you try using qdefTemp.name as show above then all you get is an error 3011, [Jet] database engine could not find the object '#Temporary QueryDef#'.

So you need to create a real querydef, with a real name, and then delete it afterwards...

Dim qdfNew as QuerDef

Set qdfNew = .CreateQueryDef("NewQueryDef", "SELECT * FROM SomeTable")
DoCmd.OutputTo acOutputQuery, "NewQueryDef", acFormatRTF, "H:\test.trf"
CurrentDb.QueryDefs.Delete qdfNew.Name


Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Wednesday, September 01, 2010 12:00 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] DoCmd.OutputTo acOutputQuery with Temporary QueryDef

I don't think I understand what you're doing.  What are you trying to output the temporary query to?  I've never tried to output a temporary query using code, so I'm not sure whether or not it's possible.  A little more information might help.

Charlotte Foust

On Tue, Aug 31, 2010 at 3:32 PM, Brad Marks <BradM at blackforestltd.com> wrote:
> All,
>
>
>
> Is it possible to use the "DoCmd.OutputTo acOutputQuery" command with 
> a Temporary QueryDef?
>
>
>
> I have experimented but cannot get this to work.  I think that I am 
> missing something.
>
>
>
> An example would be most appreciated.
>
>
>
> Thanks,
>
> Brad
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.