Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Compacting is Corrupting Table with PrimaryKey of DateTime

Discussion in 'Information Technology' started by dw85745@gbronline.com, Nov 8, 2005.

  1. dw85745@gbronline.com

    dw85745@gbronline.com
    Expand Collapse
    Guest

    PROBLEM

    Compacting is Corrupting Table with PrimaryKey of DateTime

    Prior to compacting (copy/pasting a new table), DAO MoveFirst returns
    the first record
    in the table.
    After compacting DAO MoveFirst returns Other Than the first record (the
    record being returned a good record but it appears compacting is
    changing the pointer to which MoveFirst applies)>

    BACKGROUND

    OS: Win98
    Access: Office97
    Control Panel/Regional Settings/Date: 1926 - 2025
    KB240244 says: "The low end of the operational date range for Jet 3.5
    is the year 200, and the high end of the operational date range is the
    year 2038."

    Table Format:

    fldHistDate PrimaryKey DateTime
    fldHistOpen Number->Single
    fldHistHigh Number->Single
    fldHistLow Number->Single
    fldHistClose Number->Single

    Table Date Period: 1/31/1928 - 11/4/2005

    QUESTIONS:
    1) What impact (or how) does Control Panel/Regionsl Settings Date
    affect the Access97 Database?
    2) Why is compacting causing this table to corrupt and is there a
    solution to resolve this?
     
  2. Norman Yuan

    Norman Yuan
    Expand Collapse
    Guest

    When you say DAO.RecordSet.MoveFirst, is the RecordSet sorted in any way? if
    not, it is not guaranteed the RecordSet gives you a set of records in the
    same order. Actually, table stores records in no particular order at all. It
    is you, the developer's responsibility to make sure the records retrieved
    from database are in certain order, if desired.


    <dw85745@gbronline.com> wrote in message
    news:1131370754.839477.152430@g44g2000cwa.googlegroups.com...
    > PROBLEM
    >
    > Compacting is Corrupting Table with PrimaryKey of DateTime
    >
    > Prior to compacting (copy/pasting a new table), DAO MoveFirst returns
    > the first record
    > in the table.
    > After compacting DAO MoveFirst returns Other Than the first record (the
    > record being returned a good record but it appears compacting is
    > changing the pointer to which MoveFirst applies)>
    >
    > BACKGROUND
    >
    > OS: Win98
    > Access: Office97
    > Control Panel/Regional Settings/Date: 1926 - 2025
    > KB240244 says: "The low end of the operational date range for Jet 3.5
    > is the year 200, and the high end of the operational date range is the
    > year 2038."
    >
    > Table Format:
    >
    > fldHistDate PrimaryKey DateTime
    > fldHistOpen Number->Single
    > fldHistHigh Number->Single
    > fldHistLow Number->Single
    > fldHistClose Number->Single
    >
    > Table Date Period: 1/31/1928 - 11/4/2005
    >
    > QUESTIONS:
    > 1) What impact (or how) does Control Panel/Regionsl Settings Date
    > affect the Access97 Database?
    > 2) Why is compacting causing this table to corrupt and is there a
    > solution to resolve this?
    >
     
  3. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    Norman Yuan wrote:
    > Actually, table stores records in no particular order at all.


    Actually, the data is stored in a highly predictable order: when
    compacted the data is stored in PK order and subsequent rows are stored
    in order of date/time inserted:

    New Features in Microsoft Jet Version 3.0
    http://support.microsoft.com/default.aspx?scid=kb;en-us;137039

    Should the OP rely on the physical ordering? Probably not. Is it true
    that 'a table stores records in no particular order at all'? No.
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On 7 Nov 2005 05:39:14 -0800, dw85745@gbronline.com wrote:

    >After compacting DAO MoveFirst returns Other Than the first record (the
    >record being returned a good record but it appears compacting is
    >changing the pointer to which MoveFirst applies)>


    If you are assuming that the Table is sorted in any particular
    order... lose that assumption. Access will store the records on disk
    in any order that is convenient for the optimizer. It might be in
    Primary key order or it might not.

    If you want the record returned by MoveFirst to be the first record
    chronologically, you must - no option, no choice - use a Query sorted
    by the date field as the source of the recordset - not the table.

    This is not corruption, this is just the way Access and JET work.

    John W. Vinson[MVP]
     
  5. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    dw85745@gbronline.com wrote:
    > PROBLEM
    >
    > Compacting is Corrupting Table with PrimaryKey of DateTime
    >
    > Prior to compacting (copy/pasting a new table), DAO MoveFirst returns
    > the first record
    > in the table.
    > After compacting DAO MoveFirst returns Other Than the first record
    > (the record being returned a good record but it appears compacting is
    > changing the pointer to which MoveFirst applies)>
    >
    > BACKGROUND
    >
    > OS: Win98
    > Access: Office97
    > Control Panel/Regional Settings/Date: 1926 - 2025
    > KB240244 says: "The low end of the operational date range for Jet 3.5
    > is the year 200, and the high end of the operational date range is the
    > year 2038."
    >
    > Table Format:
    >
    > fldHistDate PrimaryKey DateTime
    > fldHistOpen Number->Single
    > fldHistHigh Number->Single
    > fldHistLow Number->Single
    > fldHistClose Number->Single
    >
    > Table Date Period: 1/31/1928 - 11/4/2005
    >
    > QUESTIONS:
    > 1) What impact (or how) does Control Panel/Regionsl Settings Date
    > affect the Access97 Database?
    > 2) Why is compacting causing this table to corrupt and is there a
    > solution to resolve this?


    You need to provide for a sort order if you want records in any
    particular order. Don't try to figure out how the records are or will be
    stored. You will save yourself a lot of trouble if you just assume that a
    table is a bucket of data not a list.

    You can sort data in queries in many different ways.

    --
    Joseph Meehan

    Dia duit
     
  6. dw85745@gbronline.com

    dw85745@gbronline.com
    Expand Collapse
    Guest

    So if I understand correcty (please confirm):

    If I write the records to an Access Table in date order with a
    PrimaryKey of DateTime,
    that even though they show in DateTime order when the table is opened,
    and that the first record is the one I want, that DAO.MoveFirst
    may NOT return this first record but some other record -- Per Mr. Yuan
    -- this is PK (whatever this stand for order?
     
  7. dw85745@gbronline.com

    dw85745@gbronline.com
    Expand Collapse
    Guest

    Thanks for responding.

    This goes counter to everything I know.

    Unless I've totally misunderstood Access (which could be the case) you
    can read the table directly without the need for a query.

    If you Seek a record, then .MoveNext should give the next record based
    on
    the PrimaryKey. Conversely, if the PrimaryKey is date, the first
    record returned with .MoveFirst should be the earliest date. This is
    true prior to compacting.

    Can you explain what compacting is doing to change this order??

    Thanks
    David
     
  8. Norman Yuan

    Norman Yuan
    Expand Collapse
    Guest

    I did not know compacting will put records in PK order. as you suggested.
    However, the OP seems against this. The wise thing to do is, if you care the
    record order, you ALWAYS sorted by yourself. Assumption of record order
    retrieved from database is dangous.

    <peregenem@jetemail.net> wrote in message
    news:1131380132.285122.310680@g47g2000cwa.googlegroups.com...
    >
    > Norman Yuan wrote:
    >> Actually, table stores records in no particular order at all.

    >
    > Actually, the data is stored in a highly predictable order: when
    > compacted the data is stored in PK order and subsequent rows are stored
    > in order of date/time inserted:
    >
    > New Features in Microsoft Jet Version 3.0
    > http://support.microsoft.com/default.aspx?scid=kb;en-us;137039
    >
    > Should the OP rely on the physical ordering? Probably not. Is it true
    > that 'a table stores records in no particular order at all'? No.
    >
     
  9. Norman Yuan

    Norman Yuan
    Expand Collapse
    Guest

    If you care about the record order retrieved from database (no matter what
    type of database), you ALWAYS sort it your self. Access get the records
    through DAO (or ADO) recordSet with an underline "SELECT...FROM..." query.
    It may or may not sort the records on PK (or the order when record is
    written). But why bother to guess? why not place yuor own "...ORDER BY..."
    if you want thing in order?

    <dw85745@gbronline.com> wrote in message
    news:1131383564.323888.190210@g47g2000cwa.googlegroups.com...
    > So if I understand correcty (please confirm):
    >
    > If I write the records to an Access Table in date order with a
    > PrimaryKey of DateTime,
    > that even though they show in DateTime order when the table is opened,
    > and that the first record is the one I want, that DAO.MoveFirst
    > may NOT return this first record but some other record -- Per Mr. Yuan
    > -- this is PK (whatever this stand for order?
    >
     
  10. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    dw85745@gbronline.com wrote:
    > Thanks for responding.
    >
    > This goes counter to everything I know.
    >
    > Unless I've totally misunderstood Access (which could be the case) you
    > can read the table directly without the need for a query.


    You can, but you should not rely on that to tell you anything about the
    order of the data.

    Consider this. Everyone who has replied to your question has said the
    same thing. That should be a good hint.

    >
    > If you Seek a record, then .MoveNext should give the next record based
    > on
    > the PrimaryKey. Conversely, if the PrimaryKey is date, the first
    > record returned with .MoveFirst should be the earliest date. This is
    > true prior to compacting.
    >
    > Can you explain what compacting is doing to change this order??
    >
    > Thanks
    > David


    --
    Joseph Meehan

    Dia duit
     
  11. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    John Vinson wrote:
    > Access will store the records on disk
    > in any order that is convenient for the optimizer. It might be in
    > Primary key order or it might not.


    If the question is, 'Does the optimizer have control over how data is
    physically stored on disk?' then the answer is 'No'.
     
  12. david epsom dot com dot au

    david epsom dot com dot au
    Expand Collapse
    Guest

    "If /recordset/ refers to a table-type Recordset (Microsoft Jet workspaces
    only), movement follows the current index. You can set the current index by
    using the Index property. If you don't set the current index, the order of
    returned records is undefined."


    If you are using a TABLE TYPE recordset, set the current index first.

    With rstProducts
    ' Set the index.
    ..Index = "PrimaryKey"


    If you are using a SNAPSHOT or DYNASET recordset, specify the sort order in
    the select query.


    Otherwise, the order of the returned records is undefined.

    (david)




    <dw85745@gbronline.com> wrote in message
    news:1131383564.323888.190210@g47g2000cwa.googlegroups.com...
    > So if I understand correcty (please confirm):
    >
    > If I write the records to an Access Table in date order with a
    > PrimaryKey of DateTime,
    > that even though they show in DateTime order when the table is opened,
    > and that the first record is the one I want, that DAO.MoveFirst
    > may NOT return this first record but some other record -- Per Mr. Yuan
    > -- this is PK (whatever this stand for order?
    >
     
  13. dw85745@gbronline.com

    dw85745@gbronline.com
    Expand Collapse
    Guest

    Thanks All for responses.

    Special thanks to David Epsom. Confirmed my testing for a Table type
    Recordset.

    Per Microsoft:

    If recordset refers to a table-type Recordset (Microsoft Jet workspaces
    only), movement follows the current index. You can set the current
    index by using the Index property. If you don't set the current index,
    the order of returned records is undefined.

    ------------------------------------------------------
    Up to this point I've never blown up with querying a table-type
    RecordSet with a Key Field WITHOUT setting the Index prior to the
    query..

    Learned a Hard Lesson -- Will NOW set current index prior to querying
    any Table Recordset.
     

Share This Page