How to change an existing field size with DAO or ADO?

[Lauren Wilson]

Hi Folks,

I am totally confused about the following:

1. Is it possible to change the size of an EXISTING TEXT table field
in Access 2k or later using DAO or ADO code?
2. If so, does anyone have some sample code?

I have ransacked all my documentation to find the answer to no avail.
Please advise ASAP!

Thanks.

[Douglas J. Steele]

Try ALTER TABLE MyTable ALTER COLUMN MyField Text(100)

(replace MyTable and MyField with the appropriate names)

However, while that can be used to change a field type, I'm not sure it can
be used to alter the length of a field. You may very well have to add a new
field of the correct size, run an update query to transfer the values from
the old field to the new field, delete the old field then rename the new
field.

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

"Lauren Wilson" <pr*****@nospam.com> wrote in message
news:qn********************************@4ax.com...

Hi Folks,

I am totally confused about the following:

1. Is it possible to change the size of an EXISTING TEXT table field
in Access 2k or later using DAO or ADO code?
2. If so, does anyone have some sample code?

I have ransacked all my documentation to find the answer to no avail.
Please advise ASAP!

Thanks.

[Randy Harris]

Doug, your example does indeed work to change the field length. Tested in
A2K.

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:yN********************@rogers.com...

Try ALTER TABLE MyTable ALTER COLUMN MyField Text(100)

(replace MyTable and MyField with the appropriate names)

However, while that can be used to change a field type, I'm not sure it can be used to alter the length of a field. You may very well have to add a new field of the correct size, run an update query to transfer the values from
the old field to the new field, delete the old field then rename the new
field.

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

"Lauren Wilson" <pr*****@nospam.com> wrote in message
news:qn********************************@4ax.com...

Hi Folks,

I am totally confused about the following:

1. Is it possible to change the size of an EXISTING TEXT table field
in Access 2k or later using DAO or ADO code?
2. If so, does anyone have some sample code?

I have ransacked all my documentation to find the answer to no avail.
Please advise ASAP!

Thanks.

[Lauren Wilson]

On Thu, 30 Dec 2004 06:49:56 -0500, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

Try ALTER TABLE MyTable ALTER COLUMN MyField Text(100)

(replace MyTable and MyField with the appropriate names)

However, while that can be used to change a field type, I'm not sure it can
be used to alter the length of a field. You may very well have to add a new
field of the correct size, run an update query to transfer the values from
the old field to the new field, delete the old field then rename the new
field.

Thanks tons Doug.
I THOUGHT I could do it with an ALTER query but could not find any
documentation that specifically says that is possible -- even in the
A2K Dev Handbook by Ken Getz , et al. Perhaps I just missed it. I
also searched on the MVPS website and found nothing of value for this
purpose. In fact, that reminds me: the search tool on that site is
almost worthless because it always returns stuff that does NOT match
the criteria. But I digress.

I am still wondering if it's possible to do it with DAO or ADO. Do
you know? I already have code that uses DAO to ADD NEW fields to a
table, but I'm having trouble finding documentation that specifically
says how to modify the built in properties of an existing table
column. I feel like a dummy. I'm also having trouble with the Access
VBA help on my installation of A2K with Dev tools. Every time I try
to retrieve help for items listed in the Object Browser, Access tells
me that the "feature is broken" and prompts me to repair it. When I
do so, it goes through the motions but does NOT repair the help files.
Any clue about that?

Thanks again Doug.

--LW

[Randy Harris]

DoCmd.RunSQL "ALTER TABLE MyTable ALTER COLUMN MyField Text(100)"

