Change field data type in table

[Guest]

I have successfully imported a table from Excel into Access with the
TransferSpreadsheet method. However, there are two fields whose data types I
want to change. One is a number (double) that I want to change to a number
(integer) and the second is a number (double) that I want to change to a
currency.

What is the VBA code to change a data type?

For example:
dim db as database
dim rst as recordset

rst = db.OpenRecordset("Table")
rst("ID").DataType = Integer
rst("Salary").DataType = Currency
rst.Update
rst.Close


Thanks.

 

[Allen Browne]

Exeucte a DDL query statement like this:
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN ID SHORT;"
DBEngine(0)(0).Execute strSql, dbFailOnError

For a list of the names to use in the DDL query compared to the names used
in the Access interface, see:
Field type reference - names and values for DDL, DAO, and ADOX
at:
http://allenbrowne.com/ser-49.html

 

[Guest]

Perfect!

Thank you!

Exeucte a DDL query statement like this:
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN ID SHORT;"
DBEngine(0)(0).Execute strSql, dbFailOnError

For a list of the names to use in the DDL query compared to the names used
in the Access interface, see:
Field type reference - names and values for DDL, DAO, and ADOX
at:
http://allenbrowne.com/ser-49.html