Using a Module-level recordset as a recordsource

[Dennis Snelgrove]

I've got a form whose RecordSource is a query with a User-Defined
Function in it. The UDF is passed the current record's Primary key,
then it has to run another DAO recordset in order to check for
follow-up records in the same table. The table is of course getting
bigger all the time as new entries are added, and so far it's up to
about 45000 records. The form is limited to the last 14 hours of
entries, as a default, so there aren't usually more than 30-40 entries
in the detail. The trouble is that it takes about 5-8 seconds for the
form to finish updating the recordset, and I think much of the reason
is due to all the repetitive recordset work. I figure that it might
speed things up if I created a DAO recordset as a global form-level
variable in the main form, then in the Form_Open event I can set it to
mirror the recordsource of the Detail. I can then use this variable
directly in my UDF, thereby saving a lot repetitive scanning of the
main table. In addition, it would save on a lot of traffic over the
network to the back-end file. I know that I can create a local table in
the front-end and append the records into it, but this strikes me as
more work than just using a variable that will disappear when the form
is closed. I'd have to keep doing a complete Delete on the contents,
then an Append of the info over again. An object I can simply Requery
when there are new entries added.

So, my question is this: if I have an object, say rstRecordDetail, and
I want to use it as the recordsource in another query, how do I
reference it? I've tried the following with no luck.

SELECT * FROM rstRecordDetail;

Thanks for any help...

 

[Ken Snell \(MVP\)]

With ACCESS DAO recordsets, you cannot reference the recordset in a query.
You'd need to save the recordset's SQL statement as a saved query (using
CreateQueryDef action) and then you can use that saved query in the other
query.

 

[Dennis Snelgrove]

Thanks for the response.

But if I save the SQL as a query, doesn't that kinda miss the point of
what I'm trying to do? I mean, this would give me the SQL, but I'm
trying to hold the actual recordset in memory so as to minimize
processing time. Or does this do what I'm looking for?

Also, if I converted my DAO.RecordSet to an ADO.RecordSet, would that
allow me to reference the variable in my SQL?

I appreciate your time and help, Ken. Thanks...

 

[Ken Snell \(MVP\)]

Comments inline...
--

Ken Snell
<MS ACCESS MVP>

Thanks for the response.

But if I save the SQL as a query, doesn't that kinda miss the point of
what I'm trying to do? I mean, this would give me the SQL, but I'm
trying to hold the actual recordset in memory so as to minimize
processing time. Or does this do what I'm looking for?

Yes, it would seem so. But you cannot "see" a DAO recordset that you've
created in code except in the code as a recordset object that is manipulated
via DAO methods.

Also, if I converted my DAO.RecordSet to an ADO.RecordSet, would that
allow me to reference the variable in my SQL?

I've not worked much with ADO recordsets, so others may be able to give a
better answer. I know that ADO recordsets can be disconnected, but I don't
know if that allows one to "see" them from a standard SQL statement. I'm
inclined to think "No" because the variable that defines the recordset is a
creature of the programming and is not "visible" to other objects in the
database except via the use of code to "interpret" what the variable
represents. For example, one cannot refer to a global constant or variable
within a query's SQL statement; one must call a user-defined VBA function
that returns the value of the constant or variable to the query so that it
can be used.

 

[Marshall Barton]

I've got a form whose RecordSource is a query with a User-Defined
Function in it. The UDF is passed the current record's Primary key,
then it has to run another DAO recordset in order to check for
follow-up records in the same table. The table is of course getting
bigger all the time as new entries are added, and so far it's up to
about 45000 records. The form is limited to the last 14 hours of
entries, as a default, so there aren't usually more than 30-40 entries
in the detail. The trouble is that it takes about 5-8 seconds for the
form to finish updating the recordset, and I think much of the reason
is due to all the repetitive recordset work. I figure that it might
speed things up if I created a DAO recordset as a global form-level
variable in the main form, then in the Form_Open event I can set it to
mirror the recordsource of the Detail. I can then use this variable
directly in my UDF, thereby saving a lot repetitive scanning of the
main table. In addition, it would save on a lot of traffic over the
network to the back-end file. I know that I can create a local table in
the front-end and append the records into it, but this strikes me as
more work than just using a variable that will disappear when the form
is closed. I'd have to keep doing a complete Delete on the contents,
then an Append of the info over again. An object I can simply Requery
when there are new entries added.

So, my question is this: if I have an object, say rstRecordDetail, and
I want to use it as the recordsource in another query, how do I
reference it? I've tried the following with no luck.

SELECT * FROM rstRecordDetail;

From what you've implied about your objective, you do not
need to use any SQL. You can create a new, filtered
recordset based on the original recordset:

rstRecordDetail.Filter = "[some field] = " & somevalue
Set rsnew = rstRecordDetail.OpenRecordset()

Or locate specific records in the original recordset:

With rstRecordDetail
.FindFirst "[some field] = " & somevalue
Do Until .EOF
. . .
.FindNext "[some field] = " & somevalue
Loop

Or, if you can sort the records in the recordset, FindFirst
is sufficient:

With rstRecordDetail
.FindFirst "[some field] = " & somevalue
Do ![some field] <> & somevalue
. . .
Loop

Most likely, the last approach is measurably the fastest.

Note that the DAO Recordset object also has the Requery
method, so you can start over every hour ;-)