I want to delete fields

[Deleted member 30250]

I want to delete redundant fields from tables after I have imported them (from dbase III tables maintained in a gruesome Lotus Aproach application - don't ask - so I don't see any way of avoiding importing all the fields in the first place).

It looks so simple:
Fields.Delete CSI_Credit.CreditMemo
. . . and so on, then:
Fields.Update

I am an old programmer who frequently feels very stupid making a mess of VBA. I simply cannot get my head round when I need to put things in quotes, brackets, two sets of quotes, make-it-a-string-variable-and-use-that-instead-of-a-literal, no-you-idiot-it's-a-dot-not-a-!, etc., etc.. So somebody please tell me what elementary, stupid, dimwitted thing am I doing wrong?

 

[Moniker]

You can't perform a delete operation on an open table. In other words, you need to get to the table in its closed form, using the TableDefs collection.

It looks like this:

CurrentDb.TableDefs("Your_Table_Name_Here").Fields.Delete "Name_Of_Field_To_Delete"

If you try to open the recordset and then access the Field's Delete method, VBA will return an error.

HTH

 

[lark_tilly]

I have the Microsoft DAO 3.6 Object Library referenced.

Private Sub cmdDeleteExtraFields_Click()

Dim db As Database
Dim td As TableDef
Set db = CurrentDb

Set td = db.TableDefs("table name")
td.Fields.Delete "Name of field to delete"

End Sub

Hope this helps!

 

[Deleted member 30250]

Thank you to you both.

I tried both ways, but the redundant field is still there. I get no error messages.

Moniker's way says:
CurrentDb.TableDefs("CSI_Credit").Fields.Delete "CERDITMEMO"

lark_tilly's way says:
Dim db As Database
Dim td As TableDef
Set db = CurrentDb

Set td = db.TableDefs("CSI_Credit")
Set td = db.TableDefs
td.Fields.Delete "CERDITMEMO"

In case it is relevant, this is happening at the end of an on-click subroutine that imports the tables - the existing tables are deleted, then re-created from the dbase tables. I'm trying to build 'tidying up' code - remove blank or invalid records etc., and it would be nice to ditch the many irrelevant fields just for clarity and brevity.

 

[chergh]

I would just use SQL:

db.execute "ALTER TABLE CSI_credit " & _
           "DROP COLUMN CERDITMEMO;"

On another note you dont have any constraints on this field do you? (primary key, foreign key establishing a relationship between tables, field can only contain unique values?)

 

[Deleted member 30250]

Still no luck, and no messages either.

 

[chergh]

In the visual basic editor goto Tools > Option and select the general tab. Under error handling select "break on all errors" and try running the code again and let us know if you get an error message.

 

[Deleted member 30250]

Yee haa! But why?

db.Execute "ALTER TABLE CSI_credit DROP COLUMN CERDITMEMO; worked today. Yesterday it didn't. Don't you just hate it when that happens?

Could it be something to do with having closed and re-opened? I'll try closing everything and running it again.

Thank you, people.