Changing the data type of a field in a table thru code

[Peter]

Can anyone tell me how to change the data type of a field in a table created
with a make table query? The field is a binary and must be changed to text.
alternately does anyone know how to specify the field type when running a
make table query?

Thanks,
Sven

[Danny J. Lesandrini]

You can't change the data type of a field in a table. You have to create a new table
with your changes, load the data and drop the old table.

--
Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
"Peter" <pe***@diel.co.za> wrote in message news:br**********@ctb-nnrp2.saix.net...

Can anyone tell me how to change the data type of a field in a table created
with a make table query? The field is a binary and must be changed to text.
alternately does anyone know how to specify the field type when running a
make table query?

Thanks,
Sven

[Peter]

Thanks I feared as much, but is there any way in a sql statement to specify
the data-types of the fields being added into a new table
"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:br***********@ID-82595.news.uni-berlin.de...

You can't change the data type of a field in a table. You have to create a new table with your changes, load the data and drop the old table.

--
Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
"Peter" <pe***@diel.co.za> wrote in message

news:br**********@ctb-nnrp2.saix.net...

Can anyone tell me how to change the data type of a field in a table created with a make table query? The field is a binary and must be changed to text. alternately does anyone know how to specify the field type when running a make table query?

Thanks,
Sven

[tym]

On Wed, 10 Dec 2003 14:25:39 +0200, "Peter" <pe***@diel.co.za> wrote:

Can anyone tell me how to change the data type of a field in a table created
with a make table query? The field is a binary and must be changed to text.
alternately does anyone know how to specify the field type when running a
make table query?

You need to alter it in your Query.

Presuming that your field is called bField in table Table1, your
"output" field could be something like

sField: Str([Table1].[bField])

I have to use this converting numeric values to string ones , and vice
versa.

Don't know if it will work with your case though...

Tym

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~
See http://www.ictis.net/no_spam.html for unsolicited email warning
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~

[Tom van Stiphout]

On Wed, 10 Dec 2003 05:39:05 -0700, "Danny J. Lesandrini"
<dl*********@hotmail.com> wrote:

Actually, that's not quite true. I typically add a new field with the
correct datatype, run an update statement to copy over the data (if
possible), delete the old field, and rename the new field.

-Tom.

You can't change the data type of a field in a table. You have to create a new table
with your changes, load the data and drop the old table.

[Lyle Fairfield]

"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in news:br746r$275f$1
@ID-82595.news.uni-berlin.de:

You can't change the data type of a field in a table.

In Access XP one can toggle the data type of a field as follows:

DAO.DBEngine(0)(0).Execute "ALTER TABLE " _
& "[D:\My Documents\Access\db1.mdb].tbl2002Transactions " _
& "ALTER COLUMN fldDescription TEXT"

DAO.DBEngine(0)(0).Execute "ALTER TABLE " _
& "[D:\My Documents\Access\db1.mdb].tbl2002Transactions " _
& "ALTER COLUMN fldDescription BINARY"

The table's being in an external database is incidental to this example. The
code can change the data type of fields in internal tables.
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Danny J. Lesandrini]

I knew that statement would bring comments, but the way I understand it,
even in SQL Server, you CANNOT change the type of f field. You're
given the impression that you are changing the datatype, but behind the
scenes it's building a new table, copying data and dropping the old table.

Does Access do the same when, in design view, you change a datatype?
I don't really know. Michka probably does, but if I had to guess, I'd say
that's what's happening. I say that because I know you can't use DAO to
change the datatype the property of a field. It doesn't allow you. Why?
Because you can't simply toggle a field type.

Again, all of this is illusion, since you can work around this. I asked this
question to the group once, and someone answered, "Why would you
want to change a field type in code?" Not a bad question. For such an
important modification, you might as well go to each table in design mode,
verify your changes and implement them with caution and purpose.
--
Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message news:og********************************@4ax.com...

On Wed, 10 Dec 2003 05:39:05 -0700, "Danny J. Lesandrini"
<dl*********@hotmail.com> wrote:

Actually, that's not quite true. I typically add a new field with the
correct datatype, run an update statement to copy over the data (if
possible), delete the old field, and rename the new field.

