Changing Field Properties in Code

Changing Field Properties in Code

When you manually change a field's properties in a table, Access is really performing several actions at once:

Add a new field with the required property;
Copy the data from the existing field to the new field;
Delete the old field.

Therefore, if you want to change a field in code, you will need to do the same. Here is a sample function that uses SQL statements to do this. Obviously, there are properties that can't be set using SQL, so you would need to use DAO instead.

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

You can then call the sub like:

Call sChangeField("tblName","fldName","TEXT(100)")

Which will convert the field called fldName in the table tblName to a text field with a maximum length of 100 characters.

Top

Last modified at 06/06/2006 15:01:39