[AccessD] Change Field Size

Rocky Smolin - Beach Access Software bchacc at san.rr.com
Mon Dec 5 09:50:17 CST 2005


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
> 




More information about the AccessD mailing list