delete field from DAO.recordset

[formerTexan]

Hi,

Can I programmatically delete a field from a DAO recordset?
I see that the fields collection has a delete method, but I am at loss as to what to do with it.

The object browser indicates a "name as string" parameter is needed and I presumed this meant the name of the field. But the following won't fly.

rs.Fields.delete "fieldname"

Any ideas?

Thanks
Bill

 

[Remou]

Would SQL suit?
strSQL = "Alter Table tblTable Drop Column Field1"

 

[fneily]

And if I may add a line:

strSql = "ALTER TABLE [MyTable] DROP COLUMN [DeleteMe];"
DBEngine(0)(0).Execute strSql, dbFailOnError

 

[Golom]

... with a word of warning. The above suggestions permanently remove a field from a table. If you just want to get rid of field in a recordset then

[li]Redefine the recordset with different SQL OR[/li]
[li]Create a new recordset based on the original excluding that field OR[/li]
[li]Hide the field in the display[/li]

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Golom]

... with a word of warning. The above suggestions permanently remove a field from a table. If you just want to get rid of a field in a recordset then

[li]Redefine the recordset with different SQL OR[/li]
[li]Create a new recordset based on the original excluding that field OR[/li]
[li]Hide the field in the display[/li]

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Zion7]

Tex, maybe this,

CurrentDb.TableDefs("tblCountry").Fields.Delete "txtCapital"

 

[formerTexan]

Thanks folks,

Those are fine suggestions for permanent deletion of a field from a table, but I had hoped for a quick way to drop a field from an existing recordset.

In this case I am dumping a form's recordsetclone into Excel worksheets and thought it would be a neater approach to get rid of unwanted fields up front. Guess it is back to Plan B.

Cheers,
Bill

 

[Zion7]

Sorry about the confusion there Tex, but wouldn't
any of Golom's suggestions work?

 

[formerTexan]

Hello Zion7,

Thanks much for the followup. Now that you prompted me, I guess I could rebuild a second recordset based on selected fields from the 1st recordset. I haven't tried this before, but don't recall any reason why it can't be done.

As I mentioned. I am dumping the recordset contents into an Excel worksheet. This involves looping through the recordset fields to define the Excel columns. I just head off the unwanted fields at this point. I'm not sure that building a 2nd recordset would be any more efficient in terms of coding time or speed, but it is worth a look

Originally I thought it would be a nice toy to have a method allowing deletion of fields from a recordset. And it would be a shame if one existed and I've been overlooked it. Hence the post to you and the other Tek-tips folks. As usual, even if the answer aren't what I wanted, they are good food for thought.

Cheers,
Bill