Using VBA module to reorder table fields

[GGleason]

Hello fellow users.

Can anyone give me VBA code to reorder the fields within an Access table? I tried setting the ColumnOrder property for the fields but that did not change the order when viewing the table in datasheet view.

Thanks.

 

[THoey]

Wouldn't you just reorder your select statement? Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[GGleason]

That would be a good solution if the users were working in queries, but they are used to working in the tables. So for the time being, I'm stuck with tables. Thank you for your response.

 

[JoeMiller]

It's not VBA, but you can go into the tables design view and move the columns by dragging the field after highlighting the line. Don't know if that will work for you or not.

HTH Joe Miller
joe.miller@flotech.net

 

[GGleason]

I have hundreds of tables that need to match the fields in a template table, so that is why I am trying the VBA approach. Thank you for your help.

 

[RobCPA]

Take a look at the Access Help under OrdinalPosition property. It has a good VBA example of how to reorder the fields programmatically.

Rob

 

[MichaelRed]

On the other hand, Terry's suggestion is probably the easiest. There is no reason to NOT have Users doing any operation on a query (looking at a single table) which they would normally do in a Table. There are ACTUALLY a few advantages.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

Then, again, Why would a relational db be set up to have Users looking DIRECTLY at single tables? Particularly " ... hundreds of tables that need to match the fields in a template table ... ". And, If they all have to match this 'template', why NOT just go ahead and re-structure them all at once? Why would a realtional db include "hundreds" of (identical?) tables? the whole thing sounds VERY WRONG.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[GGleason]

Here is some additional background to the problem. I have multiple projects where users are looking at tables with the same field structure but different data. Tables are being added as required, using a master table as a template. One of the projects has almost 1,000 tables so updating them manually was not a pleasant option.

The solution I finally settled on is a module that copies the revised field structure to an empty table. An append query appends the data from the old table to the new table . The old table is deleted and the new table is then renamed to the old table name.

I know that this was probably not the elegant solution a Microsoft engineer would have come up with, but it worked and it gave me the result I was looking for.

Thankful for everyone’s input,
GGleason

 

[JerryDennison]

Your database is not normalized properly at all. Michael is absolutely correct, there is no reason whatsoever to have that many tables that share the same structure. If you stop and think about it, every record is different in some way from other records within the table. So why have so many tables that share the same fieldnames? This is because you clearly don't understand what and how a relational database works. This has created a huge amount of work trying to maintain the db that is totally unneccessary. If you'll reconstruct the db and normalize the data you'll find it far simpler to work with and much much easier to maintain. If it's necessary that some information be kept "separate" then you simply use the appropriate grouping fields and sub data tables.