Is there any way to use a recordset object as the datasource fora query (Dao or ADO)?

[Andreas]

dim rs as Recordset
Set rs = ...

Dim rs2 as Recodset
Set rs = ... SELECT * FROM rs WHERE ...

Thanks in advance,
Andreas

 

[Steve Schapel]

Andreas

Can you say a little more about what you are trying to achieve here? So
far it looks like you want 2 recordsets both defined exactly the same as
each other. Is this correct? For what purpose?

 

[Andreas]

Hi Steve,
Thanks very much for your reply.

I am trying to build a local recordset which has all the detail data I
need (from a query with four source tables). This data will be stored in
the local object variable "rs".
As part of processing the data (writing the data to Word), I need to use
the same data to set up counters and do basic data validation. One way
to do that, is to use the "rs" data as the datasource for an aggregate
query to be stored in another object variable "rs2" and process the
result using VBA.

I could just run multiple queries but with the database being used over
a WAN, I am trying to reduce network traffic.
I am currently using VBA to step through each record from "rs" to
achieve my goal and this works fine, but I believe queries perform
faster and I was just wondering whether it could be done (I believe I
saw something somewhere, sometime - I just can't remember where).

 

[Steve Schapel]

Andreas,

You can create both recordsets based on the same query...
Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM YourQuery")
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM YourQuery")

Then you can use these two recordsets separately for the two separate
processes. Does that meet your requirements?

 

[Andreas]

Hi again,

That would work but requires the data to be accessed twice over the WAN.
I am ideally looking for a way to create a local (memory based) set of
data which can then be used as the datasource for subsequent queries:

Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM Tables")
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM rs1")

Can this be done somehow?
I am happy to use either DAO or ADO.

 

[Steve Schapel]

Andreas,

Ok, thanks for the further explanation. The only way I can think of
which might achieve your purpose, is to start off with an Append or
MakeTable query to write the data to a temporary database/table on the
local machine, and then base your subsequent recordset objects and data
manipulations on this.

 

[david epsom dot com dot au]

set rs = ...

set rs.filter = ....
set rs2 = rs.OpenRecordset()

set rs.filter = ...
set rs3 = rs.OpenRecordset()

You can't add fields or tables to the recordset: all
you can do is filter records from the parent recordset.

If you need to add fields or tables, you need to
create a persistent disconnected recordset in your
database to use as the basis for subsequent queries.
Of course, that can't be re-queried or refreshed.

(david)

 

[Andreas]

Hi,

Thanks for this.
This could be a partial solution, I'll have to give it some thought.
Do you know of any way to do the equivalent of the following:

set rs = ...

set rs2 = ???.OpenRecordset("SELECT Field1, Count(Field2) AS
CountOfField FROM rs GROUP BY Field1;")

Hope the SQL is roughly correct, I am not too brilliant doing this of
the top of my head (such a nice GUI in Access).

Regards,
Andreas

 

[david epsom dot com dot au]

Create a persistent recordset in your database:

Codedb.Execute "Select * into RS from Whatever where Whichever")

set rs2 = ???.OpenRecordset("SELECT Field1, Count(Field2) AS
CountOfField FROM rs GROUP BY Field1;")

(david)

 

[Andreas]

Hi,

I got Access on the other PC and can't look up help at present (need
sleep, did an allnighter last night).
If you still got the patience, may be you could bear with me.
The database is accessed via a WAN, with both the BE and the FE on the
server. If you say persistent, I presume it creates a table in the
database. That would mean still having to send data over the WAN to save
the recordset and to send it again (multiple times) when accessing it
later. Is there any way to keep the whole thing in local memory in such
a manner that multiple aggregate queries can be run against it for data
validation before using the original data for the final task (creating a
report in Word)? It would be so much more elegant than using arrays,
pointers and disprin!

Many thanks,
Andreas

 

[david epsom dot com dot au]

The database is accessed via a WAN, with both the BE and the FE on

server. If you say persistent, I presume it creates a table in the

I can understand why you didn't mention that before: you realise
that no-one else would inflict that structure on their clients :~).

Use Terminal Services or Citrix or IIS or some other service
that allows you to run both the FE and the BE on the server.

If your WAN is fast enough and your users are slow enough to
let you use a BE file on a WAN server, at least put the FE on
the client machine.

If you are sure that you are going to be the exception that
tests the rule about never running Access over a WAN, then
boost up the size of the Jet cache and the Jet paging interval.
You don't have to send data over the WAN if it is in the Jet
Cache.

(david)

 

[Andreas]

Hi,

I can understand why you didn't mention that before: you realise
that no-one else would inflict that structure on their clients :~).

I like to be different
Anyway, fast WAN, small database, currently in prototype status hence
everything on the server...
I am really just exploring this to expand on my (lack of) knowledge.

Use Terminal Services or Citrix or IIS or some other service
that allows you to run both the FE and the BE on the server.

If your WAN is fast enough and your users are slow enough to
let you use a BE file on a WAN server, at least put the FE on
the client machine.

Looked up CodeDb.
This could be solution by just having the relevant "library" on the
client PC - could come in handy - thank you.

If you are sure that you are going to be the exception that
tests the rule about never running Access over a WAN, then
boost up the size of the Jet cache and the Jet paging interval.
You don't have to send data over the WAN if it is in the Jet
Cache.

Now this is probably the most interesting part.
1) I gather there is no way to use a memory resident recordset as a
recordsource (which would explain why I found a site selling a COM
add-in that allows doing just that with ADO).
2) If I understand this correctly, then increasing the cache will keep
the data "locally" and any reference to the original tables will
actually access memory. Since I don't change any data, network traffic
would be minimal. This would therefore also be a good solution for a
slow LAN. I will look this up (presumably a Registry entry?). Is there
any (simple) way of checking how much memory is being used by the
different objects and how often the remote objects are accessed?


Regards,
Andreas

 

[david epsom dot com dot au]

any (simple) way of checking how much memory is being used by the

different objects and how often the remote objects are accessed?

No. You can use dbEngine.SetOption to set the size of the cache
and the cache update interval, but it is a method, not a property,
and there is no corresponding GetOption. You can work out roughly
how much cache is used by an object by decreasing the size of the
cache until the object access time indicates that it doesn't all
fit in the cache: there is no better way.

(david)

 

[Andreas]

Just a quick thank you for david.

Many thanks,
Andreas