Changing Field Datatype

[Guest]

I am trying to give the users the facitility to change the datatype behind a
field via a form using the following code:

Private Sub ChangeDataType(Fld As String, FldType As Integer)

Dim db As Database
Dim tbl As TableDef

Set db = DBEngine(0)(0)
Set tbl = db.TableDefs("TblUserFieldEmployeeDetails")

tbl(Fld).Type = FldType

Set db = Nothing
Set tbl = Nothing
End Sub

The problem is that the datatype is read only. Is there another or better
way of ding this?

Many thanks

 

[Rick Brandt]

I am trying to give the users the facitility to change the datatype
behind a field via a form using the following code:

Private Sub ChangeDataType(Fld As String, FldType As Integer)

Dim db As Database
Dim tbl As TableDef

Set db = DBEngine(0)(0)
Set tbl = db.TableDefs("TblUserFieldEmployeeDetails")

tbl(Fld).Type = FldType

Set db = Nothing
Set tbl = Nothing
End Sub

The problem is that the datatype is read only. Is there another or
better way of ding this?

Many thanks

With the method you are using you need to create a new field of the type you
want, update all of the data from the old field into the new one, drop the
old field, and then change the name of the new field to match the old one.

When you change a DataType in the Access GUI it is actually doing all of the
above for you in the background so it's not obvious what is required.

Newer versions allow for the use of DDL SQL Statements "ALTER TABLE..." that
you might investigate as well.

 

[Guest]

Thanks for the reply that was very helpful - I never knew about ALTER TABLE.

I can now change the type with the following:
strSQL = "ALTER TABLE TblUserFieldEmployeeDetails ALTER COLUMN " &
fldName & " " & strFieldType
db.Execute strSQL

But have ran into another problem in that whilst the table is immediately
changed (looking at it in design view), none of the forms will recognise the
new datatype without completely exiting the databse and reopening it.

 

[Rick Brandt]

Thanks for the reply that was very helpful - I never knew about ALTER
TABLE.

I can now change the type with the following:
strSQL = "ALTER TABLE TblUserFieldEmployeeDetails ALTER COLUMN " &
fldName & " " & strFieldType
db.Execute strSQL

But have ran into another problem in that whilst the table is
immediately changed (looking at it in design view), none of the forms
will recognise the new datatype without completely exiting the
databse and reopening it.

Sorry, I can't help you there and since this is not the type of thing that
would typically be done in an application after it is in production you
might have a hard time finding anyone who is doing something similar.