(Resolved) Delete a field from an Access database

[A441OTA]

Good morning,

Is there a way to delete an entire field from an Access database?

If not, is there a way to replace all the values in this field to Null?

Access 2002, VB6/ADO

The name of the field is static but it's location will not always be the same, nor will the names of the other fields. This is a very messy database that I'm trying to clean up so that I can get the information that I need from it.

Thanks,
Mary

[A441OTA]

I kept getting error messages, so I kept trying..... sorry for the blank threads, I don't know how to delete them.

Thanks,
Mary

[maxl]

Not sure it that helps but you can always use a connection to execute an SQL statement.

Dim SQL as String
Dim Conn as New Adodb.Connection
 
'Open your conn here with the right connection string
Conn.Open ConnectionString
 
 
'Drop the field
SQL="ALTER TABLE table_name DROP COLUMN field_Name"
Conn.Execute SQL
'Set all to NULL
SQL="UPDATE table_name SET field_name=NULL"
Conn.Execute SQL
Conn.Close
Set Conn = Nothing
'You can always replace the field and table name with variables to do this dynamicly

Hope that helped

[A441OTA]

Thank you Max!!!

I wasn't sure if you could delete a field -- if this works you are a lifesaver!!!! I won't be able to try it today, but will try as soon as possible. This is the messiest database and cleaning it up is imperative to finding the data needed!!!

I owe you a beer next time you're in Virginia!!!!