[AccessD] Change Field Type

Well what a surprise the Online help was wrong (for A2K) or I read it wrong,
but you really can change a column definition in one step with the DDL. For
example this works fine...

CurrentDb.Execute "alter table Projections_tbl alter column nYearNumber
currency" 

So the question is why does Rocky's SQL fail? Substituting "currency" with
"Boolean" in the above gives the same error that Rocky sees, so there must
be some other keyword that the DDL uses to mean Boolean. Google, google....
and that keyword is YESNO

Thus Rocky needs to use

dbs.Execute "ALTER TABLE [MatterActions] ALTER Column StartBase YESNO"

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Tuesday, September 23, 2008 3:25 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Change Field Type

Hi Rocky,

don't know if these links are any use:-

http://www.blueclaw-db.com/alter_table_ddl.htm
http://office.microsoft.com/en-us/access/HP010322071033.aspx

Max



On Mon, Sep 22, 2008 at 8:56 PM, Shamil Salakhetdinov <
shamil at smsconsulting.spb.ru> wrote:

> Hi Rocky,
>
> I'd think you have to:
>
> - 1) create a new column with required data type;
> - 2) set values of this new column using update SQL clause;
> - 3) delete old column;
> - 4) rename new column to the old column name;
> - 5) change ordinal position for the renamed column (can be done in 
> step
> (4)
> - 6) set indexes for the new column if needed...
> - 7) (re-)define relationships;
>
> I can be wrong but I suppose that changing column type in code 
> directly in one step isn't possible...
>
> As we all know it's possible to change column type in one direct step 
> in design mode but I'd suppose that "under the hood" the above 
> sequence of actions is performed...
>
> Thank you.
>
> --
> Shamil
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky 
> Smolin at Beach Access Software
> Sent: Monday, September 22, 2008 11:27 PM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] Change Field Type
>
> Dear List:
>
> I am trying to change the type of a field 'StartBase' in table 
> 'MatterActions' from Number to Boolean.  I've tried two approaches:
>
> 1) dbs.Execute "ALTER TABLE [MatterActions] ALTER Column StartBase
Boolean"
> givers the error 3381 There is no field named 'Boolean' in table 
> 'MatterActions'
>
> 2) Set tdf = dbs.TableDefs("MatterActions") 
> tdf.Fields("StartBase").Type = dbBoolean
>
> gives the error invalid operation on the second line.
>
> Does anyone know the correct code to change this field's type?
>
> MTIA
>
> Rocky
>
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com