|
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.
|
