query a recordset

[JamesDeckert]

Access 2003

Is there some way to query a recordset? What I'd like to accomplish is to
create a table from a recordset. I'm doing it now programmatically,
traversing the rs and writing to the table. But I think it would execute
faster to use some kind of Make Table SQL to query the rs and create the
table. The recordset I'm using this time is a clone of a form recordset.

thanks,
James Deckert

 

[Michel Walsh]

SQL cannot uses your recordset directly: SQL is about data in tables and
available to many users while a recordset lives in memory of a PC (and
memory is not directly sharable between many users). Those are two different
worlds.

It is probably better to NOT start with a recordset at all.

But you can still walk over a recordset (rst.MoveNext) and, inside the loop,
insert records into a table of your choice:

CurrentDb.Execute "INSERT INTO tableName(field1, field2, field3)
VALUES( value1, value2, value3)", dbFailOnError


as example. Note that the individual values in the values list have to get
their appropriate delimiter, if they are string or date_time.

That would be relative slow, since inserting one row at a time as a
relatively high overhead. To avoid it, I repeat myself, don't start with a
recordset in the first place.



Vanderghast, Access MVP

 

[JamesDeckert]

Michel,
Thanks for the response. I kind of had my doubts, but thought I'd check
anyway.
The reason for the recordset is that I'd like to create a table based on a
forms recordset. This form can be filtered in various ways to display a
subset of the original table. I'm basing a report on what is displayed on the
form. I've already got the recordset creating a table one record at a time. I
just wanted to speed up the process if I could.

thanks again,
James