Ordinal Position of DAO Fields

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.

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?

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

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

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.

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

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.

>> 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.

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.

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