How to save a recordset as a query?

[Lu An De]

Hi all,

Does anyone know how to save a recordset as a query(if it CAN be done)?

The recordset has been made, opened and appended in code.

Alternatively, how to save a recordset as a table(in code)?

What I'm trying to do is take a recordset that I've built by adding to it
only
the pertinent records from another recordset and make its own records
available for
querying via a select statement(all in code). I know that I can make a table
or a
query the subject of a select statement.

Any ideas?

Regards, Lu An De

 

[Graham R Seach]

Lu An,

I'm not convinced I understand what you're trying to do.

Do you want to apply a filter to an existing recordset? If so, then you can
use the Recordset object's Filter method.

Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim sSQL1 As String
Dim sSQL2 As String

Set db = CurrentDb
sSQL1 = "SELECT * FROM Table1"
sSQL2 = "Field1 = 'abc'"
Set rs1 = db.OpenRecordset(sSQL1, dbOpenSnapshot)

'Filter the recordset
rs1.Filter = sSQL2
Set rs2 = rs1.OpenRecordset()

'Do something

rs2.Close
rs1.Close
Set rs2 = Nothing
Set rs1 = Nothing
Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Lu An De]

Thanks for your reply Graham,

I know it sounds odd but its a bit off a contorted search process I'm doing.
Being able to save a recordset as a query or table in code would be really
helpful.
Is there a way to make and run a 'Make Table' query all in code?

Lu An De

 

[Lu An De]

I've just had a better read of your suggestion Graham and will give it a
try.

Thanks.

 

[Mike Painter]

Hi all,

Does anyone know how to save a recordset as a query(if it CAN be
done)?

The recordset has been made, opened and appended in code.

Alternatively, how to save a recordset as a table(in code)?

What I'm trying to do is take a recordset that I've built by adding
to it only
the pertinent records from another recordset and make its own records
available for
querying via a select statement(all in code). I know that I can make
a table or a
query the subject of a select statement.

Any ideas?

Regards, Lu An De

A recordset is a collection of records and may be a table or a query.
A query may contain one or more tables which are almost always related.

I don't think you can create a recordset with out invoking one or more
tables.
(You could create a pseudo set with an array.)

Adding records from one table to another is done with an append query.
You can use a make table query to save a recordset.

Normally you would save a query which would produce a recordset.

It could be a unique query that always returned the same records but
normally would not.