Emulate SQL Type Conversion "ALTER TABLE - CHANGE" in ACCESS 97

  • Thread starter Thread starter TDN
  • Start date Start date
T

TDN

hello,

in a table i need to convert the field type of an existing
table. The source field has either "Numveric" or "Text"
type and has to be changed to "Text".

in mySQL I'd use
'ALTER TABLE "MyTable"
CHANGE "SourceField" "DesinationField" Char(6);'.

unfortunately, Access 97 does not understand the CHANGE
option of ALTER TABLE. does anyone know a way to emulate
this behaviour to have the type conversion done?


Note:
i've tried a "Make Table" query with the statement
SELECT Str("SourcField") As "DestinationField";
but this only works with "Numeric" SourceFields. It
produces an error and loss of data if the SourceField is
of "Text" type, so this is no solution, either.

any comments really appreciated! I'd hate to give in to
Access...

thanks

TDN
 
TDN said:
hello,

in a table i need to convert the field type of an existing
table. The source field has either "Numveric" or "Text"
type and has to be changed to "Text".

in mySQL I'd use
'ALTER TABLE "MyTable"
CHANGE "SourceField" "DesinationField" Char(6);'.

unfortunately, Access 97 does not understand the CHANGE
option of ALTER TABLE. does anyone know a way to emulate
this behaviour to have the type conversion done?


Note:
i've tried a "Make Table" query with the statement
SELECT Str("SourcField") As "DestinationField";
but this only works with "Numeric" SourceFields. It
produces an error and loss of data if the SourceField is
of "Text" type, so this is no solution, either.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you're using JET (aka Access) Data Definition Language (DDL), there
isn't a CHANGE keyword. Using DDL you'd have to:

1. Create a new column w/ the new data type.
2. Copy the contents of the old column to the new column.
3. Delete the old column.

If you wanted to keep the name of the old column, you'd have to,
additionally:

4. Create a new column w/ the old column's name.
5. Copy the data from the intermediate column (1) to the newest column.
6. Delete the intermediate column.

Example DDL/SQL:

1. ALTER TABLE TableName ADD COLUMN NewColumnName <new data type>
2. UPDATE TableName SET NewColumnName = OldColumnName
3. ALTER TABLE TableName DROP COLUMN OldColumnName

4. ALTER TABLE TableName ADD COLUMN OldColumnName <new data type>
5. UPDATE TableName SET OldColumnName = NewColumnName
6. ALTER TABLE TableName DROP COLUMN NewColumnName

Note: Each of these statements must be a separate query.

The easier thing to do is open the table in design view & change the
data type on the existing column.

WARNING: If the old data can't be converted to the new data type the
old data will be lost.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQHWKSIechKqOuFEgEQKIIgCgxbsy32GsiM8LTfJqemFOaIs1854AoNKX
645sFnA/ZE2ZaEYx3U/09f3s
=z5AA
-----END PGP SIGNATURE-----
 
Thanks MGFoster,

but i've already tried that. it works. however, it takes
awfully long! i need nine fields (out of 12) to be "text".
without any apparent rule Access sometimes imports some of
the nine fields as "numeric". i thus have to apply your
procedure to ALL of them to make sure the field types
are "text".

the procedure takes up to ten minutes to convert the
larges table! (after switching of the "are you sure you
want to do that?" messages!)

of course is it faster to do it manually but i want to
prevent users from tampering with field definitions so i'd
like Access to do it.

what i don't understand:
when you do it manually it's just a few clicks and a brief
time for conversion. but why can't this be automated?! the
has got to be a simpler solution...

anyway, thanks again for your effort, MGFoster

br

TDN
 
TDN said:
Thanks MGFoster,

but i've already tried that. it works. however, it takes
awfully long! i need nine fields (out of 12) to be "text".
without any apparent rule Access sometimes imports some of
the nine fields as "numeric". i thus have to apply your
procedure to ALL of them to make sure the field types
are "text".

the procedure takes up to ten minutes to convert the
larges table! (after switching of the "are you sure you
want to do that?" messages!)

of course is it faster to do it manually but i want to
prevent users from tampering with field definitions so i'd
like Access to do it.

what i don't understand:
when you do it manually it's just a few clicks and a brief
time for conversion. but why can't this be automated?! the
has got to be a simpler solution...
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you're importing data from text files you could use an import
specification that would define each column's data type. See the Access
Help articles on TransferText method and (if you can find them) the
import/export specifications.

To see an import/export specification: on the main menu click File >
Get External Data > Import. Then select a text file. An import dialog
box will appear w/ an "Advanced" button in the lower right corner.
Click that button and the specifications dialog box will appear. In the
datagrid will be the column names & their data types. You can change
the column names and data types & save the specification for latter use
in the TransferText method.

If you're importing data from Excel you could put a value at the top of
each column (below the column name) that would define the data type of
that column & format that cell in the data type you want. It's kinda
kludgey, but works.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQHwZo4echKqOuFEgEQKMIgCeMsHoL98SPWFd7mZ8yjCGQ42VUoQAoIH5
w2A1HsLiW1fb9Z/d+up6tLJB
=6bsm
-----END PGP SIGNATURE-----
 
Back
Top