CODEWITHSUNDEEP
sqlms-accesstypessqldatatypes
BadgerBeaz
BadgerBeaz

Reputation: 393

MS Access SQL, changing data type

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?

Upvotes: 6

Views: 19334

Answers (2)

Taryn
Taryn

Reputation: 247810

You might want to read up on 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 function 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

Upvotes: 1

Igor Turman
Igor Turman

Reputation: 2205

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.

Upvotes: 7

Related Questions