How can I change the column order using VBA

[Balbina]

I have written code that appends data to a table. Occasionally, I need to
add more columns. To do this I am using a bubble sort to compare with my
existing table weather i already have the column or not. Because of this, I
make my new table in alphabetical order, but I would like to be able to
reorder the columns into something other than alphabetical. Is there a column
order property for tables? Any suggestions?

Thank you.

 

[Banana]

I have written code that appends data to a table. Occasionally, I need to
add more columns. To do this I am using a bubble sort to compare with my
existing table weather i already have the column or not. Because of this, I
make my new table in alphabetical order, but I would like to be able to
reorder the columns into something other than alphabetical. Is there a column
order property for tables? Any suggestions?

Thank you.

I can't help but think the table is not designed correctly. It is not
usual to add a column to a table and it should be designed such that you
add a new row, rather than a column.

May I suggest you google and read up on 'database normalization'? If
you're not sure how to apply to your model, post a brief description of
table and its structure and we can make suggestions how to optimize it.

I know that is not the answer you were asking for, but believe me- going
down this path would be much more painful than doing it right.

HTH.

 

[KARL DEWEY]

Following on what Banana wrote, if your database was 'normalized' then a
crosstab query would put the columns in alpha order (or any order you wanted).

 

[Balbina]

Following on what Banana wrote, if your database was 'normalized' then a
crosstab query would put the columns in alpha order (or any order you wanted).

Okay, let's say I did want to go down the complicated path, is there a
property for column order? What I was really hoping for was that I could get
at it like I can with column type and defined size etc.

I created a data type to hold my column information that I need to modify

Type TempColumns
strColumnName As String
intColumnType As Integer
intColumnAtrib As Integer
intDefinedSize As Integer
End Type

I then write the column info into an array of that data type that I named
typColumns.

Do Until intCounter = intColumns

With typColumns(intCounter)
.strColumnName = tbl.Columns.Item(intCounter)
.intColumnType = tbl.Columns.Item(intCounter).Type
.intColumnAtrib = tbl.Columns.Item(intCounter).Attributes
.intDefinedSize = tbl.Columns.Item(intCounter).DefinedSize
End With
intCounter = intCounter + 1

Loop

Ideally, I would just add another line that would read the original column
position and away I go. Then I could use it as is or change it at my whim. Is
this wishful thinking?

Thank you.

 

[KARL DEWEY]

You can use a union query to normalize your data and then the crosstab query.

 

[Paul Shapiro]

Here's a routine I use when adding a new column to an existing table. Rather
than specifying the field's position directly, I specify the field name I
want it to follow. You could modify the code to do what you want. The DAO
property is field.OrdinalPosition.

Public Function FieldSetOrdinalPosition( _
tblModify As DAO.TableDef, _
strFieldnameToBePositioned As String, _
strFieldnameToPositionAfter As String _
) As Boolean
'In tblModify, move strFieldnameToBePositioned to the position _
immediately following strFieldnameToPositionAfter.
Dim fldNew As DAO.Field
Dim lngOrdinalPositionNew As Long

'Get the ordinal position desired for the field
Set fldNew = tblModify.Fields(strFieldnameToPositionAfter)
lngOrdinalPositionNew = fldNew.OrdinalPosition + 1
Set fldNew = Nothing

'Increment ordinal positions and make space for the newly-assigned field
For Each fldNew In tblModify.Fields
If fldNew.Name = strFieldnameToBePositioned Then
fldNew.OrdinalPosition = lngOrdinalPositionNew
ElseIf fldNew.OrdinalPosition >= lngOrdinalPositionNew Then
fldNew.OrdinalPosition = fldNew.OrdinalPosition + 1
End If
Next