[AccessD] DAO Recordset question asked, answered, and misplaced.

Stuart McLachlan stuart at lexacorp.com.pg
Thu Nov 11 15:54:44 CST 2021

We went through this last month.  As I said they, you are confusing a Recordset with 

A recordset is  a collection of data rows and columns.  Access generally doesn't know about 
what SQL,if any , created the data and it certainly doesn't have an SQL property 

There are only two situations where you may be able to retrieve SQL used to create the 

Situation #1
If a Recordset was created from a QueryDef using the OpenRecordSet method of the 

 In that case, you can do something like 

Dim rs As DAO.Recordset
Dim qdf As QueryDef
Set rs = CurrentDb.QueryDefs("qryAllMailboxes").OpenRecordset
Set qdf = rs.CopyQueryDef
Debug.Print qdf.SQL

But that's the only situation where CopyQueryDef  works. If you try to use CopyQueryDef for 
a recordset created in any other way, you will just get an error.

SItuation #2:
if a recordset was created using an SQL string, the recordst's Name property contains the 
first 256 characters of that string.

In any ofther situation, you are SOL.

On 11 Nov 2021 at 15:31, Arthur Fuller wrote:

> Given a named recordset, which is never destroyed (so far as I can
> tell from the code I inherited from the previous developer, since
> retired). It appears that the the named recordset is never destroyed,
> but instead emptied upon each new rs.Open call> I think that RSs have
> a SQL property. That's what I want to grab, so I can use it to create
> a query and then a form based on the query, so I can see what I'm
> dealing with. Given that, I can clone the RS and use typical TS
> processing when I kick in the rest of the code.
> -- 
> Arthur
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

More information about the AccessD mailing list