changing ordinal position of a field

Does anyone have the idea of how to change the ordinal position of a
field using DDL statements in access 97? I know i'm posting it in
incorrect group, as this is the only active group i've visited till
now.
thanks in advance<rameshsaive@.gmail.com> wrote in message
news:1140699343.348758.133780@.e56g2000cwe.googlegroups.com...
> Does anyone have the idea of how to change the ordinal position of a
> field using DDL statements in access 97? I know i'm posting it in
> incorrect group, as this is the only active group i've visited till
> now.
> thanks in advance
>
I'm not sure what that matters to you. The database stores the data in
whatever order makes the best sense to it. That said, the easy way would
be to do something like:
SELECT col1, col2, col3 -- Use the order that you want the data
displayed in
INTO SomeTempTable
FROM OldTable
DROP OldTabe
SELECT *
INTO NewTable
FROM SomeTempTable
DROP SomeTempTable
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Rick for your info.
There is no problem with the order of the fields in the database. But
it causing me a hell of a problems in my 100+ reports developed in
crystal reports 8.5.
I've a table named "employee", has a field named "postdesc". Initially
the size of the field "postdesc" is limited to 100 but now it has
increased to 200. Unfortunately, i've dropped the column & recreated
it using DDL Statements.
But this has changed the ordinal position of the field (it has added
the field at the end of the table). Now the reports which depends on
this field is not working properly. i know this is a problem with
crystal reports, but it is very cumbursome to change 100+ reports.
Please help me on this.|||<rameshsaive@.gmail.com> wrote in message
news:1140701965.950863.124030@.u72g2000cwu.googlegroups.com...
> Thanks Rick for your info.
> There is no problem with the order of the fields in the database. But
> it causing me a hell of a problems in my 100+ reports developed in
> crystal reports 8.5.
> I've a table named "employee", has a field named "postdesc". Initially
> the size of the field "postdesc" is limited to 100 but now it has
> increased to 200. Unfortunately, i've dropped the column & recreated
> it using DDL Statements.
> But this has changed the ordinal position of the field (it has added
> the field at the end of the table). Now the reports which depends on
> this field is not working properly. i know this is a problem with
> crystal reports, but it is very cumbursome to change 100+ reports.
> Please help me on this.
>
I understand now..
You should be able to go into Access and open the Table Designer. From
there, you should be able to move the row to where you want it in the list.
If that doesn't work, there is always VBScripting. ;-)
Rick Sawtell|||This will not fix your current problem, but ...
When at all possible use views instead of tables in crystal reports. When
the underlying table changes crystal will be unaffected.
If you change structure of the view (add or remove a field) you will still
need to go into crystal and verify the database though.
Just some general advice to make working with crystal a little bit easier.
<rameshsaive@.gmail.com> wrote in message
news:1140701965.950863.124030@.u72g2000cwu.googlegroups.com...
> Thanks Rick for your info.
> There is no problem with the order of the fields in the database. But
> it causing me a hell of a problems in my 100+ reports developed in
> crystal reports 8.5.
> I've a table named "employee", has a field named "postdesc". Initially
> the size of the field "postdesc" is limited to 100 but now it has
> increased to 200. Unfortunately, i've dropped the column & recreated
> it using DDL Statements.
> But this has changed the ordinal position of the field (it has added
> the field at the end of the table). Now the reports which depends on
> this field is not working properly. i know this is a problem with
> crystal reports, but it is very cumbursome to change 100+ reports.
> Please help me on this.
>

Changing Order of columns in a table

Is there any way to change the order (position) of columns of a table ,
without the need of dropping and recreating the table itself !?
I found the 'colid' field of the 'syscolumn' system table: by changing the
colid of each column I got the desired result, unfortunately if I have to
create indexes ih the above columns later on I get the error
Location: record.cpp: 759
Expression: pbind-> fcheckfornull ()
spID: errore 56 o 61
process ID 2976 o 2452
ID code -2147467259
as a side effect.
Moreover , I saw that in the 'syscolumns' system table , the change in the
'colid' field was an insert rather than an update...
Thanks,
Massimo.If you monkey around with the data in the sys... tables you can screw up
your database permanently.
I think you should really drop the table and re-create it.
You could also create a view with the columns in the required order.
"news" <massimo.facchi@.getronics.com> wrote in message
news:eqacvBkjDHA.2656@.TK2MSFTNGP10.phx.gbl...
> Is there any way to change the order (position) of columns of a table ,
> without the need of dropping and recreating the table itself !?
> I found the 'colid' field of the 'syscolumn' system table: by changing the
> colid of each column I got the desired result, unfortunately if I have to
> create indexes ih the above columns later on I get the error
> Location: record.cpp: 759
> Expression: pbind-> fcheckfornull ()
> spID: errore 56 o 61
> process ID 2976 o 2452
> ID code -2147467259
> as a side effect.
>
> Moreover , I saw that in the 'syscolumns' system table , the change in the
> 'colid' field was an insert rather than an update...
> Thanks,
>
> Massimo.
>
>
>|||I used to worry about the order of columns when I first started using sql,
but I quickly found I was wasting my time... As the previous poster said,
messing with system tables is not a good way to go... The order of the
columns physically in the record is different than the order you put in the
create table...
Unless you have some huge, overriding reason don't worry about column
order... Otherwise, drop and re-create the table.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"news" <massimo.facchi@.getronics.com> wrote in message
news:eqacvBkjDHA.2656@.TK2MSFTNGP10.phx.gbl...
> Is there any way to change the order (position) of columns of a table ,
> without the need of dropping and recreating the table itself !?
> I found the 'colid' field of the 'syscolumn' system table: by changing the
> colid of each column I got the desired result, unfortunately if I have to
> create indexes ih the above columns later on I get the error
> Location: record.cpp: 759
> Expression: pbind-> fcheckfornull ()
> spID: errore 56 o 61
> process ID 2976 o 2452
> ID code -2147467259
> as a side effect.
>
> Moreover , I saw that in the 'syscolumns' system table , the change in the
> 'colid' field was an insert rather than an update...
> Thanks,
>
> Massimo.
>
>
>