Can someone give me a snippet of code to modify the fieldsize property of a
text field (call it "FieldA") in a table (call it "TableA") in a database
(call it "DatabaseA").

It was 10, and now needs to be 11.

I don't know if this needs to be DAO or can be done in a simpler way. I
just know i need to do it from VBA code.

Thanks.

bob

"Bob Howard" wrote in message
news:%(E-Mail Removed)...
> Can someone give me a snippet of code to modify the fieldsize property of
> a text field (call it "FieldA") in a table (call it "TableA") in a
> database (call it "DatabaseA").
>
> It was 10, and now needs to be 11.
>
> I don't know if this needs to be DAO or can be done in a simpler way. I
> just know i need to do it from VBA code.


Not DAO, but this ought to do it if run within DatabaseA:

CurrentDb.Execute _
"ALTER TABLE TableA ALTER COLUMN FieldA Text(11)", _
dbFailOnError

If run from some other database, you could open a Database object on
DatabaseA:

With DBEngine.OpenDatabase("\Path\To\DatabaseA.mdb")
.Execute _
"ALTER TABLE TableA ALTER COLUMN FieldA Text(11)", _
dbFailOnError
.Close
End With


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

"Dirk Goldgar" <(E-Mail Removed)> wrote in message
news:465E1B02-0DCF-42CB-83A6-(E-Mail Removed)...
> "Bob Howard" wrote in message
> news:%(E-Mail Removed)...
>> Can someone give me a snippet of code to modify the fieldsize property of
>> a text field (call it "FieldA") in a table (call it "TableA") in a
>> database (call it "DatabaseA").
>>
>> It was 10, and now needs to be 11.
>>
>> I don't know if this needs to be DAO or can be done in a simpler way. I
>> just know i need to do it from VBA code.
>
>
> Not DAO, but this ought to do it if run within DatabaseA:
>
> CurrentDb.Execute _
> "ALTER TABLE TableA ALTER COLUMN FieldA Text(11)", _
> dbFailOnError

Just to add onto Dirk's reply, it's not possible to change the size of a
field using DAO. Once the field has been added to the Fields collection of
the table, the Size property becomes read-only.

If there's a reason why you must use DAO (and to be honest I can't imagine
one), you'd have to add a new field of the correct size, run an update query
to populate the new field with the value of the old field, delete the old
field and rename the new field to the name of the old field (then, of
course, compact the database, although you should probably do that after
using DDL to make the change too)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Actually, I did run this successfully. I simply built some SQL to change
the column size. It worked (tested in under both Access 2003 and Access
2007, on both Windows XP and Windows 7).

I used the following:

DoCmd.SetWarnings False
nameofopendaodatabase.Execute ALTER TABLE [table name] ALTER COLUMN [field
name] TEXT(11);
If Err.Number <> 0 Then ddd
DoCmd.SetWarnings True

The error number was 0, and I looked at the resulting field size and it was
changed to 11.

Thanks all!

I'll now deploy the fix to my web site and notify + 1,000 clients that all's
well (for now).

bob

"Dirk Goldgar" <(E-Mail Removed)> wrote in message
news:465E1B02-0DCF-42CB-83A6-(E-Mail Removed)...
> "Bob Howard" wrote in message
> news:%(E-Mail Removed)...
>> Can someone give me a snippet of code to modify the fieldsize property of
>> a text field (call it "FieldA") in a table (call it "TableA") in a
>> database (call it "DatabaseA").
>>
>> It was 10, and now needs to be 11.
>>
>> I don't know if this needs to be DAO or can be done in a simpler way. I
>> just know i need to do it from VBA code.
>
>
> Not DAO, but this ought to do it if run within DatabaseA:
>
> CurrentDb.Execute _
> "ALTER TABLE TableA ALTER COLUMN FieldA Text(11)", _
> dbFailOnError
>
> If run from some other database, you could open a Database object on
> DatabaseA:
>
> With DBEngine.OpenDatabase("\Path\To\DatabaseA.mdb")
> .Execute _
> "ALTER TABLE TableA ALTER COLUMN FieldA Text(11)", _
> dbFailOnError
> .Close
> End With
>
>
> --
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
>
> (please reply to the newsgroup)
>

"Bob Howard" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Actually, I did run this successfully. I simply built some SQL to change
> the column size. It worked (tested in under both Access 2003 and Access
> 2007, on both Windows XP and Windows 7).
>
> I used the following:
>
> DoCmd.SetWarnings False
> nameofopendaodatabase.Execute ALTER TABLE [table name] ALTER COLUMN [field
> name] TEXT(11);
> If Err.Number <> 0 Then ddd
> DoCmd.SetWarnings True

I presume ddd is a sub or function. If so, you may want to switch warnings
back on before you execute it:

DoCmd.SetWarnings False
nameofopendaodatabase.Execute "ALTER TABLE [table name] ALTER COLUMN [field
name] TEXT(11);"
DoCmd.SetWarnings True
If Err.Number <> 0 Then ddd

In fact that's a golden rule to follow. If you switch off warnings, switch
them back on ASAP.

"Bob Howard" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Actually, I did run this successfully. I simply built some SQL to change
> the column size. It worked (tested in under both Access 2003 and Access
> 2007, on both Windows XP and Windows 7).
>
> I used the following:
>
> DoCmd.SetWarnings False
> nameofopendaodatabase.Execute ALTER TABLE [table name] ALTER COLUMN [field
> name] TEXT(11);
> If Err.Number <> 0 Then ddd
> DoCmd.SetWarnings True
>
> The error number was 0, and I looked at the resulting field size and it
> was changed to 11.


That's actually a bit of a mishmash of DAO code and Access-object-model
code. You are using the DAO Exexcute method to run the SQL statement (which
must actually have been enclosed in quotes in order for your code to work at
all), and SetWarnings is relevant only to warnings raised by Access, not
DAO. If you had used DoCmd.RunSQL to run the SQL statement, then
SetWarnings would have governed whether you got a warning message from
Access. As it is, if the Execute method had encountered an error, an error
message would have been displayed, regardless of the current state of
SetWarnings.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

I really should learn to read the code properly before I post.