Delete field from BE table by Code

[EddyLLC]

I use the following code to add field to a backend table. Is there a way I can modify this code to delete a field from a BE table? I want to delete the entire field not just the data within it. I have never done this before so in tinkering aroung I've noticed there is no .DeleteField that I can pop into the code below. Can somebody point me in the right direction? Thanks


Set wrkDefault = DBEngine.Workspaces(0)
Set dbsUpdate = wrkDefault.OpenDatabase(DatabasePath, True)
Set tdfUpdate = dbsUpdate.TableDefs("tbl1")
With tdfUpdate
Set tdfField = .CreateField("Field1", DB_BOOLEAN)
.Fields.Append tdfField
End With

 

[Golom]

Try

dbsUpdate.Execute "ALTER TABLE tbl1 DROP COLUMN myField"

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

 

[EddyLLC]

Thanks for the reply. I used this code to attempt to delete the field in question. It is run on the opening of the main form. This code generates error 321 telling me it can't lock tbl1 because it is in use by another user. I am the only one in the system. Is there a way around this?

Sub Delete_Field()

Set dbLocal = CurrentDb()
DatabasePath = ap_GetDatabaseProp(dbLocal, "LastBackEndPath")
DatabaseName = "BACKEND.MDB"
DatabasePath = DatabasePath & DatabaseName
Dim strTbl As TableDef
Dim strSQL As String
Dim dbBackend As DAO.Database
Set dbBackend = OpenDatabase(DatabasePath)

strSQL = "ALTER TABLE tbl1 Drop COLUMN RelDateH"
dbBackend.Execute strSQL

Exit_Delete_Field:
Exit Sub
Err_Delete_Field:
Error_modUpdate_Backend

End Sub

 

[Golom]

You aren't the only user.

You are the user that has that back-end table linked to the front-end and then (wearing your other disguise) you are the user that has established a separate connection to the back-end via dbBackEnd for your own nefarious purposes. You may need to drop the link to the back end; do your column drop; and then re-establish the link.

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

 

[EddyLLC]

Foiled again! It's not that important so I will leave the field and my nefarious deeds for a later time. Thanks for the response.

 

[Zion7]

is the main form bound. if it is, it means the back-end is open.
any DDL manipulations I do, are from the switchboard,
before any bound fields or forms are opened.
You may nered to choose, a different event, to run procedure?