Change a field's data type through code

[leassaf]

I'm trying to change a field's data type through code. This field contains date values but its current data type is "text". Anyone can supply the code to perform this task. Thanks,

Assaf

 

[Guest_imported]

I hope this helps

Sub ChangeFieldDataType()
Dim dbs As Database, tdf As TableDef
Dim fld As Field

' Return reference to current database.
Set dbs = CurrentDb
' Return reference to Employees table.
Set tdf = dbs.TableDefs!Employees
' Set Field object.
Set fld = tdf.Fields("Field1&quot
' Set field properties.
fld.Type = dbText

End Sub

 

[leassaf]

I tried your code in the first place (I changed fld.Type = dbText to fld.Type = dbDate) but what I got and still get is :

Run-time error '3219':
Invalid operation.

How do I bypass that???

Assaf

 

[scking]

Time out! There should be no reason to change the design of the table within code. If you do there is something seriously wrong. Consider the VBA built in functions for changing variable datatypes.

CStr()
CDate()
CInt()
CDbl()

Example:
Dim strVal As String
strVal = CStr(intValue)

Steve King Growth follows a healthy professional curiosity

 

[lachie]

There's no great reason why you shouldn't change the design of a database in code.

In many respects - it may be your only option - you're writing a database conversion tool that changes the database schema on a database that you have shipped out to clients - it's not feasible or professional to get users to change fields for you.

Rather, you should create the new field with a temporary name.

copy the required data into this (since you are using jet - you can use the functions cstr() cint() and the like)

(hint: UPDATE blah SET thefieldyoujustadded = CStr(thefielthatisgivingyougrief))

Then drop your original field.

Then rename your new field so that it has the correct name