Column Type Conversion for a Table?

  • Thread starter Thread starter xenophon
  • Start date Start date
X

xenophon

I have used ADOX via COM-Interop to create a Jet MDB with a VarChar
(aka Access Text) column type. I would like to change that column type
to a DateTime, but I can't figure out how. What commands do I give to
make that happen?

Thanks.
 
¤
¤ I have used ADOX via COM-Interop to create a Jet MDB with a VarChar
¤ (aka Access Text) column type. I would like to change that column type
¤ to a DateTime, but I can't figure out how. What commands do I give to
¤ make that happen?

You can't change the data type directly. A new column must be created, data copied, old column
dropped and new column renamed:

Dim cnn As New ADODB.Connection

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\My Documents\db1.mdb"

cnn.Execute "ALTER TABLE Table1 ADD COLUMN NewDate Date"
cnn.Execute "UPDATE Table1 SET NewDate = CurrDateText"
cnn.Execute "ALTER TABLE Table1 DROP COLUMN CurrDateText"

Dim cat As New ADOX.Catalog
Set cat.ActiveConnection = cnn

cat.Tables("Table1").Columns("NewDate").Name = "CurrDateText"

cat = Nothing
cnn.Close
cnn = Nothing


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top