Programatically Changing A Table Field Data Type?

[BTilson]

Is there any way to programatically change the data type of a field within a table from VB? I need to compare two tables, and the field in one table is text, but it's number in the other table. I can change them manually, but this needs to be an automated process as much as possible.

Thanks!

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[traingamer]

If you are using a query to compare, you can convert the data 'on the fly' with a val() function.
...where t1.id = val(t2.id)...

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[JoeAtWork]

You might find DAO's TableDef and Field objects of use.
Here's something I copied from the Help file:

Sub TypeX2()

   Dim dbsNorthwind As Database
   Dim fldLoop As Field

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")

   Debug.Print "Fields in Employees TableDef:"
   Debug.Print "  Type - Name"

   ' Enumerate Fields collection of Employees table.
   For Each fldLoop In _
      dbsNorthwind.TableDefs!Employees.Fields
      Debug.Print "    " & FieldType(fldLoop.Type) & _
         " - " & fldLoop.Name
   Next fldLoop

   dbsNorthwind.Close

End Sub

Function FieldType(intType As Integer) As String

   Select Case intType
      Case dbBoolean
         FieldType = "dbBoolean"
      Case dbByte
         FieldType = "dbByte"
      Case dbInteger
         FieldType = "dbInteger"
      Case dbLong
         FieldType = "dbLong"
      Case dbCurrency
         FieldType = "dbCurrency"
      Case dbSingle
         FieldType = "dbSingle"
      Case dbDouble
         FieldType = "dbDouble"
      Case dbDate
         FieldType = "dbDate"
      Case dbText
         FieldType = "dbText"
      Case dbLongBinary
         FieldType = "dbLongBinary"
      Case dbMemo
         FieldType = "dbMemo"
      Case dbGUID
         FieldType = "dbGUID"
   End Select

End Function

My understanding is that you can't change the Type of a Field while it is contained in the table. So you might have to go through a process of:
1. Create the new Field object
2. Set it's properties, including Type and Name (which needs to be different from the field that exists in the table)
3. Append the new Field to the TableDef
4. For all records in the table, set the value of the new field to the value of the old field
5. Delete the old field from the table
6. Rename the new field to the name of the old field

 

[PHV]

You may use DDL:
DoCmd.RunSQL "ALTER TABLE theTable ALTER COLUMN theTextField LONG;"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886