Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2009
    Posts
    7

    Unanswered: How to Sort Table and keep it in new Order ? (change Default Sort Order)

    I have searched the web for 3 hours !! It is so surprising to me that no one else has asked this question - sorry if they have here but I did not find it.

    Anyway I have a Table with 2.6 millions records, indexed. I sorted it by:
    State / City / Street and it is much easier to work with. Then I saved the Table. But every time I open it, it takes 5 minutes to rerun the triple Sort Query. I am so surprised that Access has no way to apply a Sort and simply Save the table "As Sorted" without having to save it as a Query of the Table. Then when you reopen it . . . BAM !! It opens instantly with no 5-minute wait. But No. Apparently that is not the case.

    Is there a trick or a workaround ? I just want to change the Default Sort order so I can isnatntly open the Table and it will open in the Order I desire it to be in. TIA;

    ls

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Is this a locally stored table or one on a network somewhere?
    Me.Geek = True

  3. #3
    Join Date
    Aug 2009
    Posts
    7
    Local drive - but this is not a rights issue, just a limitation of Access that I need a workaround for.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Check your indexes on the table since thats often how it sorts. I've even dumped all the data into a new pre-indexed blank table which has helped with speed (appending the data in the way that coincides with the indexes.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    What you need is a Clustered Index. Unfortunately, it doesn't appear that Access (2007 at least) supports them. This is from the MSDN Office Developer Center:
    Microsoft Access database engine databases ignore the Clustered property because the Microsoft Access database engine doesn't support clustered indexes.
    In SQL Server, a Clustered index would maintain the data in the order of the Index. Without that, there isn't anyway to ensure the data is in any particular order.

    As pkstormy pointed out, correctly designed indexes can help.

  6. #6
    Join Date
    Aug 2009
    Posts
    7
    PKstormy - how would I do what you suggest ? Can you give a few steps? Thanks

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well I suppose you could make the preferred sort order the primary key......
    failing that it seems that you either consider moving to another data storage mechanism..

    or you change your form desing so that rather than retrieving the the whoel data set you retrieve either just the unique states and allow the user to drill down to get what data they need.

    with 2.5 million rows I doubt you will get 'fantastic' performance, especailly if you try to retrieve the whole data set in one hit
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Aug 2009
    Posts
    7
    It seems like one of the FIRST things Microsoft would have done in designing Access, is to allow people to modify Tables and save the modified Table. It is so amazing to me that we are up to Office 2007 and you still cannot simply Sort a Table and save it with the Records arranged in the new order. Seems incredible that you cannot do such a basic thing !!

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not really
    after all there are two separate elements here
    you have Access which is a front end RAD tool, so its not an Access issue as thats to do with the front end, the user interface
    you have JET which is a data storage mechanism, and that follows standards set elsewhere.

    I'd be surprised if the issue is the indexes per se, I'd be far more suspicious of the design of the from that takes 5 minutes to load. if you are using bound forms you'd be surprised ho much work goes on behind the covers.

    As said before I suspect you need to rethink your design paradigm rather than rage at something that is out of your control. not knowing your app (and in truth not wanting to know your app), I suspect you need to redesign it so a minimum number of rows are displayed. if you cannot get the number of rows to, say, less than a couple of hundred (or thousand) then I think you will have severe performance problems irrespective of what storage mechanism you use.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •