Ordinal Position of DAO Fields

I have added a Field to a DAO Table that already has 9 Fields using ...tbl.Fields.Append fld
This adds the new Field to the right of the Table, but I want it to be the first Field, on the left.

After adding...fld.OrdinalPosition = 1...I now have two fields with OrdinalPosition 1, the old one to the left and the new one to the right.
How to I get the Fields in the correct order?
LVL 1
chrisezardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve BinkCommented:
Is there a particular reason you need them in that order?  You can always alter the field order through the table's Design view, and you can access fields in  a table by name...much easier than trying to get their ordinal position.
0
shanesuebsahakarnCommented:
If you really want to do it, you can just loop through the fields:

For Each fld In tdf
   If fld.Name<>"MyNewField" Then fld.OrdinalPosition=fld.OrdinalPosition+1
Next

But routinet is right - why do you need them in this order?
0
peter57rCommented:
Hello chrisezard,

Not something that is normally an issue; but you could create a SQL string in code which selects the fields in their display order and and save that SQL string to the SQL property of a dummy saved query.  Then open the query instead of the table.

Pete
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

chrisezardAuthor Commented:
I imported 550,000 records from a .txt file and deleted 135,000 unwanted rows with a Query, so I did not specify a Primary Key on Import.
The new Field is an ID AutoNumber PrimaryKey, and I did not want gaps in the number sequence after deletions.
By convention, ID Fields go on the left.
Shane's suggestion seems perfectly logical, but it isn't working properly yet.
See you tomorrow.

Chris
0
Steve BinkCommented:
True, PKs are traditionally the first field in the table, but you will not see any benefit from re-ordering the fields aside from aesthetics.  As peter57r posted, you can easily create your own field order by using a query.

If you still insist it is necessary, the EASIEST way is to simply re-order the fields manually in the table's Desgin view.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shanesuebsahakarnCommented:
In addition to routinet's comment:

> and I did not want gaps in the number sequence after deletions.

Gaps should not, and should NEVER matter. A surrogate key should ONLY be used to uniquely identify the record - it should never be relied upon to hold a meaningful value, especially when using an autonumber.
0
chrisezardAuthor Commented:
1. Obviously manually moving the Field works fine but I want a reuseable piece of code.
2. I hear what you say about gaps in AutoNumbers, but this is a financial model subject to Sarbanes Oxley Act scrutiny. One thing auditors do NOT like is the appearance of missing data (i.e. has someone fraudulently removed it?)

After running Shane's suggested code I get the right Ordinal Positions, but the ID Field is still second in Datasheet View.
It makes no difference whether I run the loop before or after creating the ID Field.

    Set fld = tbl.CreateField("ID", dbLong)
    fld.Attributes = dbAutoIncrField               'AutoNumber setting
    fld.OrdinalPosition = 1                            'Moves Field to first position
    tbl.Fields.Append fld

'   Move each Field one place to the right to make room for ID Field as first Field in Table
    For Each fld In tbl.Fields
    If fld.Name <> "ID" Then fld.OrdinalPosition = fld.OrdinalPosition + 1
    Next fld

OrdinalPosition                       Datasheet View
 1  ID                                     Date Created
 2  Date Created                     ID
 3  Order Number                   Order Number
 4  Order Date                        Order Date
... six more Fields                ...in the right order
0
shanesuebsahakarnCommented:
OrdinalPosition counts from 0, so try this:

   Set fld = tbl.CreateField("ID", dbLong)
    fld.Attributes = dbAutoIncrField               'AutoNumber setting
    fld.OrdinalPosition = 0                            'Moves Field to first position
    tbl.Fields.Append fld

'   Move each Field one place to the right to make room for ID Field as first Field in Table
    For Each fld In tbl.Fields
    If fld.Name <> "ID" Then fld.OrdinalPosition = fld.OrdinalPosition + 1
    Next fld
0
chrisezardAuthor Commented:
This is interesting - your suggestion makes things worse!

OrdinalPosition                       Datasheet View
 0  ID                                     Date Created
 1  Date Created                     Order Number
 2  Order Number                   ID

I have done some tests on OrdinalPosition, and linked Tables start at 0, whereas created/imported Tables start at 1. Mine is imported.
0
Steve BinkCommented:
>> but this is a financial model subject to Sarbanes Oxley Act scrutiny

I am also gearing up for a Sarbanes compliance audit, and I have to tell you that this is not going to matter.  If the auditor decides to make an issue of a missing autonumber, there is more than enough documentation available, both here and externally (including Microsoft!), to show this is behavior by design.  After all, autonumbers go missing all the time for various reasons on virtually EVERY database platform.  

Chances are your auditor will have enough experience and familiarity to know this is a non-issue for compliance.
0
shanesuebsahakarnCommented:
And this also comes back to the point about autonumbers holding meaningful values - in this instance, they ARE being used to hold a meaningful value, which they should not. An alternative key value, which should be programmatically calculated, should hold the sequential number. For example, if there are two invoice records with numbers A1000 and A1001, does it matter that their ID values are 20769 and 21697?

As for OrdinalPosition, well, I don't know since I never use it. The position of the field in a table is, and should be, a matter of extreme indifference, since a *table* should never be used for data entry anyway and so the user will never see the field positioning, and in queries you can define the order if it is that important - but queries should also never be used for direct data entry.
0
chrisezardAuthor Commented:
Right. Enough's enough.
I've set OrdinalPosition to 0 and left out the loop. Then I move the Field manually.
Will split the points - thanks for your help

Chris
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.