ListArc » English » Computer and Internet » AccessD » DoCmd.OutputTo+acOutputQuery+with+Temporary+QueryDef


DoCmd.OutputTo+acOut...


01-09-2010 02:32 AM
1


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

http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
)


01-09-2010 07:59 PM
2


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 <> 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
>
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

--
AccessD mailing list

http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
)


01-09-2010 09:27 PM
3


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- [mailto:accessd-] 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 <> 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
>
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

--
AccessD mailing list

http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list

http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
)


01-09-2010 10:07 PM
4


If that is so, then the instructions are somewhat misleading, since
the directions I found indicate that when the current object is being
exported, you don't need the name. Might be a versional difference.

Charlotte Foust

On Wed, Sep 1, 2010 at 10:27 AM, Heenan, Lambert
<> wrote:
> 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- [mailto:accessd-] 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 <> 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
>>
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>
> --
> AccessD mailing list
>
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
>
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

--
AccessD mailing list

http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
)


01-09-2010 10:17 PM
5


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- [mailto:accessd-] 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- [mailto:accessd-] 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 <> 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
>
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

--
AccessD mailing list

http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list

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.


--
AccessD mailing list

http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
)


01-09-2010 10:37 PM
6


Hmm. I'm running Access 2003.

If you had a temporary querydef, AKA an anonymous querydef, how would you make it the current object so that it could be exported?

Lambert

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

If that is so, then the instructions are somewhat misleading, since the directions I found indicate that when the current object is being exported, you don't need the name. Might be a versional difference.

Charlotte Foust

On Wed, Sep 1, 2010 at 10:27 AM, Heenan, Lambert <> wrote:
> 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-
> [mailto:accessd-] 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 <> 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
>>
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>
> --
> AccessD mailing list
>
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
>
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

--
AccessD mailing list

http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list

http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
)


02-09-2010 07:07 PM
7


That was what I was trying to figure out. I suppose you might do it
by creating a recordset from the querydef and doing an export on the
recordset object, but until the most recent explanation, I hadn't
realized a report was involved. The report itself could be exported,
of course, although the formatting would be lousy. I wonder if you
could export the recordsource of the report and get the right data?

Charlotte Foust

On Wed, Sep 1, 2010 at 11:37 AM, Heenan, Lambert
<> wrote:
> Hmm. I'm running Access 2003.
>
> If you had a temporary querydef, AKA an anonymous querydef, how would you make it the current object so that it could be exported?
>
> Lambert
>
> -----Original Message-----
> From: accessd- [mailto:accessd-] On Behalf Of Charlotte Foust
> Sent: Wednesday, September 01, 2010 2:07 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] DoCmd.OutputTo acOutputQuery with Temporary QueryDef
>
> If that is so, then the instructions are somewhat misleading, since the directions I found indicate that when the current object is being exported, you don't need the name.  Might be a versional difference.
>
> Charlotte Foust
>
> On Wed, Sep 1, 2010 at 10:27 AM, Heenan, Lambert <> wrote:
>> 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-
>> [mailto:accessd-] 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 <> 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
>>>
>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>> Website: http://www.databaseadvisors.com
>>>
>>
>> --
>> AccessD mailing list
>>
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>> --
>> AccessD mailing list
>>
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>
> --
> AccessD mailing list
>
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
>
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

--
AccessD mailing list

http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
)


02-09-2010 10:00 PM
8


Charlotte,

Thanks for your help.

Yes, I could export the Report's RecordSource (the underlying query). The catch is that if the report has a filter, using the underlying query will return all records (not filtered).

Brad



-----Original Message-----
From: accessd- [mailto:accessd-] On Behalf Of Charlotte Foust
Sent: Thursday, September 02, 2010 10:07 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] DoCmd.OutputTo acOutputQuery with Temporary QueryDef

That was what I was trying to figure out. I suppose you might do it
by creating a recordset from the querydef and doing an export on the
recordset object, but until the most recent explanation, I hadn't
realized a report was involved. The report itself could be exported,
of course, although the formatting would be lousy. I wonder if you
could export the recordsource of the report and get the right data?

Charlotte Foust

On Wed, Sep 1, 2010 at 11:37 AM, Heenan, Lambert
<> wrote:
> Hmm. I'm running Access 2003.
>
> If you had a temporary querydef, AKA an anonymous querydef, how would you make it the current object so that it could be exported?
>
> Lambert
>
> -----Original Message-----
> From: accessd- [mailto:accessd-] On Behalf Of Charlotte Foust
> Sent: Wednesday, September 01, 2010 2:07 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] DoCmd.OutputTo acOutputQuery with Temporary QueryDef
>
> If that is so, then the instructions are somewhat misleading, since the directions I found indicate that when the current object is being exported, you don't need the name.  Might be a versional difference.
>
> Charlotte Foust
>
> On Wed, Sep 1, 2010 at 10:27 AM, Heenan, Lambert <> wrote:
>> 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-
>> [mailto:accessd-] 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 <> 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
>>>
>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>> Website: http://www.databaseadvisors.com
>>>
>>
>> --
>> AccessD mailing list
>>
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>> --
>> AccessD mailing list
>>
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>
> --
> AccessD mailing list
>
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
>
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

--
AccessD mailing list

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.


--
AccessD mailing list

http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
)