[AccessD] Change Field Type

Max Wanadoo max.wanadoo at gmail.com
Tue Sep 23 09:46:50 CDT 2008


Rocky,
I am replying from a machine without Access (can you believe that?).
However, I think the clause "Boolean" is incorrect.  I think it is something
liked "Logical" or similar but I cannot quite remember and I cannot test for
it.  Try changing it to Logical and see if that works, if not Google for
this clause.  (It might even be YESNO or YES/NO or BIT)
Good luck
Max

On Tue, Sep 23, 2008 at 1:01 PM, Rocky Smolin at Beach Access Software <
rockysmolin at bchacc.com> wrote:

> 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
> >
> >
> >
> >
> >
> > --
> > 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



More information about the AccessD mailing list