472,374 Members | 1,245 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,374 software developers and data experts.

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

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
6 8969
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Hovik Melikyan | last post by:
I'm trying add a new member to a class that's defined and implemented in a dynamic library (both Windows DLL and Unix SO). Provided that I don't change the interface methods and I add this new...
15
by: DesignGuy | last post by:
I've inherited a site that has 1000+ product pages that follow a similar layout. I would like to import these pages into a database for ease of future updating. The pages have the usual banners,...
34
by: Andrew DeFaria | last post by:
I thought this would be fairly straight forward but apparently it's not. Given the following html file: <!DOCTYPE html PUBLIC "-//w3c//dtd html 4.0 transitional//en"> <html> <head>...
5
by: Robert Stearns | last post by:
Either I missed something, or ALTER TABLE does not have this capability. Is there any way of doing it except DROPping all constraints which mention this table, EXPORTing the data, DROPping the...
13
by: nyt | last post by:
I have a problem of number and text field. I got the database file(mdb) that contains many combo boxes used and its list values are created by "value list" For eg field Field name= 'furniture'...
2
by: VictorS | last post by:
Hello everybody! Iíve inherited a moderate size Employee database. Main Employees table has a SSN as a primary key, but it is a ďTextĒ data type. Iím now told to delete social security numbers due...
2
by: DavidPr | last post by:
I'm creating (trying to create) a picture gallery for my website. The script is not working. I've been working on it now for about 80 hours with no success. My php skills aren't very good. This...
1
by: barbara_dave | last post by:
Hi all, I want to change a field data type from Text to Memo.The reason is I need to type more text in that field. when I was saving the change I got error message "The width of a Unicode...
25
by: Peng Yu | last post by:
Hi, It is possible to change the length of "\t" to a number other than 8. std::cout << "\t"; Thanks, Peng
2
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
2
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
1
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
1
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
0
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.