Compacting DB is Corrupting Table When Key Field DateTime

[dw85745]

I finally found out what's going on. I need to know if there is a
fix??.

PROBLEM
After compacting a table defined with a Key Field of DateTime the table
is
loosing its pointer to the beginning (first) record.

BACKGROUND

OS: Win98
Access Version: Access97
Setting/ Control Panel/Regional Settings/Date set for period 1926 to
2025
Per Q240244 Access 97 is supposed to handle dates properly for the
inclusive period of 200 to 2038.

Table format is:

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

Record Period for table: 1/3/1928 to 11/4/2005

MoveFirst does NOT solve the problem.
Prior to compacting, .MoveFirst points to the first record dated
1/3/1928.
After compacting (.MoveFirst) points to the record dated 2/27/1929.
Even if records are deleted to beginning with 1940, after compacting,
MoveFirst points to some later record.
I have not determined is the record offset is consistent (exactly the
same if 1940 is used)
but a rough estimate indicates it is.
-------------------------------------------------------------------------------------------------

QUESTIONS:

1) Is there a way to correct this, if so how?
2) What relationship does the Control Panel Regional settings have to
Access97 -- in other words if the date period is changed in Control
Panel how will it affect Access97?


Thanks
David

 

[Marshall Barton]

I finally found out what's going on. I need to know if there is a
fix??.

PROBLEM
After compacting a table defined with a Key Field of DateTime the table
is
loosing its pointer to the beginning (first) record.

BACKGROUND

OS: Win98
Access Version: Access97
Setting/ Control Panel/Regional Settings/Date set for period 1926 to
2025
Per Q240244 Access 97 is supposed to handle dates properly for the
inclusive period of 200 to 2038.

Table format is:

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

Record Period for table: 1/3/1928 to 11/4/2005

MoveFirst does NOT solve the problem.
Prior to compacting, .MoveFirst points to the first record dated
1/3/1928.
After compacting (.MoveFirst) points to the record dated 2/27/1929.
Even if records are deleted to beginning with 1940, after compacting,
MoveFirst points to some later record.
I have not determined is the record offset is consistent (exactly the
same if 1940 is used)
but a rough estimate indicates it is.
-------------------------------------------------------------------------------------------------

QUESTIONS:

1) Is there a way to correct this, if so how?
2) What relationship does the Control Panel Regional settings have to
Access97 -- in other words if the date period is changed in Control
Panel how will it affect Access97?

Corrupting is a pretty strong accusation, that's rarely
true. Before going down that road, let's analyze what
you're doing.

Since you're using .MoveFirst, you must be opening a
recordset. If that's a safe assumption, I will then presume
you are opening a table type recordset. If you open the
recorset based on a query with the date field sorted in
ascending order, you should not have this problem.

If that's not what's happening, please post the code and
details of the recorset that you are actually using.