Change Text datatype to Number Datatype in VB

[bryn30]

Is there a way to change the datatype of a field from Text using VB?

 

[RickSpr]

You can do it easily in the user interface, of course, but doing it in VBA is considerably more work. You have to create a new field, copy the data to it, and delete the original field. (When you do this in the user interface, this all happens "behind the scenes" for you.)

In what follows, I'm assuming that you are familiar with DAO, and that your table is actually present in the database. If the table is linked, a modification is necessary. If the table is accessed via ODBC, this won't work at all.

To do it all with DAO:
1. Access the TableDef for the table.
2. Create a Field object with the numeric datatype and a temporary name. Give it the same Ordinal Position property value as the original field.
3. Append the new Field to the TableDef's Fields collection.
4. Set the TableDef object variable to Nothing. (I'm not sure this is really necessary, but it's easier just to do it than to test whether you need to.)
5. Open a Recordset over the table and loop through the rows, copying the text field to the new field. Close the recordset.
6. Access the TableDef again.
7. Delete the original field name from the Fields collection.
8. Assign the original name to the new Field object by changing its Name property. (You can do this to a Field appended to a TableDef.)

You can also do this partly with DDL:
1. Create and run (using DoCmd.RunSQL) a statement to add the new column. For example,
ALTER TABLE tablename ADD COLUMN fieldname LONG (or SHORT) NOT NULL
NOT NULL is optional, and corresponds to setting the Required property to Yes. Note that you can't specify the ordinal position or any other properties this way.
2. Copy the data from the original column to the new one as in step 5 above.
3. Create and run a statement to delete the original column.
ALTER TABLE tablename DROP COLUMN original fieldname
4. Access the TableDef and rename the Field by assigning the original name to its Name property. You could also change the Ordinal Position and any other properties at this time.

If the table is linked, the necessary modification is in how you access the TableDef. The TableDef in your CurrentDB can't be used, because it only represents a local copy of the real table's definition. Instead, you need to open the database that contains the real table, using OpenDatabase to set a Database object variable. You can then access the TableDefs collection of that Database object. You can't use the DDL method in this case; you must use DAO. Rick Sprague

 

[bryn30]

Thanks RickSpr
I think I have it... it seems to work here is the final Code

Sub AlterFieldType(TblName As String, AlterTempField As String, NewDataType As String)
On Error GoTo Err_AlterFieldType
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()


' Create a dummy QueryDef object.
Set qdf = db.CreateQueryDef("", "Select * from SIMSUpdate&quot

' Add a temporary field to the table.
qdf.SQL = "ALTER TABLE [" & TblName & "] ADD COLUMN AlterTempField " & NewDataType
qdf.Execute

' Copy the data from old field into the new field.
qdf.SQL = "UPDATE DISTINCTROW [" & TblName & "] SET AlterTempField = A_SIM_NUM"
qdf.Execute

' Delete the old field.
qdf.SQL = "ALTER TABLE [" & TblName & "] DROP COLUMN A_SIM_NUM"
qdf.Execute

' Rename the temporary field to the old field's name.
db.TableDefs("[" & TblName & "]&quot.Fields("AlterTempField&quot.Name = "A_SIM_NUM"
' Clean up.
UpdateNASC

Exit_AlterFieldType:
Exit Sub

Err_AlterFieldType:
MsgBox Err.Description
Resume Exit_AlterFieldType

End Sub

Sub UpdateNASC()
On Error GoTo Err_UpdateNASC

DoCmd.SetWarnings False
DoCmd.OpenQuery "SimsUpdate A"
DoCmd.OpenQuery "SimsUpdate Z"
DoCmd.SetWarnings True

Exit_UpdateNASC:
Exit Sub

Err_UpdateNASC:
MsgBox Err.Description
Resume Exit_UpdateNASC

End Sub