WHY isn't my code changing the OrdinalPosition of my fields??? - This SHOULD work!?!?!

[bobg]

running access 2k FE/BE;

First off - before any points this out
Yes - I know that the ordinal position of fields within a table are
irrelevant as I can change the order in my SELECT.

This has become an anal exploration of academic knowledge!

My full goal is to insert a new field in a specific position in a
table. I'm attempting to do this, by "renumbering" the ordinals below
my insertion point, and then inserting the new field into position.

the new field inserts perfectly where I want - but none of the other
fields get their positions reorganized!!

Could some kind soul please point out where I'm going wrong!!??

here's my relevant code:

'***** start code *****
Private Sub sTestChgOrdinal()
Dim dbs As Database, tbldef As TableDef, c As Integer, pos As Integer
Set dbs = DBEngine.Workspaces(0).OpenDatabase("c:\path\to\mydb.mdb")
Set tbldef = dbs.TableDefs("tbl_inv")

pos = 4 ' position where a new field will be inserted after renum'g
the others

For c = tbldef.Fields.Count - 1 To pos - 1 Step -1
b4 = tbldef.Fields(c).Name & " | " & "B4: " &
tbldef.Fields(c).OrdinalPosition
tbldef.Fields(c).OrdinalPosition = c + 1
' Fields.Refresh ???? would this help?? doesn't seem to....
aftr = " ||| Afr: " & tbldef.Fields(c).OrdinalPosition
Debug.Print b4 & aftr, "c= " & c
Next

' (insert new field & set its ord.) - this works....
Set tbldef = Nothing
Set dbs = Nothing
End Sub
'***** end code *****

tia - Bob

 

[Cheval]

The problem is that you are not chaging the order of the
other fields, only their ordinal number.
eg,
Before:
FieldA=1, FieldB=2, FieldC=3, FieldD=4.
After:
FieldA=1, FieldZ=2, FieldB=4, FieldC=5, FieldD=6.

So they are still in the same order, even with the
inserted field.

 

[bobg]

Cheval;

thank you for your reply; I appreciate it.
After spending a bit more time on this:

I'm not sure if you understood me but; changing the ordinal position
IS supposed to change their order....

I also discovered there is practically nothing wrong with my code -
except I fragged my ord.pos. calculation. I mixed up the field
position #'s with the ordinal #'s.....

minor fix, and all works perfectly!

tx again!!!
Bob