-Tom.

You can't change the data type of a field in a table. You have to create a new table
with your changes, load the data and drop the old table.

[Lyle Fairfield]

"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in news:br7dq8$69ee$1
@ID-82595.news.uni-berlin.de:

I knew that statement would bring comments, but the way I understand it,
even in SQL Server, you CANNOT change the type of f field.

Wrong.

[Danny J. Lesandrini]

Lyle, I'm being dogmatic. Yes, of course, you can change the field type,
but as I said, it's my understanding that what you are REALLY doing is
building a new table with the correct field types and migrating data. This
all happens behind the scenes so you don't even realize it.

Why do I bother to raise this point? Because this little slight of hand is
the reason you can't do this

Dim fld As DAO.Field
Set fld = tdf.Fields("MyField")
If fld.Type = 8 Then fld.Type = 9

Can't do this! Why? Because you CANNOT change a field type.
That's all I'm saying.
--
Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message news:Xn*******************@130.133.1.4...

"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in news:br7dq8$69ee$1
@ID-82595.news.uni-berlin.de:

I knew that statement would bring comments, but the way I understand it,
even in SQL Server, you CANNOT change the type of f field.

Wrong.

[Hans Dreyer]

"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:br***********@ID-82595.news.uni-berlin.de...

I knew that statement would bring comments, but the way I understand it,
even in SQL Server, you CANNOT change the type of f field. You're
given the impression that you are changing the datatype, but behind the
scenes it's building a new table, copying data and dropping the old table.

SQL Server 2000/03 will cascade the data type change through all fields
("columns" in SQL Server) that are impacted by the change, i.e., according
to the PK>FK relationships. Your theory about new tables being created
"behind the scenes," etc. is doubtful, becuase the process is extremely
rapid even in large MDF's running on very old servers. (Consider the
machine resources that would be needed to perform a data type change in the
manner you propose.)

The only change that takes place "behind the scenes" that SQL Server will
admit to is that any indexes on the changed field will be discarded and
rebuilt. However, related fields will have their data type, length,
prcescion, scale, and collation changed automatically as the data type
change cascades. Presumeably, their indexes are discarded and rebuilt,
also.

Notes:
1) In versions prior to SQL Server 2000, the data type change might not
cascade. In fact, PK>FK relationships might be deleted, especially if you
change the FK data type. But in SQL Server 2000 and later, data type
changes cascade from either end of the relationship.
2) The ALTER TABLE procedure in TSQL does not allow many of the column
changes mentioned here.

Does Access do the same when, in design view, you change a datatype?
I don't really know. Michka probably does, but if I had to guess, I'd say
that's what's happening. I say that because I know you can't use DAO to
change the datatype the property of a field. It doesn't allow you. Why?
Because you can't simply toggle a field type.

The only thing dumber than Michael Kaplan is DAO. You picked a bad example.

Again, all of this is illusion, since you can work around this. I asked this question to the group once, and someone answered, "Why would you
want to change a field type in code?" Not a bad question. For such an
important modification, you might as well go to each table in design mode,
verify your changes and implement them with caution and purpose.

Granted. But you have stipulated "in code," which changes the central
argument slightly. And it is not clear if we are talking about Access Jet
or SQL Server.

[Danny J. Lesandrini]

Thanks for your insights and I am willing to concede the victory.
I'm only speaking from what I've been told. You are talking
about SQL 2000/2003, but do you know if that's how SQL 7
handled it?

We are, in fact, speaking of JET and DAO changes to field types,
so the whole discussion of SQL Server was allegorical.

There is a reason why one might want to use code to change the
datatypes, and it relates to your example. If you change SSN
field from Number to Text, you might want that to propagate
throughout your database. Access won't do this, so if it's a key
field you use a lot, you'd have to open each table and make the
changes one at a time. Would be nice in some cases to loop through,
looking for the fields you want to change and doing them all at once.

It wasn't my intention to start a holy war (though they can be fun).
I still believe I was correct for SQL 7 and I know what I said
applied to Oracle 7 ... at least that's what I recall.

