Can someone help, I am looking for a bit of code or macro that will; on a given date or set time lapse delete a field in a database. Any help would be appreciated Dave

:confused: Note: Database do not have fields, but tables do.


Here is a function to alter a table. See the DROP COLUMN part to see how to delete a field.



Sub sChangeField(strTableName As String, strFieldName As String, strFieldType As String)
Dim db As Database
Dim strSQL As String
Set db = CurrentDb
' Add a new field, called TempField, of the correct type in the table
strSQL = "ALTER TABLE [" & strTableName & "] ADD COLUMN [TempField] " & strFieldType & ";"
db.Execute strSQL
' Copy the data from the existing column to the new column
strSQL = "UPDATE DISTINCTROW [" & strTableName & "] SET [" & strFieldName & "]=[TempField];"
db.Execute strSQL
' Delete the existing field from the table
strSQL = "ALTER TABLE [" & strTableName & "] DROP COLUMN [" & strFieldName & "];"
db.Execute strSQL
' Change the new field name back to the existing field name
db.TableDefs(strTableName).Fields("TempField").Name = strFieldName
Set db = Nothing
End Sub





To use the above:




Private Sub Command14_Click()
Call sChangeField("Table1New", "FieldOne", "NUMBER")
End Sub




I would be very careful doing this. You need to make sure that the table is not in use.

You could use the Task scheduler to run the code. See: How to Start Access by Using the Windows NT Schedule Service (http://support.microsoft.com/kb/230575)

Why would one need to do this on a timed basis (given date or time lapsed)? It doesn't sound right to me. Sounds more like trying to add a "time bomb" into the works, if you ask me.