MS Access SQL, data type change

Question

I keep getting the error "not enough disk space or memory" when I try to change the data type from text to a number in development mode in Access (working with a database up to 2 GB), so I found a workaround mainly by creating a new column, setting the type there data by copying the contents of the old godfathers, deleting the old column and renaming the new column to the name of the old column.

I heard that ALTER TABLE can also be used to change the data type.

Can anyone give me an example of how to use ALTER TABLE to change the integer data type of columns to Number from text,

or does anyone have a better way to change the data type?

Answer 1

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 will look like this:

ALTER TABLE TableName ALTER COLUMN ColumnName INTEGER 

By the way, Column == Field (in Access). If I didn’t miss something.

Answer 2

You might want to read this from MSDN:

http://msdn.microsoft.com/en-us/library/bb177883(v=office.12).aspx

 ALTER TABLE yourTable ALTER COLUMN yourColumn TEXT(10) -- or whatever your new datatype is 

Here is an example from the article:

 Sub AlterTableX2() Dim dbs As Database ' Modify this line to include the path to Northwind ' on your computer. Set dbs = OpenDatabase("Northwind.mdb") ' Add the Salary field to the Employees table ' and make it a Money data type. dbs.Execute "ALTER TABLE Employees " _ & "ALTER COLUMN Salary CHAR(20);" dbs.Close End Sub