[RESOLVED] Solutions to fldDateTime as PrimaryKey

[dw85745]

Re: Access

I have a number of tables where the PrimaryKey is DateTime.

I just noticed that in one of the tables contains multiple records with the same DateTime. Most likely this is because Access only goes out to seconds and the double value that represents DateTime may go out to more precision.

Looking for recommendations on how best to handle both entry and seeking
records?

One thought was to make PrimaryKey AutoNumber and the store DateTime as Text in a yyyymmddhhmmss format and make this unique -- or I guess it could even be the PrimaryKey since a Text field.

Suggestions/Comments/Recommendations?

Thanks
David

[jmcilhinney]

Use an AutoNumber column as PK and store the DateTime values as DateTime values. Simple. Why store DateTime values as text when there's a data type specificly for that type of data?

[dw85745]

jmcilhinney:

Would you not run into the same issue just having a DateTime indexed field?

If you're using Seek to search DateTime, any variance in the double would
not find the record whether defined as PrimaryKey or as a separate Indexed field -- or am I missing something?

[dee-u]

I am just wondering, if it is set as PrimaryKey then how were you able to insert records which have the same date?

[jmcilhinney]

I really don't think it's a good idea to use the time as the primary key, even if you can store with increased precision. If you're saying that the real problem is that Access is unable to store times to the precision you need then it's fairly obvious that Access is not the appropriate choice of database for your application.

I wasn't aware that Access could only store times to seconds. Are you absolutely sure that that's the case?

[dw85745]

dee-u

The reason (IMO) they got inserted is that DateTime is really a double. If the double precision varies then the system thinks its a different datetime but shows in the DB as the same since the DB does Not show dates past seconds.

To my knowledge not even sure if you can use VB to screen past seconds so no way to check for milliseconds for a date type.

Just remembered GetTickCount will do milliseconds, will verify.

jmcilhinney:

See above.

[jmcilhinney]

I'm guessing is that what's actually happening is that the times are simply being displayed in the Access application to seconds. The actual values themselves will contain a greater precision. That simply MUST be the case or, as dee-u said, the values couldnot be inserted as a PK because duplicate keys are not allowed. If you query your database and have a look at the values I think you'll find that those values are more precise than you think. You can probably change the format of the column so that greater precision will be displayed in the Access application too.

[dw85745]

That's what I indicated in my initial post.

Also, to my knowledge, VB won't allow a miiliseconds display (my guess Access too) unless you know something -- will do KB search???

[jmcilhinney]

I'm not sure about Access as I don't use the application itself all that much but VB.NET will certainly let you display fractional seconds to 7 decimal places. I don't know what the deal is with VB6, if that's what you're using, but I'd be very surprised (nay, astonished) if you were limited to whole seconds.

[dw85745]

I believe thats one of the changes from Classic to Net.

Found a good article at DevX on subject. Need time to digest it.
Don't believe it addresses the stripping of the milliseconds though.

Read will have to wait till tomorrow though, tired of looking at the screen

Have a Good Day!

David

[jmcilhinney]

You still haven't actually told us what version of VB you're using. That should always be in your first post so we know what we're dealing with. Then I wouldn't have to post guesses about VB6 if I knew you were using VB.NET and if I knew you were using VB6 then I'd leave it to those with experience with it.

[dw85745]

jmcilhinney:

Will try and annotate VB usage in future. Next to your name used to display the VB of interest, don't know what happened to it.

Considering this thread closed.

See Thread: Seeking typeateTime Fails