Delete column in open recordset

[KMKelly]

I am trying to go through a table check several rows to see if they contain values and if not delete the field from the table. This can end up being for approx 50 fields it needs to check. The fields are all named Field1, Field2 etc...

I tried two things so far: (1) find a field I want to delete and use the dbs.Execute command to do an ALTER TABLE query and DROP the appropriate field; (2) use the delete method on the fields collection for the recordset, ie. grid.fields.delete "Field20". In both cases it won't let me delete the field because I have an open recordset. The only other possibility I can think of is to have to close the recordset, do the delete and then reopen it, reset the index etc..., but that seems extra-ordinarily cumbersome.

Does anyone know of anyway to do this?

Thanks.

Kris

 

[willir]

Kris

You are correct. You have to have exclusive rights to the table when you are playing with the schema.

Look at LockType

http://support.microsoft.com/default.aspx?kbid=281998

http://www.developer.com/tech/article.php/721911

And RecordLocks

 

[cmmrfrds]

Do you want to check all rows to see if a field is empty in all the rows? Do you want to iterate through all the fields in the table?

You could iterate through the fields collection for the table and build a dynamic query to check each field for being empty. If the field is empty, then save the field name in an array or collection. Then, iterate through the array and build a dynamic alter sql command for each field.