Change text FieldSize property in DAO

[Bob Howard]

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

 

[Dirk Goldgar]

in message

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

 

[Douglas J. Steele]

in message



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)

 

[Bob Howard]

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

 

[Stuart McCall]

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.

 

[Dirk Goldgar]

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.

 

[Stuart McCall]

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