[AccessD] Change Field Size

Paul Hartland (ISHARP) paul.hartland at isharp.co.uk
Mon Dec 5 09:05:29 CST 2005


Wouldn't you have to run the change field size SQL on the BE table then
refresh the linked tables in your current project ? 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin -
Beach Access Software
Sent: 05 December 2005 14:51
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Change Field Size

Gotcha...run-time error 3611...cannot execute data definition statements on
linked data sources...

Is there a way to do this on a linked table?  I want to send the patch with
the front end (E-Z-MRP) to extend the length of a field on startup of the
program.

T&R

Rocky

----- Original Message -----
From: "Martin" <mwp.reid at qub.ac.uk>
To: <accessd at databaseadvisors.com>
Sent: Monday, December 05, 2005 6:00 AM
Subject: Re: [AccessD] Change Field Size


> Rocky
>
> Dim strSql As String
> strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(33);"
> DBEngine(0)(0).Execute strSql, dbFailOnError
>
>
>
>    From: "Rocky Smolin - Beach Access Software"<bchacc at san.rr.com>
>    Sent: 05/12/05 13:43:38
>    To: "Access Developers discussion and problem 
> solving"<accessd at databaseadvisors.com>
>    Subject: Re: [AccessD] Change Field Size
>
>    Martin:
>
>    I can't find any references to Alter.  It's VBA, yes?
>
>    Rocky
>
>    ----- Original Message ----- 
>    From: "Martin" <mwp.reid at qub.ac.uk>
>    To: <accessd at databaseadvisors.com>
>    Sent: Sunday, December 04, 2005 10:11 AM
>    Subject: Re: [AccessD] Change Field Size
>
>
>    > Rocky
>    >
>    > You may have to use an Alter statement  also note below from web.
>    >
>    >
>    > Using DAO, you have to CreateField() of the new size, execute an 
> UPDATE
>    > query to populate it, and then remove the old field.
>    >
>    > Martin
>    >
>    >
>    > -----Original Message-----
>    >    From: "Rocky Smolin - Beach Access Software"<bchacc at san.rr.com>
>    >    Sent: 04/12/05 17:44:36
>    >    To: "AccessD at databaseadvisors.com"<AccessD at databaseadvisors.com>
>    >    Subject: [AccessD] Change Field Size
>    >      Dear List:
>    >
>    >    Close, but no cigar.
>    >
>    >    I need to change the length of a field through code.  I've got:
>    >
>    >    Set wrk = DBEngine.Workspaces(0)
>    >    Set db = wrk.OpenDatabase(gstrDatabaseName)
>    >    Set tdf = db.TableDefs("tblPODetail")
>    >
>    >
>    >        Set fld = tdf.Fields("fldPODPartDescription")
>    >        fld.Properties("AllowZeroLength") = True
>    >        fld.Properties("FieldSize") = 255
>    >        tdf.Fields.Append fld
>    >
>    >    Set fld = Nothing
>    >    Set tdf = Nothing
>    >    Set db = Nothing
>    >    Set wrk = Nothing
>    >
>    >    but fld.Properties("FieldSize") = 255  errors with "property can 
> only
>    > be set when the Field is part of a Recordset object's field 
> collection.
>    >
>    >    What am I doing wrong?
>    >
>    >    MTIA,
>    >
>    >    Rocky Smolin
>    >    Beach Access Software
>    >    http://www.e-z-mrp.com
>    >    858-259-4334
>    >    -- 
>    >    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/listinf
>
> [Message truncated. Tap Edit->Mark for Download to get remaining portion.]
>
> -- 
> 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