[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.]