Results 1 to 4 of 4
  1. #1
    keviny04 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Apr 2015
    Posts
    128

    Questions on field order in a table

    I'm an advanced user and I haven't found an answer to this question regarding the "order" of fields in a table, as in which is the first field, second field, and so on. If I refer to the fields in a table as TableDef.Fields(0), .Fields(1), .Fields(2), and so on, what order are 0, 1, 2, etc. referring to? Are they the order of the fields as listed in the table design view? The topmost field is 0, second is 1, and so on? What if I move a field to another row in table design view? Will Fields(0), (1), (2), etc., refer to the new order, or the previous order, or the order when the table was first created (first saved?)?



    Another question about the same issue is this. If I have an INSERT query (or Append query):

    INSERT INTO Table1 SELECT value1, value2, value3, ...

    What will happen? Note that I don't specify the field names. I assume this will insert value1 to field1, value2 to field2, and so on, where field1, 2, 3, etc., is this mysterious order of the fields that I can't figure out.

    So can anyone shed some light on how field orders are determined in these different cirumstances (and perhaps others I haven't thought of)?

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    To the best of my knowledge it's the order in the design view.

    As to your Insert query question - try it and see what happens...
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I also believe that field order is the order given in the table design/definition.
    And it would be last saved order in my view.

    That's part of the reason to explicitly state field name when inserting records into a table. For example, if someone has altered the field order, an INSERT without field names could be assigned to the incorrect field.

    But there is no order to records in a table. If order of records is required use a query with an ORDER BY
    clause.
    Just my $.02

  4. #4
    keviny04 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Apr 2015
    Posts
    128
    Just did some testing and found out that after changing field order in table design, you have to close and re-open the database for the new order to take effect; otherwise the old order is still effective. Also, instead of using .Fields(n), maybe it's better to use .Fields("ActualFieldName")

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Table/field display order in Query Design
    By puddinhead13 in forum Access
    Replies: 5
    Last Post: 04-20-2016, 06:57 PM
  2. Replies: 2
    Last Post: 03-07-2016, 05:02 PM
  3. Replies: 23
    Last Post: 09-14-2015, 01:34 PM
  4. sorting table field in ascending order
    By picyx in forum Access
    Replies: 7
    Last Post: 11-18-2014, 02:24 PM
  5. Replies: 1
    Last Post: 04-08-2010, 02:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums