>> Insert Into

J

Jonathan

Hi, is it possible to insert into a table using a recordset as the source? If
yes, what is the structure?

db.execute "Insert Into...

Many thanks,
Jonathan
 
M

Marshall Barton

Jonathan said:
Hi, is it possible to insert into a table using a recordset as the source? If
yes, what is the structure?

db.execute "Insert Into...
Click to expand...


No. SQL in unaware of onjects in a VBA procedure.

If you already have the data in a recordset, why create
another table for it?

If you have the data in a recordset, didn't you use a Select
query to open the recordset? Why not just use that query
instead of messing with the recordset and making another
table??
 
J

Jonathan

Marshall Barton said:
No. SQL in unaware of onjects in a VBA procedure.

If you already have the data in a recordset, why create
another table for it?

If you have the data in a recordset, didn't you use a Select
query to open the recordset? Why not just use that query
instead of messing with the recordset and making another
table??
Click to expand...

Hi Marsh, thanks for your response.
Maybe my method is 'wrong'...

I am filtering a large table to find a single record using the select
statement. If more than 1 record is found I want to filter the recordset; and
continue to do so until either a single record is found or no matching
records. I am hoping to avoid requerying the large data set.

Can I have a recordset with another recordset as its source?... I should try
this I suppose...

Many thanks, Jonathan
 
M

Marshall Barton

Jonathan said:
I am filtering a large table to find a single record using the select
statement. If more than 1 record is found I want to filter the recordset; and
continue to do so until either a single record is found or no matching
records. I am hoping to avoid requerying the large data set.

Can I have a recordset with another recordset as its source?... I should try
this I suppose...
Click to expand...


Yes. Here's a general outline of opening a recordset based
on another recordset:

Dim rs1 As Recordset
Dim rs2 As Recordset

Set rs1 = db.OpenRecordset("SELECT . . .")
If rs1.RecordCount > 1 Then
rs1.Filter = "somefield = " & somevalue
Set rs2 = rs1.OpenRecordset()
. . .
 
J

Jonathan

Thanks, works well...

Jonathan

Marshall Barton said:
Yes. Here's a general outline of opening a recordset based
on another recordset:

Dim rs1 As Recordset
Dim rs2 As Recordset

Set rs1 = db.OpenRecordset("SELECT . . .")
If rs1.RecordCount > 1 Then
rs1.Filter = "somefield = " & somevalue
Set rs2 = rs1.OpenRecordset()
. . .
Click to expand...
 
Top