MS Access SQL, changing data type

ms-access, sql, sqldatatypes, types

Solution

This article can help you with ALTER TABLE: http://msdn.microsoft.com/en-us/library/bb177883(v=office.12).aspx

So, in your case the statement would be:

ALTER TABLE TableName ALTER COLUMN ColumnName INTEGER

By the way, Column == Field (in Access). Unless I'm missing something.

Problem

I keep getting a "not enough disk space or memory" error when trying to change the data type from text to a number in design mode in Access (working with a close to 2 gb database), so I found a workaround by basically creating a new column, setting the data type to number there, copying the old coumns contents in, deleting the old column and renaming the new column to the old column's name. I heard that ALTER TABLE can be used to change the data type as well. Would someone be able to give me an example of how to use ALTER TABLE to change a whole columns data type to Number from text, or does anybody have a better way to change the data type?

Original source