Results 1 to 6 of 6
  1. #1
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694

    Table Without PK Switching My Index Orders

    All,

    I have a table with no relationships that I want 2 indexes on:



    After saving the table like this and going to DS view, I see this:



    The last record is disobeying the index rules that I set. Then I go back to Design view, I see in the Index dialog that the indexes have been switched around so the DATE field is first ASCENDING. The only thing I can figure out as the problem is that the DATE field has a default value of NOW() in it. Is this what's causing this? Thanks!

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    There is no guaranteed order to a table. Only a query with an OrderBy clause guarantees the order.

  3. #3
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    Well try a compact/repair - and see if that resets your sort order for you.

    Otherwise, as you know, you can highlight that timestamp column and do an a-z sort at any time.

    No explanation I can think of as to why behavior is inconsistent. On the otherhand a database table really has no order, fundamentally. Records are records and order/sort is a display feature for humans - so I wouldn't let it get to you...... since it is a display feature/concept I myself don't bother with it at the table level and only invoke sort/order at the form/report level generally....or sometimes in a query record source - - but there is more than one way to skin the cat....

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by NTC View Post
    Well try a compact/repair - and see if that resets your sort order for you.

    Otherwise, as you know, you can highlight that timestamp column and do an a-z sort at any time.

    No explanation I can think of as to why behavior is inconsistent. On the otherhand a database table really has no order, fundamentally. Records are records and order/sort is a display feature for humans - so I wouldn't let it get to you...... since it is a display feature/concept I myself don't bother with it at the table level and only invoke sort/order at the form/report level generally....or sometimes in a query record source - - but there is more than one way to skin the cat....
    NTC,

    I'm manipulating indexes through code based on accounting standards preference by the user. is that the best way? or can I use sorting with less risk for corruption? (either one will have to be coded)

    and i think I may have solved it too. my testing phases have yielded some risky memory usage. I have left a couple of db objects open at syntax error breaks. my mistake.

  5. #5
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    because SORT is such a standard part of query statements - - I would implement it in your sql when creating the record set. Not by manipulating index/table properties. I will guess (and it is only a guess) that the MS designers don't really view table properties to be intended to be manipulated dynamically by code - so you could find issues here that they won't be eager to resolve. So if you can get it done in the query - best to do it there....

    tis my advice.

  6. #6
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    you're right. what I'm trying to do cut down on the code I have to read later. it's easier to change an index property to desc rather than asc in one line than it is to lookup the method their using, and use conditions to sort the rec object based on the input.

    they can change the acct method on the main screen, so the index will already be set when they go to manipulate inventory. you are right though, properties like that are not intended to be changed in the bg. thanks!

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

Similar Threads

  1. Switching between multiple databases
    By developer11 in forum Access
    Replies: 2
    Last Post: 11-16-2020, 05:56 AM
  2. Replies: 3
    Last Post: 10-11-2011, 01:43 PM
  3. switching int/double
    By giladweil in forum Access
    Replies: 2
    Last Post: 07-05-2010, 01:13 AM
  4. Replies: 4
    Last Post: 06-10-2010, 07:51 AM
  5. Create an index in existing database
    By blip in forum Programming
    Replies: 1
    Last Post: 05-21-2010, 11:23 AM

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