Compacting is Corrupting Table with PrimaryKey of DateTime

[dw85745]

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?

 

[Norman Yuan]

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.

 

[peregenem]

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.

 

[John Vinson]

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]

 

[dw85745]

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?

 

[dw85745]

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

 

[Norman Yuan]

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.

 

[Norman Yuan]

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?

 

[peregenem]

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'.

 

[david epsom dot com dot au]

"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]

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.