Modifying a Data Type

[jim treitman]

I need to modify a table structure converting a field data
type from TEXT to INTEGER. I Tried using the ALTER
statement as follows with no success

ALTER TABLE (tablename)
ALTER COLUMN (FieldName) INTEGER

I get a syntax error

 

[Michel Walsh]

Hi,


Try, in the immediate debug window

CurrentProject.Connection.Execute "ALTER TABLE tableName ALTER COLUMN
columnName INTEGER "


That is a Jet 4.0 extension and it is not available through DAO, which
includes the query designer.


Hoping it may help,
Vanderghast, Access MVP

 

[jim treitman]

Thanks for the suggestion.

I must be doing something wrong.

I get a a Compile Error

Expected: end of statement

From your example, am I to assume that I leave the
quotation marks in our out?

In my case, the table is called tblMonthSales and the
column is SALES-01, so my code looks like this:

CurrentProject.Connection.Execute "ALTER TABLE
tblMonthSales ALTER COLUMN
SALES-01 INTEGER "

Thanks in advance,

-Jim

 

[Van T. Dinh]

Try:

CurrentProject.Connection.Execute
"ALTER TABLE tblMonthSales
ALTER COLUMN [SALES-01] INTEGER"

 

[Gary Walter]

Hi Jim,

In addition to Van's on-the-mark reply
with the needed brackets.....

I believe Access 200x does support the ALTER
COLUMN clause of ALTER TABLE in DAO
and in the query designer
*if no relationship exists on the COLUMN *

(it can be indexed, but you will have to drop any
constraint on the column first, if one exists, before
you can alter it).

(Access 97 did not support ALTER COLUMN)

So, if you are running Access 200x and there
is no constraint on "[SALES-01]," you should
be able to just type Van's sql into the query
designer, save it, and run it.

I believe the DEFAULT clause of ALTER
COLUMN is the one you have to watch out
for because you can only run it through ADO.

Happy Holidays,

Gary Walter

Try:

CurrentProject.Connection.Execute
"ALTER TABLE tblMonthSales
ALTER COLUMN [SALES-01] INTEGER"


--
HTH
Van T. Dinh
MVP (Access)

Thanks for the suggestion.

I must be doing something wrong.

I get a a Compile Error

Expected: end of statement

From your example, am I to assume that I leave the
quotation marks in our out?

In my case, the table is called tblMonthSales and the
column is SALES-01, so my code looks like this:

CurrentProject.Connection.Execute "ALTER TABLE
tblMonthSales ALTER COLUMN
SALES-01 INTEGER "

Thanks in advance,

-Jim

 

[Gary Walter]

Hi Jim,

In addition to Van's on-the-mark reply
with the needed brackets.....

I believe Access 200x does support the ALTER
COLUMN clause of ALTER TABLE in DAO
and in the query designer
*if no relationship exists on the COLUMN *

(it can be indexed, but you will have to drop any
constraint on the column first, if one exists, before
you can alter it).

(Access 97 did not support ALTER COLUMN)

So, if you are running Access 200x and there
is no constraint on "[SALES-01]," you should
be able to just type Van's sql into the query
designer, save it, and run it.

I believe the DEFAULT clause of ALTER
COLUMN is the one you have to watch out
for because you can only run it through ADO.

Happy Holidays,

Gary Walter

Try:

CurrentProject.Connection.Execute
"ALTER TABLE tblMonthSales
ALTER COLUMN [SALES-01] INTEGER"


--
HTH
Van T. Dinh
MVP (Access)

Thanks for the suggestion.

I must be doing something wrong.

I get a a Compile Error

Expected: end of statement

From your example, am I to assume that I leave the
quotation marks in our out?

In my case, the table is called tblMonthSales and the
column is SALES-01, so my code looks like this:

CurrentProject.Connection.Execute "ALTER TABLE
tblMonthSales ALTER COLUMN
SALES-01 INTEGER "

Thanks in advance,

-Jim