How to Change Column Type in MS Access Using VB6 Programming

It's quite troublesome when we are maintaining some Access database that is located in remote area. Sometimes we need to change column type of some column because we had configured it improperly at the first time. Through some exploration and after trying for searching the solution for this kind of need, I found some useful information that could be beneficial for you when you have this need in the future. The solution has been tested by me on Access 97. What we need to do?

You can using DAO in VB to run this function. First is defining one DAO connection to your Access database. Second is putting the following code in your program:

(For example if you had created DAO as DB, the field will be changed is field99, currently the column type is Integer and you have to change it into Double)

DB.execute "ALTER TABLE [table name] ADD COLUMN fieldx Double"
DB.execute "UPDATE [table name] SET fieldx = field99
DB.execute "ALTER TABLE [table name] DROP COLUMN field99
DB.execute "ALTER TABLE [table name] ADD COLUMN field99 Double"
DB.execute "UPDATE [table name] SET field99 = fieldx
DB.execute "ALTER TABLE [table name] DROP COLUMN fieldx

That's all. After you have run the code, I'm sure the column type of field99 will be changed into Double.

Cheers.