OrdinalPosition (Field Order) Question

[GGleason]

I have copied a table to another name within Access. I have successfully added a field to the new table.

However the added field is the rightmost, but I want it to be the leftmost when the table is opened. I have tried to set the OrdinalPosition to 0 or 1 for the newly created field, but at best it makes the field the second field from the left instead of the desired leftmost position.

Has anyone met and conquered this problem?

tia,
GGleason

 

[MichaelRed]

I guess this demonstrates the !use of reference material.

design view may offer some insight -but I sincerely doubt it.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[GGleason]

MichaelRed,

Here is the funny thing, in design view it displays as advertised (and as a recordset and a query), but not in datasheet view.

My thinking at this point is that this may be a bug in Access (at least in 97 SR-2).

Have you worked with the OrdinalPosistion property, and if so, have you had success in this area?

GGleason

 

[MichaelRed]

Generally speaking, I have only ever used the ordinal position in code to generate cross reference maps of field usage within an application. In this respect, the only real use is to obtain the name property of the fields, and to act as the loop boundary in that process. Use of the "Ordinal" position for other purposes seems a bit foregin to me.

A BRIEF excursion into Ms. Help Speak does return the following interesting information:

Generally, the ordinal position of an object that you append to a collection depends on the order in which you append the object. The first appended object is in the first position (0), the second appended object is in the second position (1), and so on. The last appended object is in ordinal position count – 1, where count is the number of objects in the collection as specified by the Count property setting.

You can use the OrdinalPosition property to specify an ordinal position for new Field objects that differs from the order in which you append those objects to a collection. This enables you to specify a field order for your tables, queries, and recordsets when you use them in an application. For example, the order in which fields are returned in a SELECT * query is determined by the current OrdinalPosition property values.

You can permanently reset the order in which fields are returned in recordsets by setting the OrdinalPosition property to any positive integer.

Two or more Field objects in the same collection can have the same OrdinalPosition property value, in which case they will be ordered alphabetically. For example, if you have a field named Age set to 4 and you set a second field named Weight to 4, Weight is returned after Age.

Noting the first and last paragraphs, it does appear that the 'observed' response is consistient with the documentation.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Rolliee]

a recordset opens in the order it is called in the
SELECT. Normally you allow ACCESS to do this. Should you want to change the order, just reorder as you specifically call the fields in the query.

rollie@bwsys.net

 

[GGleason]

MichaelRed and Rolliee,

Thanks for the feedback!

GGleason