If you want something ADO specific (I don't know why you would):
CurrentProject.Connection.Execute "ALTER TABLE MyTable ALTER COLUMN MyField
Text(100)"

HTH
"Lauren Wilson" <pr*****@nospam.com> wrote in message
news:jo********************************@4ax.com...

On Thu, 30 Dec 2004 06:49:56 -0500, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

Try ALTER TABLE MyTable ALTER COLUMN MyField Text(100)

(replace MyTable and MyField with the appropriate names)

However, while that can be used to change a field type, I'm not sure it canbe used to alter the length of a field. You may very well have to add a newfield of the correct size, run an update query to transfer the values fromthe old field to the new field, delete the old field then rename the new
field.

Thanks tons Doug.
I THOUGHT I could do it with an ALTER query but could not find any
documentation that specifically says that is possible -- even in the
A2K Dev Handbook by Ken Getz , et al. Perhaps I just missed it. I
also searched on the MVPS website and found nothing of value for this
purpose. In fact, that reminds me: the search tool on that site is
almost worthless because it always returns stuff that does NOT match
the criteria. But I digress.

I am still wondering if it's possible to do it with DAO or ADO. Do
you know? I already have code that uses DAO to ADD NEW fields to a
table, but I'm having trouble finding documentation that specifically
says how to modify the built in properties of an existing table
column. I feel like a dummy. I'm also having trouble with the Access
VBA help on my installation of A2K with Dev tools. Every time I try
to retrieve help for items listed in the Object Browser, Access tells
me that the "feature is broken" and prompts me to repair it. When I
do so, it goes through the motions but does NOT repair the help files.
Any clue about that?

Thanks again Doug.

--LW

[MacDermott]

DAO exposes both the FieldSize and Size properties of a field in a TableDef,
but they both appear to be read-only properties.

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:zD*****************@newssvr19.news.prodigy.co m...

DoCmd.RunSQL "ALTER TABLE MyTable ALTER COLUMN MyField Text(100)"

If you want something ADO specific (I don't know why you would):
CurrentProject.Connection.Execute "ALTER TABLE MyTable ALTER COLUMN MyField Text(100)"

HTH
"Lauren Wilson" <pr*****@nospam.com> wrote in message
news:jo********************************@4ax.com...

On Thu, 30 Dec 2004 06:49:56 -0500, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

Try ALTER TABLE MyTable ALTER COLUMN MyField Text(100)

(replace MyTable and MyField with the appropriate names)

However, while that can be used to change a field type, I'm not sure it canbe used to alter the length of a field. You may very well have to add a newfield of the correct size, run an update query to transfer the values fromthe old field to the new field, delete the old field then rename the newfield.

Thanks tons Doug.
I THOUGHT I could do it with an ALTER query but could not find any
documentation that specifically says that is possible -- even in the
A2K Dev Handbook by Ken Getz , et al. Perhaps I just missed it. I
also searched on the MVPS website and found nothing of value for this
purpose. In fact, that reminds me: the search tool on that site is
almost worthless because it always returns stuff that does NOT match
the criteria. But I digress.

I am still wondering if it's possible to do it with DAO or ADO. Do
you know? I already have code that uses DAO to ADD NEW fields to a
table, but I'm having trouble finding documentation that specifically
says how to modify the built in properties of an existing table
column. I feel like a dummy. I'm also having trouble with the Access
VBA help on my installation of A2K with Dev tools. Every time I try
to retrieve help for items listed in the Object Browser, Access tells
me that the "feature is broken" and prompts me to repair it. When I
do so, it goes through the motions but does NOT repair the help files.
Any clue about that?

Thanks again Doug.

--LW

[Douglas J. Steele]

That's correct: once the Field has been added to the TableDef, the fields
become read-only.

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

"MacDermott" <ma********@nospam.com> wrote in message
news:_3*****************@newsread3.news.atl.earthl ink.net...

DAO exposes both the FieldSize and Size properties of a field in a TableDef, but they both appear to be read-only properties.

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:zD*****************@newssvr19.news.prodigy.co m...

DoCmd.RunSQL "ALTER TABLE MyTable ALTER COLUMN MyField Text(100)"

If you want something ADO specific (I don't know why you would):
CurrentProject.Connection.Execute "ALTER TABLE MyTable ALTER COLUMN

MyField

Text(100)"

HTH
"Lauren Wilson" <pr*****@nospam.com> wrote in message
news:jo********************************@4ax.com...

On Thu, 30 Dec 2004 06:49:56 -0500, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

>Try ALTER TABLE MyTable ALTER COLUMN MyField Text(100)
>
>(replace MyTable and MyField with the appropriate names)
>
>However, while that can be used to change a field type, I'm not sure

it

can

>be used to alter the length of a field. You may very well have to add

a new

>field of the correct size, run an update query to transfer the values

from

>the old field to the new field, delete the old field then rename the

new >field.

Thanks tons Doug.
I THOUGHT I could do it with an ALTER query but could not find any
documentation that specifically says that is possible -- even in the
A2K Dev Handbook by Ken Getz , et al. Perhaps I just missed it. I
also searched on the MVPS website and found nothing of value for this
purpose. In fact, that reminds me: the search tool on that site is
almost worthless because it always returns stuff that does NOT match
the criteria. But I digress.

I am still wondering if it's possible to do it with DAO or ADO. Do
you know? I already have code that uses DAO to ADD NEW fields to a
table, but I'm having trouble finding documentation that specifically
says how to modify the built in properties of an existing table
column. I feel like a dummy. I'm also having trouble with the Access
VBA help on my installation of A2K with Dev tools. Every time I try
to retrieve help for items listed in the Object Browser, Access tells
me that the "feature is broken" and prompts me to repair it. When I
do so, it goes through the motions but does NOT repair the help files.
Any clue about that?

Thanks again Doug.

--LW