[AccessD] Change Field Size

Andy Lacey andy at minstersystems.co.uk
Mon Dec 5 12:17:08 CST 2005


Hi Rocky
Thanks for the link. Yes we're considering the code Fred uses but it has
limitations, such as not dealing with relationships and not dealing with all
field properties. Anyway, hopefully, the next version of BEU (which we're
now working on) will have the feature somehow.

-- Andy Lacey
http://www.minstersystems.co.uk 
 

> -----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 15:50
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Change Field Size
> 
> 
> Andy:
> 
> The code at the link Fred sent: 
> http://aislebyaisle.com/access/vba_backend_code.htm
> 
> might give you some ideas on how to do it.  It worked quite 
> well, just cut 
> and paste.
> 
> Rocky
> 
> ----- Original Message ----- 
> From: "Andy Lacey" <andy at minstersystems.co.uk>
> To: "Access Developers discussion and problem solving" 
> <accessd at databaseadvisors.com>
> Sent: Monday, December 05, 2005 7:37 AM
> Subject: Re: [AccessD] Change Field Size
> 
> 
> > Thx for the plug Gary but in fact changing a field length is not 
> > available in BEU (yet). It wasn't in because, like changing field 
> > type, you can't do it straightforwardly with DAO code and 
> ALTER COLUMN 
> > doesn't work in all versions (not in A97 certainly). As it happens 
> > we're currently looking at a v2 of BEU and one of the 
> features we're 
> > hoping to incorporate is this.
> >
> > --
> > Andy Lacey
> > http://www.minstersystems.co.uk
> >
> >
> >
> >
> > --------- Original Message --------
> > From: "Access Developers discussion and problem solving" 
> > <accessd at databaseadvisors.com>
> > To: "Access Developers discussion and problem solving" 
> > <accessd at databaseadvisors.com>
> > Subject: Re: [AccessD] Change Field Size
> > Date: 05/12/05 15:12
> >
> >
> > Isn't that what the DatabaseAdvisors BACKEND UPDATER was made to 
> > do????
> >
> > Tool and documentation are available here.....
> >
> > http://www.databaseadvisors.com/downloads.htm
> >
> > GK
> >
> > On 12/5/05, Rocky Smolin - Beach Access Software <bchacc at san.rr.com>
> > wrote:
> >> 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
> >>
> >
> >
> > --
> > Gary Kjos
> > garykjos at gmail.com
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com 
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > ________________________________________________
> > Message sent using UebiMiau 2.7.2
> >
> > --
> > 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