[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1326
  • Last Modified:

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?
0
chrisezard
Asked:
chrisezard
  • 4
  • 4
  • 3
  • +1
2 Solutions
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now