Delete rows from a recordset without affecting the underlying query...

[ViAn]

I want to copy the records from a query except for the two first columns. I thought I could use:

myRst = myQuery.OpenRecordset(dbOpenDynaset)
myRst.Fields.Delete("MyFirstField")
myRst.Fields.Delete("MySecondField")

But I am not permitted to make updates on this recordset.
NB: I do NOT want to update "myQuery", only "myRst".

Ideas?

- ViAn -

 

[Rolliee]

If you wish to play with the data without changes, use a recordsetclone which is a copy of the recordset. Otherwise making changes to the recordset makes the changes to the underlying data - read table.

rollie@bwsys.net

recordsetclone is a 'scratch' recordset.

 

[ViAn]

I hoped it was so easy, but I have tried:

Set rst2 = myRst.clone

and then tried to operate on rst2. But MsgBox rst2.Updatable gives "False" and I am not allowed to make any changes on the data...

So I am still stuck with my unchangeable recordset :-(

Hope there are some good tips...

-ViAn-

PS: The query whose recordset "myRst" is set to, is a direct ODBC-query.

 

[Rolliee]

if the recordset is bound to the form you use

set rst = me.recordsetclone


Rollie

 

[ViAn]

Thanks for the tip!
The recordset is "bound to" a query and then displayed in a subform. Subsequently I couldn't get VBA to accept

 set rst = Forms!MyForm!MyForm_sub

But: My problem is NOT to get a clone of the recordset. That is easy. The problem is that this clone is also NOT updatable.

I can't understand why "Bill" has made this so difficult...

- ViAn -

 

[Rolliee]

to update from a clone just:

me.bookmark = rs.bookmark ' moves to the right rec

for i = 0 to rs.fields.count - 1
me.fields(i) = rs.fields(i)

next
Voile'


Rollie

 

[ViAn]

Thanks. That tip may be handy some time. But this time I want to DELETE some columns. Not update (but of course, the recordset will have to be updatable in order to get permission to delete).
Another solution would be to build up a new recordset of all columns but the ones to be deleted from the existing recordset, but I do not know how to do that either...

-ViAn-