Cheers
--
Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com
"Hans Dreyer" <ha*******@HotMail.com> wrote in message news:28******************************@news.teranew s.com...

"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:br***********@ID-82595.news.uni-berlin.de...

I knew that statement would bring comments, but the way I understand it,
even in SQL Server, you CANNOT change the type of f field. You're
given the impression that you are changing the datatype, but behind the
scenes it's building a new table, copying data and dropping the old table.

SQL Server 2000/03 will cascade the data type change through all fields
("columns" in SQL Server) that are impacted by the change, i.e., according
to the PK>FK relationships. Your theory about new tables being created
"behind the scenes," etc. is doubtful, becuase the process is extremely
rapid even in large MDF's running on very old servers. (Consider the
machine resources that would be needed to perform a data type change in the
manner you propose.)

The only change that takes place "behind the scenes" that SQL Server will
admit to is that any indexes on the changed field will be discarded and
rebuilt. However, related fields will have their data type, length,
prcescion, scale, and collation changed automatically as the data type
change cascades. Presumeably, their indexes are discarded and rebuilt,
also.

Notes:
1) In versions prior to SQL Server 2000, the data type change might not
cascade. In fact, PK>FK relationships might be deleted, especially if you
change the FK data type. But in SQL Server 2000 and later, data type
changes cascade from either end of the relationship.
2) The ALTER TABLE procedure in TSQL does not allow many of the column
changes mentioned here.

Does Access do the same when, in design view, you change a datatype?
I don't really know. Michka probably does, but if I had to guess, I'd say
that's what's happening. I say that because I know you can't use DAO to
change the datatype the property of a field. It doesn't allow you. Why?
Because you can't simply toggle a field type.

The only thing dumber than Michael Kaplan is DAO. You picked a bad example.

Again, all of this is illusion, since you can work around this. I asked

this

question to the group once, and someone answered, "Why would you
want to change a field type in code?" Not a bad question. For such an
important modification, you might as well go to each table in design mode,
verify your changes and implement them with caution and purpose.

Granted. But you have stipulated "in code," which changes the central
argument slightly. And it is not clear if we are talking about Access Jet
or SQL Server.

[Danny J. Lesandrini]

Well, the question about SQL Server creating a new table and
copying really bugged me, so I checked Google for a hit. Found one

On 8/15/2001 Tibor Karaszi wrote ...

How did you do the change? Ent Mgr, or ALTER TABLE?
Ent Mgr creates a new table and copies the data.
ALTER TABLE might be "smarter".
--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com

"Danny J. Lesandrini" <dl*********@hotmail.com> wrote ...
....

I still believe I was correct for SQL 7 and I know what I said
applied to Oracle 7 ... at least that's what I recall.

[Peter]

Your a winner!!
This was exactly what I was looking for, Works perfectly

Thanks,

Sven Pedersen

"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...

"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in news:br746r$275f$1 @ID-82595.news.uni-berlin.de:

You can't change the data type of a field in a table.
In Access XP one can toggle the data type of a field as follows:

DAO.DBEngine(0)(0).Execute "ALTER TABLE " _
& "[D:\My Documents\Access\db1.mdb].tbl2002Transactions " _
& "ALTER COLUMN fldDescription TEXT"

DAO.DBEngine(0)(0).Execute "ALTER TABLE " _
& "[D:\My Documents\Access\db1.mdb].tbl2002Transactions " _
& "ALTER COLUMN fldDescription BINARY"

The table's being in an external database is incidental to this example.

The code can change the data type of fields in internal tables.
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Lyle Fairfield]

"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in news:br7egr$5m01$1
@ID-82595.news.uni-berlin.de:

Lyle, I'm being dogmatic. Yes, of course, you can change the field type,
but as I said, it's my understanding that what you are REALLY doing is
building a new table with the correct field types and migrating data. This
all happens behind the scenes so you don't even realize it.

Yes, I expect that is what happens. If one runs my code on an external db one
hundred times, that db bloats up significantly. This is consistent with
building a new table behind the scenes as you suggest.
But in terms of programming, my code effectively changes a field type.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)