Deleting fields from table

[tekkyun]

Can anyone point me to some code which will delete a field from a recordset?
I have tried to use Delete Method (Fields.Delete field) but it won't allow deletion whilst recordset is open. If I close the recordset then how do I refer to the fields to delete?

What I am trying to do is:-
'Open recordset
For each field in Rs.fields
If field.name = "xyz" Then 'Remove this field form the recordset
Next field

 

[RivetHed]

What does your recordset object represent? Is it a recordset you've generated on the fly or is it a pre-existing table or query in your database?

 

[JoeAtWork]

Well, I have found the only way I can delete a field is after I close the recordset - and if I reopen the recordset the field is restored again.

Public Sub RemoveField()
    Dim rs As ADODB.Recordset
    
    Set rs = New ADODB.Recordset
    
    rs.Open "SELECT ID, Stuff FROM tblStuff", CurrentProject.Connection
        
    MsgBox "Field count (after open) = " & rs.Fields.Count
    
    rs.Close
    
    rs.Fields.Delete 1
    
    MsgBox "Field count (after close and delete) = " & rs.Fields.Count
    
    rs.Open
    
    MsgBox "Field count (after reopen) = " & rs.Fields.Count
    
    rs.Close
    
End Sub

Please satisfy my curiosity as to why you want to do this, and what you want to accomplish. Deleting a recordset's field does nothing to the database - so I don't see the point.

 

[tekkyun]

thanks for quick response guys,

The recordset is a table that is generated on the fly by users selecting options on a form.
This table is then used as a recordsource for a subform.
I am currently switching the sourceobject of the subform to another table, then updating the temp table with the results of a crosstab query. This bit works fine. I just need to delete unwanted fields from the temp table and then switch the sourceobject property back to the temp table for the users to see.

Am I making sense?

 

[JoeAtWork]

I just need to delete unwanted fields from the temp table

I would not try this through a RecordSet object, as it represents data from a table, not the table itself.

Two possible methods I can think of.
1. Via the Fields collection in a DAO TableDef object. If you are dealing with a linked table, you would need to create a reference to the backend database.
2. Using an SQL ALTER TABLE statement