Reordering fields

[Edski]

Hi,
I was browsing this thread: thread701-1163790 and I noticed a comment about the ordering of fields in a table not being relevant in a relational database.

Since the thread is closed I'd like to bring this topic up for debate. I can think of a situation in which the ordering of fields in a table is very relevant.

Suppose you have built in VBA code to query a table and send the result to an excel file. The query uses something like

SELECT * FROM tableA

The excel file now contains the columns from the table - in the order of the fields as they are defined in the table. Suppose that you also use Automation to modify the column formats in the excel spreadsheet so that they look nicer (maybe dates need re-formatting or the size of the columns need adjusting or the column headers need renaming). If the fields in the table are ever reordered, then the columns in the excel file will be "out of whack" with the formatting done by the built in VBA code.

Obviously changing the SQL to select each field one by one from the table instead of using the * would eliminate this potential problem.

Cheers...Ed

 

[r937]

where's the debate? this is just a clear case for never, ever using the dreaded, evil "select star"

r937.com | rudy.ca

 

[PHV]

I strongly agree with rudy !
Furthermore, never ever export a table but a query ...

 

[Crowley16]

nothing wrong with using select *

just don't export your tables, use queries or create reports...

--------------------
Procrastinate Now!

 

[Crowley16]

eh, that should be:

don't export your tbles, instead use queries or create reports...

in fact, you shouldn't really expose your tables at all, in any way...

--------------------
Procrastinate Now!

 

[Edski]

Hmmm, I think we've gone off track here. I never mentioned exporting a table directly (although using the "SELECT * FROM ..." query syntax is not much different). I also agree that this is bad practice.

My point was simply to warn people that reordering fields in a table using the method posted in the thread given in my first post may cause problems if the database has any built-in code or stored query that exports columns from a table using the "dreaded" * in the SQL.