Change DAO DBEngine DataTable's column from DataType dbInteger to dbLong in VB6

Question

I've inherited a legacy VB6 app to maintain, and my vb6 is more than a little rusty...

I've got a DAO Table that had a field of type DAO.DataTypeEnum.dbInteger that needs to be altered to type DAO.DataTypeEnum.dbLong. Is there a shortcut vb6 way of setting this new data type and retaining the existing values, or do I need to create a temp column to store the data, then remove and recreate the column with the new datatype and then migrate the data by hand?

Answer 1

If this is a one time job, you could open the Access database and change the datatype.
Add a comment to this post, otherwise.

EDIT: You could issue an ALTER statement on the database object

CurrentDb.Execute "ALTER TABLE myTable ALTER Column myIntegerColumn Long"

Answer 2

Shahkalpesh's answer is fine if your database engine supports ALTER TABLE ALTER COLUMN. If you are using the Access database engine (Jet .mdb, ACE .accdb, etc) you can use ALTER COLUMN when in ANSI-92 Query Mode (Jet 4.0 and Access 2002 onwards).

In the past I've done it like this entirely through code. The code below converts a string field to a floating-point double without using ALTER COLUMN. It creates a new field with a different name and the correct data type, copies the data, drops the original field, and renames the new field to the original name. You could easily adapt this to do integer to long.

  Dim fld As DAO.Field

  ' Cant just change the type of an existing field. '
  ' Instead have to create the new field with a temporary name, '
  ' fill it with the required data, delete the old MyField field '
  ' and then rename the new field. The renaming has to be done '
  ' with DAO - cant do it through SQL '

  ' Add TEMP_MyField field: required double field. Will be renamed later '
  sSQL = "ALTER TABLE MyTable " & _
         "ADD COLUMN TEMP_MyField DOUBLE NOT NULL "
  dbDatabase.Execute sSQL, dbFailOnError

  ' Copy the MyField values to the TEMP_MyField field '
  sSQL = "UPDATE MyTable SET TEMP_MyField = CDbl(MyField)"
  dbDatabase.Execute sSQL, dbFailOnError

  ' Delete the original MyField field (the text field) '
  sSQL = "ALTER TABLE MyTable DROP COLUMN MyField"
  dbDatabase.Execute sSQL, dbFailOnError

  ' Need to refresh the TableDefs to make sure new field shows up '
  dbDatabase.TableDefs.Refresh

  ' Get a reference to the temporary MyField field we just created '
  Set fld = dbDatabase.TableDefs("MyTable").Fields("TEMP_MyField")

  ' Rename it to the final name we want it to have '
  fld.Name = "MyField"