Programmatically changing field type

[Guest]

I am enclosing the code because I am stuck. One of those
fields is being imported as a number and I want it to be text. This is the
code that I have written but I get the error message " wrong number of
arguments or invalid property assigned".

Dim dbCurr As DAO.Database
Dim tdefCurr As DAO.TableDef
Dim fldCurr As DAO.Field
Dim typCurr As DAO.DataTypeEnum

Set dbCurr = CurrentDb()
Set tdefCurr = dbCurr.TableDefs("New_Billing_Temp")
Set fldCurr = tdefCurr.Fields("Mobile Number")
Set typCurr = fldCurr.Type(adChar)

dbCurr.TableDefs.Refresh

Set tdefCurr = Nothing
Set dbCurr = Nothing

 

[Allen Browne]

You cannot change the field type like that through DAO code.

If you do want to change the field type programmatically, in Access 2000 and
later you can execute a DDL query statement like this:

strSql = "ALTER TABLE MyTable ALTER COLUMN MyText2Change TEXT(100);"
DBEngine(0)(0).Execute strSql, dbFailOnError

For a list of the names to use for the field types, see:
Field type names (JET, DDL, DAO and ADOX)
at:
http://allenbrowne.com/ser-49.html

As that lists suggests, the ad... constants are not designed to work with
the DAO library (as your code example did.)