Change Field Data Type

[DubCap01]

Hi all, hours on Google, to no avail.
I am trying to alter the datatype in a field from Number to Short Text. The field never contains more than 18 characters, and they are always numeric. However for onward transmission this field must be a text field.
This is the code that I thought should work, but it doesn't:

DoCmd.RunSQL "ALTER TABLE TblEpisode ALTER COLUMN EpisodeID Text(20);"

Can anyone correct me or point to an infallible alternative?
thanks in anticipation
Pete

[andy49]

Is the field episodeID a primary key or is it linked to another field in a relationship or query.

When you copy and paste that code into a query window in SQL mode, does it work?


Sent from my iPhone using Tapatalk

[DubCap01]

Hi Andy, no, no and no! I just want to change the data type on the fly!

[aytee111]

Does it give you an error? Is the table in use, on a form or ??

[Missinglinq]

Where, exactly, are you trying to do this? In v2007, using your code behind a Command Button, it performs exactly as you want it to, for me!

Linq ;0)>

Just had a thought (at my age, I try to hold onto to them, they happen so seldom) since the Datatype name has been changed from Text to ShortText, in later versions, does the SQL need to us ShortText, rather than Text? I can't test this in v2007.

[AccessPower]

Shouldn't you be able to do this in data-sheet/design view? Just change the field type.

[Missinglinq]

The OP said

...I just want to change the data type on the fly...

He's apparently exporting it, needs to change it before doing so, and probably (I'm guessing, here) is going to change it back to a Number after the export.

Linq ;0)>

[AccessPower]

Where, exactly, are you trying to do this? In v2007, using your code behind a Command Button, it performs exactly as you want it to, for me!

Linq ;0)>

Just had a thought (at my age, I try to hold onto to them, they happen so seldom) since the Datatype name has been changed from Text to ShortText, in later versions, does the SQL need to us ShortText, rather than Text? I can't test this in v2007.

I'm going to give this a shot in v2007.

[AccessPower]

The code you posted is working on 2007 and 2016. Attached is a proof-of-concept for converting back and forth.

DataTypeConversion.zip

You will get an error in the even of a null value, though.

There is a work-around if this isn't working in your database. You can create code to copy the data to a dummy table with specified data types, drop the current table, then rename your dummy table to match your original table. That would allow you to do whatever you need on-the-fly.

[DubCap01]

Hi all, thanks for the replies. I don't want to change it in design view, as it interrupts the automation of importing and exporting somewhat!
'Text(20)' to ShortText(20) doesn't work either, so maybe I will try another method. Thanks for all your help folks
I will leave the thread open for now, just in case someone else has other suggestions
Pete

[ssanfu]

In TblEpisode, I would add another column that is Text (or Short text - I'm using A2010), maybe named "EpisodeTxt".
Then execute an update query to convert the number ("EpisodeID") to text ("EpisodeTxt").
Instead of exporting "EpisodeID", export "EpisodeTxt".


IMHO, Constantly changing a field's data type is a good way to introduce corruption.

[AccessPower]

Hi all, thanks for the replies. I don't want to change it in design view, as it interrupts the automation of importing and exporting somewhat!
'Text(20)' to ShortText(20) doesn't work either, so maybe I will try another method. Thanks for all your help folks
I will leave the thread open for now, just in case someone else has other suggestions
Pete

The file I uploaded works. It uses a button on a form to change the data type.