SQL and recordsets

[rockman]

Is there a way to apply SQL statements to a recordset?

For example:

Dim rsOne as ADODB.recordset

rsOne.OpenRecord("SELECT * FROM tblEmployees")
doCmd.RunSQL "DELETE rsOne WHERE fldName = 'Jones'"

I understand the example above can be accomplished all in the OpenRecordset method but what I'm trying to get across is how can I manipulate an already created recordset with SQL.

Thanks for any thoughts,
Jeff

 

[WayneRyan]

Jeff,

I don't understand, your SQL to open the
recordset can have all of the logic that
you need in it.

sql = "Select * from YourTable " & _
"Where flda = '" & Me.src1 & "' " & _
"Order by fldb"

set rst = dbs.Openrecordset(sql)

Wayne

 

[rockman]

Yes, I realize that upon opening the recordset I have full SQL control, but I was wondering if I can manipulate recordsets with SQL after they are created.

Jeff

 

[WayneRyan]

Jeff,

I don't think that you can apply SQL to
an open recordset. If I remember, when
I tried it before, it would never accept
the rs name in the FROM part.

I think that you are "stuck" with the
.find mechanics. If you want repeated
applications of SQL you can base queries
on queries etc.

Wayne

 

[rockman]

Thanks for the reply Wayne. I was trying to tackle a problem that I have detailed in this post:

Combining table data problem

If I can't reprocess open recordsets with SQL, it looks like I need a slick SQL to generate the data upon opening the recordset.

Thanks again for you reply,
Jeff

 

[WayneRyan]

Jeff,

That looks like a simple UNION query.

See the help files ...

Wayne

 

[rockman]

Yep, the UNION command did the trick... THANKS!

Jeff