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

Arthur Fuller fuller.artful at gmail.com
Thu Nov 11 17:41:49 CST 2021


Thanks, Stuart. There are some snippets in the code that (why, I have no
idea, since I didn't write it) that in the event that no records match, do
an Insert with a dummy record. So perhaps I can reverse-engineer this SQL
ssring and at least get close to what is supposed to be in there. I shall
start there.

On Thu, Nov 11, 2021 at 4:55 PM Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:

> We went through this last month.  As I said they, you are confusing a
> Recordset with
> QueryDef.
>
> 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
> recordset.
>
> Situation #1
> If a Recordset was created from a QueryDef using the OpenRecordSet method
> of the
> querydef.
>
>  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
> >
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


-- 
Arthur


More information about the AccessD mailing list