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
"Balbina" <(E-Mail Removed)> wrote in message
news:45537E97-3343-4CED-9036-(E-Mail Removed)...
>
>
> "KARL DEWEY" wrote:
>
>> 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).
>> --
>> Build a little, test a little.
>>
>>
>> "Banana" wrote:
>>
>> > Balbina wrote:
>> > > 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.
>> > .
> 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.
|
Similar Threads
