Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Location
    San Antonio, TX
    Posts
    69

    Unanswered: A97: Changing field data type programmatically

    Is there a way to directly change the data type of a field through code? I really don't want to do the whole create field->append field->copy data from old field to new field thing.


    Thanks.
    "Doing stuff is overrated. Take Hitler for example. He did lots but don't we all wish he'd have just stayed home and gotten stoned?"

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251

    Re: A97: Changing field data type programmatically

    if you use ADOX, it takes you seconds.... and you will be able to manipulale the values.... be aware that you can loose some data during conversion (Double --> Integer), during this process you can do something with data you'd loose ( intOriginal * 100 = intFINAL, ..... )


    this is not that long code....


    Dim cat As ADOX.Catalog
    Dim col As ADOX.Column
    Dim conn As ADODB.Connection

    Set cat = New ADOX.Catalog
    cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<DatabasePath>;"

    Set col = New ADOX.Column
    col.Name = "NewColName"
    col.Type = adDouble
    Set col.ParentCatalog = cat
    cat.Tables(<TableName>).Columns.Append col

    Set conn = New ADODB.Connection
    conn.Provider = "Microsoft.Jet.OLEDB.4.0;Data Source=<DatabasePath>;"
    conn.Mode = adModeReadWrite
    conn.Open
    conn.Execute "UPDATE <TableName> SET NewColName = VAL(<OldColName>)" ' Type Conversion Function

    cat.Tables("<TableName>").Columns.Delete ("<OldColName>")
    Set col = Nothing
    Set cat = Nothing
    set conn = Nothing

  3. #3
    Join Date
    Feb 2002
    Location
    San Antonio, TX
    Posts
    69
    I don't use ADOX. Is there another solution?
    "Doing stuff is overrated. Take Hitler for example. He did lots but don't we all wish he'd have just stayed home and gotten stoned?"

  4. #4
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    pure SQL ....

    ALTER TABLE add column
    UPDATE
    ALTER TABLE drop column


    see http://support.microsoft.com/default...;en-us;q128016 for the sample


    jiri

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •