Deleting table field in Access

[Firsttimeprogrammer]

Hi, I'm only new to programming. I want to delete a field and its data from a table. I don't want any other fields affected. I need help writing this code in a module. Can any one be kind enough to help.

I know how to do this in design or datasheet view, but in this case l have to write the code in a module.
I'm using Access 2.0.

Also can anybody tell me how to rename a table field.

What I've been doing; I created a new field in a table that worked okay. Now i need to delete a field and then rename the new field i created to the one i deleted.

Thank you.
Kevin

 

[3587]

This code will create a temp field, take the data from the old field and put it into the new temp field the create a new field and put the data into it then delete the new temp field. While you are learning use this code on test data only! The code works in Access 97/2000 not tested in 2.0?

Good Luck,

 

[3587]

This code will create a temp field, take the data from the old field and put it into the new temp field the create a new field and put the data into it then delete the new temp field. While you are learning use this code on test data only! The code works in Access 97/2000 not tested in 2.0?

Good Luck,

Dim MyWorkspace As Workspace, MyDatabase As DATABASE
Dim MyField As Field
Dim MyTableDef As TableDef


Set MyWorkspace = DBEngine.Workspaces(0) ' Get current workspace.
Set MyDatabase = MyWorkspace.OpenDatabase(Forms!frmUpdate!txtPath) ' Open database.
Set MyTableDef = MyDatabase![tblTasks]

Set MyField = MyTableDef.CreateField("temp", dbText, 255)

On Error GoTo Err_tblTasksFieldSize
MyTableDef.Fields.Append MyField ' Append field to collection.

'move the values
MyDatabase.Execute "UPDATE DISTINCTROW tblTasks SET tblTasks.temp = [tblTasks]![Description];"

'drop the old field
MyDatabase.Execute "ALTER TABLE tblTasks DROP COLUMN Description;"
MyDatabase.TableDefs.Refresh

'change the origional field size and add it back to the table
Set MyField = MyTableDef.CreateField("Description", dbText, 255)
MyTableDef.Fields.Append MyField

'move the values back to the edited field
MyDatabase.Execute "UPDATE tblTasks SET tblTasks.Description = [tblTasks]![temp];"

'delete the temporary field
MyDatabase.Execute "ALTER TABLE tblTasks DROP COLUMN temp;"
MyDatabase.TableDefs.Refresh