By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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.

Nov 13 '05 #2

P: n/a
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.


Nov 13 '05 #3

P: n/a
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
Nov 13 '05 #4

P: n/a
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

Nov 13 '05 #5

P: n/a
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


Nov 13 '05 #6

P: n/a
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



Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.