Menu
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?
781
You can modify the data type of a column in SQL Server by using SQL Server Management Studio or Transact-SQL. Modifying the data type of a column that already contains data can result in the permanent loss of data when the existing data is converted to the new type.
When you type the $ sign, Access automatically encloses the string you type in quote marks. Verify that the data type of each pair of joined fields in the query is the same. If not, change the data type of one of the joined fields to match the data type of the other so you don't get the mismatch error.
Explanation: Type casting refers to changing an variable of one data type into another. The compiler will automatically change one type of data into another if it makes sense. For instance, if you assign an integer value to a floating-point variable, the compiler will convert the int to a float.
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.
122
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
