Results 1 to 15 of 15
  1. #1
    Join Date
    May 2007
    Location
    Calgary, Canada
    Posts
    22

    Unanswered: Access table not sorting new records with AddNew

    Hello All....

    I am using VBA to create records in a table, similar to below:

    rsxxx.movelast
    rsxxx.addnew
    rsxxx.edit
    rsxxx.update

    Around 20% of the time, when I look back at the table the rows are not in the correct order. Does anyone have any ideas on what I am missing?

    thanks,
    Craig.
    Last edited by C-COOP; 05-15-07 at 16:58.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Is the table being opened by someone, re-sorted by a different column, and then the table is saved? If you open the table, re-sort by a different column, and then save it, it saves that sorting method.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    May 2007
    Location
    Calgary, Canada
    Posts
    22
    No one touches this table. It is a system table! It just seems when I use AddNew it doesn't always add the record to the bottom of the table....

  4. #4
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Sorting of Tables in Access

    Access maintains various indexes on tables. Some are created automatically to facilitate queries, etc. Others can be added by the user. I am not sure of how Access evaluates the standing of these indexes, but if you open the table by double-clicking on it you will see it sorted in one order or another. If you use the A-Z sorting buttons to rearrange the data, this sort order can be changed and can be stored. When you open the table again, the same sort order will apply. In the absence of all else, I think autonumber fields become the de facto sort order.
    If you really need the records to be displayed in order of entry, you can try adding an autonumber field, if you have not got one already. These fields can be added restrospectively.

  5. #5
    Join Date
    May 2007
    Posts
    38
    yep

    its important to realise that access has no concept of an "order". Everything is a set of data meeting certain criteria, and we apply whatever order we need to that dataset

  6. #6
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    In other words, Access does not care about order in a table. If you need to see thing in a certain way or order, you need to use a query.

    C

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    agree whole heartedly. with the previous contributors, a SQL table has no inherent order.. its just a meaningless collection of data. if you need the records in a specific order then you need to specify an order as part of the sql that retrieved the data in the first place.

    if you dont then you leave the choice for the order to the sql engine.. it may come back indexed, it may come back according to he primary key, it may come back according to the the last used index (which 'just' happened to be in memory from the last access of that data)... y'never ever know.

    so if its important be explicit.

    when appending new records to an exisiting recordset its entirely possible that the new records will not be in sort order. say if you did specify a sort order, then add new records to the record set, its highly likely that those new records would be added at the end of the existing recordset, in the order they were added to the recordset, irrespective of any order you specify.

    moral of the tale.. if you are then wanting to go through and find say if the record already exists, then you are better off going back to the original data and runnign a query for that exact record. bear in mind if you had two people doing the same process then the data they have in their recordset reflects the data at the time ran that query, plus any changes or alterations they may have made to the recordset.. it will; not inlcude any changes or alterations done by the other user if there are any done after the first user 'grabbed' their records. if you need the freshest data.. then requery.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just picking up on this
    Quote Originally Posted by C COOP
    rsxxx.movelast
    rsxxx.addnew
    rsxxx.edit
    rsxxx.update
    edit and update, eh? I think if might simply be a typo and you atually meant insert..?

    And as I believe Brett once famously said
    Quote Originally Posted by Brett Kaiser
    The physical order of data in a database has no meaning
    So if you want it ordered - query it
    George
    Home | Blog

  9. #9
    Join Date
    May 2007
    Location
    Calgary, Canada
    Posts
    22
    Quote Originally Posted by georgev

    edit and update, eh? I think if might simply be a typo and you atually meant insert..?
    Now I am confused again, should it not be:

    AddNew - add the new record
    Edit - edit the new record
    Update - commit changes

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by C-COOP
    It is a system table! It just seems when I use AddNew it doesn't always add the record to the bottom of the table....
    Eeek - do you mean a Microsoft System table or one of your own system tables?


    A set of data in relational theory is inherently unordered. As such typically in an RDBMS the physical order of data in a database is meaningless and not related to any logical order. Now - I have had a suspicion for some time that since JET is file based the order of data is significant and the order the data is displayed is always the same as the order in which it is stored (assuming there is no order by clause of course) which in turn is based on the primary key (assuming the table has one).

    I know the set theory "but order has no meaning!" mantra - just curious if anyone knows how JET operates in this regard.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    May 2007
    Posts
    38
    its nothing to do with the sort order, but offhand i think with a dao recordset its either

    existing record
    rst.edit

    rst!etc

    rst.update

    or for a new record
    rst.addnew

    rst!etc

    rst.update

    you also need to trap errors in case update fails (duplicate keys etc)

  12. #12
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    A Jet primary key index is a kind of clustered index. Clustered means that the physical order of the data/table matches the index, ensuring that related records are stored physically close to each other (cluster). I think that in older systems, such as this was achieved with "padding" the table with space, so that new records could be added "in the correct order".

    Jet doesn't add this extra space, so rows are added in time sequence, I think, and the physical reordering (Primary Key sort order) is done when you do a Compact & Repair. For it to have impact on speed of data retrieval, one would probably also need to defragment the disk often

    Some will use this to achieve the exact opposite - build relationship on unique non null indices, and use the primary key designation to "spread related records" to avoid locking conflicts.

    However, in the Access UI, I'm led to believe that when opening a table, what actually happens, is a "SELECT * FROM theTable" to populate the datasheet (first populating enough records to display one screen, then continue the retrieval until all records are fetched, at which point the # of records thingie is populated), where the ORDER BY clause is probably initially the primary key, but with the possibility of saving your own sort order (hit the sort button, save on close). This saved sort order, is probably a Access GUI thing, as it is not the order you get when you retrieve the table programmatically.

    So - I think you have one sort order for Jet tables (just after a Compact & Repair, it will be Primary Key sort order, but it will most probably be a mix of Primary Key sort order and time sequence order based on how long ago the last Compact & Repair was conducted), and one sort order for the Access interface (saved after changing sort order of the table in the Access interface).
    Roy-Vidar

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Just picking up on this

    Quote:
    Originally Posted by C COOP
    rsxxx.movelast
    rsxxx.addnew
    rsxxx.edit
    rsxxx.update


    edit and update, eh? I think if might simply be a typo and you atually meant insert..?


    do not .movelast (tables are intrinsically unordered as several people have said = .movelast is a waste of clock cycles)

    do not .edit after .addnew (.addnew positions you de-facto on the appended record in edit mode until you issue the .update)

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    May 2007
    Location
    Calgary, Canada
    Posts
    22
    Quote Originally Posted by izyrider
    Just picking up on this

    Quote:
    Originally Posted by C COOP
    rsxxx.movelast
    rsxxx.addnew
    rsxxx.edit
    rsxxx.update


    edit and update, eh? I think if might simply be a typo and you atually meant insert..?


    do not .movelast (tables are intrinsically unordered as several people have said = .movelast is a waste of clock cycles)

    do not .edit after .addnew (.addnew positions you de-facto on the appended record in edit mode until you issue the .update)

    izy

    Ok... thanks.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks Roy. This sort of info is easy to come across for enterprise\ server rdbmss but seems kind of ethereal when it comes to Access
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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