PrimaryKey Failing

[David]

Didn't know what other Access forum to put this in.

I have an Access97 DB with a table and a PrimaryKey of DateTime.

I reviewing my records I noticed several records exist with the exact same
date and time stamp. After the query, the records are added or edited ,
however the add/edit procedure contains a "Resume Next".

Anyone seen this before? -- any solution?

 

[Jeff Boyce]

David

Are you saying that when you open that table in design view, Access tells
you that that particular field is of Date/Time datatype, is marked as the
primary key, AND has the Indexed property set to "Yes (No Duplicates)"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Didn't know what other Access forum to put this in.

I have an Access97 DB with a table and a PrimaryKey of DateTime.

I reviewing my records I noticed several records exist with the exact same
date and time stamp. After the query, the records are added or edited ,
however the add/edit procedure contains a "Resume Next".

Anyone seen this before? -- any solution?

A Date/Time is simply a Double Float number, a count of days and fractions of
a day since midnight, December 30, 1899. As such it is accuarate to a few
microseconds. For example, Now() is 4/17/2009 1:00:58 PM or equivalently
39920.542337963.

However, ?cdbl(#4/17/2009 1:00:59 PM#) is 39920.542349537 - so there are
about four decimals at the end which are sub-second in duration.

As such, you could have two records with "the same" time as displayed, but
actually differing by .0000001 days or so.

This is one good reason not to use Doubles or Date/Time fields as primary
keys!

 

[David]

Jeff Boyce

Are you saying that when you open that table in design view, Access tells
primary key, AND has the Indexed property set to "Yes (No >Duplicates)"?

-----------------------

Thanks exactly what I'm saying

----------------------------
John W. Vinson

I realize datetime is a double.
Your explanation is the same conclusion I came to, just never have seen

Will convert DateTIme to double and verify.

This is one good reason not to use Doubles or Date/Time fields as >primary
keys!

Got a suggestion when trying to track by datetime???

David

 

[Bob Barrows]

Jeff Boyce


Thanks exactly what I'm saying

----------------------------
John W. Vinson


Will convert DateTIme to double and verify.


Got a suggestion when trying to track by datetime???

David

Use an autonumber field to break ties?

 

[John W. Vinson]

Got a suggestion when trying to track by datetime???

Store the datetime (using Now()) but don't make it the primary key. It's
*data*.

 

[David]

First off, copied and pasted both dates from Access to VB, converted dates
to double. They returned the exact same values. No difference that shows
with Debug.Print

-----------------------------
Mr Barrows

Use an autonumber field to break ties?

Not sure how to accomplish this?

I assume the primarykey would be AutoNumber, and an indexed date field of
DateTime. I then would perform a .Seek using the indexed date field,.
But don't see what if anything this would accomplish.

I can easily add based on AutoNumber, the .Seek to edit is the problem

===================
Mr Vinson:

Same goes with the idea of using "Now". I have a number of tables,
each with datetime as PrimaryKey. Each table holds data based on
the ending time (e.g. To the minute, to 30 minutes, to hour, etc.).

David

 

[Bob Barrows]

First off, copied and pasted both dates from Access to VB, converted
dates to double. They returned the exact same values. No difference
that shows with Debug.Print

Why is that a surprise? VB stores datetimes in memory the same way Jet
stores them in the database. And it has the same problem with resolution
of doubles that Access has.

A better test would be to us CDbl to force them to be displayed as
doubles.

Not sure how to accomplish this?

I assume the primarykey would be AutoNumber, and an indexed date
field of DateTime. I then would perform a .Seek using the indexed
date field,. But don't see what if anything this would accomplish.

Huh? I guess I don't understand the problem you are trying to solve.
Exactly what are you trying to accomplish? Prevent the storing of two
records with the same date/time? I doubt that is possible.

I can easily add based on AutoNumber, the .Seek to edit is the problem

===================
Mr Vinson:

Same goes with the idea of using "Now". I have a number of tables,
each with datetime as PrimaryKey. Each table holds data based on
the ending time (e.g. To the minute, to 30 minutes, to hour, etc.).

It was only a matter of "time" before using a Date/Time as a primary key
would cause you problems.