[AccessD] Change Field Type

Hi Rocky

Try with:

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

/gustav

>>> rockysmolin at bchacc.com 23-09-2008 14:01 >>>
Max:

In the Microsoft Office article it says I can do what I'm trying to do but
didn't seem to work. It says:

****************************
Use ALTER COLUMN to change the data type of an existing field. You specify
the field name, the new data type, and an optional size for Text and Binary
fields. For example, the following statement changes the data type of a
field in the Employees table called ZipCode (originally defined as Integer)
to a 10-character Text field:

ALTER TABLE Employees ALTER COLUMN ZipCode TEXT(10)
****************************

I tried: 

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

And got the error 3381 There is no field named 'Boolean' in table
'MatterActions' 


Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com
www.bchacc.com 
 
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Tuesday, September 23, 2008 12: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