Need more exact time 1

[nagyf]

I have log tables of changes, with unique index (who,where,when).
The when field has date type. It stores the date+time until seconds.
It works well when I enter changes manually.
But if I want to log changes made by a query they can occur within a second, so the unique index causes an error.

I overcomed the difficulty using more fields in the index :
(who,where,when+key fields of the logged table).

I do not want to use counter field.

Is there any way to ask for hundreds or thousands of second from the system clock and form a more exact moment like 2002.10.09:09.26.10+12/100? [tt]
Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' "The goal of the life is the struggle itself"
[/tt]

 

[KenReay]

Hi

debug.Print format(now(),"dd/mm/yy hh:mm:ss.s&quot
09/10/02 08:47:14.14

is that what you need? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[MichaelRed]

Obviously (see below) not.

? format(now(),"dd/mm/yy hh:mm:ss.ssss")
09/10/02 09:33:59.5959
09/10/02 09:33:58.5858
09/10/02 09:33:56.5656
09/10/02 09:33:55.5555
09/10/02 09:33:52.5252
09/10/02 09:33:44.4444

Note that "SS" is simply reeated three times, w/ a decimal point after the first occurance.

In general, to get time accurate to less than one second is neither accurate or useful. The normal 'cloclk' in MS is -at best - accruate to ~ 18 milliseconds, so accuracy beyond this surely needs a third party piece of hardware. Unfortunatly, even this 'precision' is seldom achieved, as the 'clock' is not a high priority item in Win. If you are adding/editing records via an SQL statement, MS A will generally NOT relinquish control to the O.S., so the clock will not update for each record manipulation, making the time stamp the same for some (dependent on the system configuration) number of records.

I am interested in how you are achieving the time-stamp for the record addition / updating via queries.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[PaulBricker]

Michael, to get a Timestamp when appending records, you set a field in the Table to be appended to that is Date/Time or Number formatted to Double depending on how you want to see the data. Then in the Append Query you set a field on the Field Row to MyTimeStamp:Now() and set the Append To line to your Date/Time Field in the Table.
When you run the Append Query, it appends the Date/Time to your Table. Doesn't work as a standalone Primary Key with the field set to either a Date/Time or Double.
If you could slow down the process by sending something to a Function that would allow for the clock to move, some DoEvents thing, you might be able to accomplish what your looking for. Just a thought.

Paul

 

[SgtJarrow]

Take a look at faq705-2292. This class module is much more accurate and may get you the values you want....I have used it in the past to record function process times, etc.

If you convert your query to a SQL process, and update you log table through this....you can initiate the stopwatch (that's what this class is called) at the very beginning and then work from that to evaluate times, etc....

You will probbaly have to do some work to get it the way you need the data returned, but it shuold put you on the right track. Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[MichaelRed]

PaulBricker,

Kinda the "knee jerk" reaction. Works FINE for manual data entry. I was asking to see what process Ferenc Nagy implemented. Again, the request is to (somehow) get a timestamp which could be used as a unique (primary) key. This canot be done -even with manual data entry- unless certain precautions are taken (for multiple data entry personnel). It certainly won't work (as a unique key) with a query. Even the DoEvents in some (bogus/dummy) procedure within the SQL process is (probably) not effective. At the VERY least, you would need to send some field(s) to the procedure AND within the procedure, check that the time stamp incremented by N where N > ~ 3e-4 and / or use something other than the default date/time functions to arrive at the timestamp.

As noted by mstrmage1768 there ARE routines which can 'improve' the resoloution of a time stamp. To the best of my knowledge, these are also limited to the tick rate of the system clock - unless third party hardware is available.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[PaulBricker]

Michael, I didn't think you really needed a lesson in TimeStamps. I'll admit to taking license with Ferenc post but his process can't be much different than what I've assumed. Either way, I agree that a TimeStamp is not the way to set up a Primary Key.

Paul

 

[nagyf]

You state that that now() is more accurate than seconds.
But if I execute a query, it will be executed *once*, so the same value will appear in all records. That is impossible in case of unique key. [tt]
Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' "The goal of the life is the struggle itself"
[/tt]

 

[SgtJarrow]

Nagyf,

You need to look back at what Michael and Paul have said....

As they have nicely pointed out, using a "time stamp" is not the way to set up your primary key. You can easily fix your problem by adding an autonumber field to your table, and making it the primary key. That should be the only change you have to make. You will now have a meaningless autnumber field, that uniquely identifes each record. And each record can have the same "time stamp", as should be the case if you process them all at the same time. Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[MichaelRed]

PaulBricker,

To Say I don't need a lesson in TimeStamping is not correct. I may not need that specific lesson. On the other hand, there are many lessons I have not yet gotten through. One of my reasons for 'lurking about' is the fact that I do learn from the postings.

I was NOT trying to be un-kind, just wanting to know if what technique/approach was being suggested. It SOUNDED like (to me) there was some approach which could be used to guarntee a unique time stamp - even through an append or update query.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[PaulBricker]

Michael, no offense taken and no offense intended. I think everyone is saying the same thing from slightly different perspectives. It's probably possible using Roberts StopWatch Function to create a Unique Identifier, but it isn't necessarily advisable. Seems to me that either way you are using a number as the PK so why go with a Double Precision value when AutoNumber will do the same thing.

Paul

 

[SgtJarrow]

Michael and Paul,

Your discussion inspired me to give the time/stopwatch thing a whirl....Using the following, still couldn't make it work.....it is processing too fast that it still wants to assign the same value as PK

Table1
TimeStamp Text PK
Letter Text

Table
Letter Text (26 Records, Just Letters A to Z)

Using the following code:
The StopWatch class as mention above and

Public Sub AppendLetters()

Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim sw As StopWatch

Set sw = New StopWatch
Set db = CurrentDb
Set rs1 = db.OpenRecordset("SELECT Table2.Letter FROM Table2;", dbOpenDynaset)
Set rs2 = db.OpenRecordset("Table1", dbOpenDynaset)

sw.StartTimer

With rs1
.MoveFirst
Do While Not .EOF
rs2.AddNew
rs2.Fields(0) = CStr(Format(Now(), "hh:nn:ss&quot) & ":" & CStr(Format(sw.EndTimer, "0000&quot)
rs2.Fields(1) = .Fields(0)
rs2.Update
.MoveNext
Loop
End With

Set rs2 = Nothing
Set rs1 = Nothing
Set db = Nothing

End Sub
Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[MichaelRed]

PaulBricker,

To Say I don't need a lesson in TimeStamping is not correct. I may not need that specific lesson. On the other hand, there are many lessons I have not yet gotten through. One of my reasons for 'lurking about' is the fact that I do learn from the postings.

I was NOT trying to be un-kind, just wanting to know if what technique/approach was being suggested. It SOUNDED like (to me) there was some approach which could be used to guarntee a unique time stamp - even through an append or update query.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

mstrmage1768 (a.k.a Robert L. Johnson III).

In an earlier post, I noted that to attempt the approach you need to call a function WITH A PARAMETER which is a field from the source(s) from within the process (query). Ms. A. may be somewhat ancient as far as software goes, however 'she' is not totally senile. The "optimizer" will reorganize the process to remove many "constant" functions. You can FOOL Ms. A. by sending a FIELD to a function. Since the field MAY change, it will not get 'optimized' and thus ACTUALLY call the procedure. Another item I noted was that you would need the (called) procedure for the timestamp to check that the value actually changed.

I have rather (stridently) argued against the original stated intent, and remain confident that -as originally proposed- this cannot work. I have posted (faq700-184) which would be adaptable to generating a custom Unique value. Using SOME adaptation of this (faq) approach could combine the time stamp with additional information to generate a unique key which included a date/time. WHY anyone would do so remains a mystery (to me), as any unique value is sufficient as a primary key, and making it monotomic provides the specific ordering. Adding any old common timestamp (as a seperate field) in the record provides the (approximate) date/time of the activity.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[SgtJarrow]

MichaelRed,

I agree, and would never consider a "time stamp" entry as a primary key......And I also agree that autonumber as PK in a multiuser environment is a no-no....

Me.SoapBox.Value = True
I think we have come full circle on this topic, and can probably agree that education on database design for a multiuser environment is the key here. Although we all (PaulBricker, Yourself and I) have put forth various comments and suggestions, in the end I feel nagyf may want to step back and rethink his problem a bit. Learn about the processes, and potential problems, with what he is attempting. Seek alternatives, delve into resource web pages for FAQs such as yuor, the big M's white pages, etc. Ultimately he will discover the error in his design and have to fix it, and I think you sig line:
There is never time to do it right but there is always time to do it over
is perfect for this example.
Me.SoapBox.Value = False

That all being said, nagyf.....good luck and feel free to ask questions as you need. We are all her only to help....

Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[PaulBricker]

All I can add is, I had to Compact and Repair after that.

Paul

 

[nagyf]

The thread went far from my intention.
I have now the following primary key for LOG_FIRM
table:
computer+userid+when.
E.g. PC1,NF,2002.10.18. 10:20:21[,?].

I have the following outcomoes:
1. adding firmcode to the key. If the clock has not changed manually then the chance for unique key is much better.
2. adding a counter field
2.a. which will be the unique key.
2.b. just for see the changes.
3. remove the unique settings from the computer+userid+when key. In this case logging still will work even if I replace the value of non-key field with ^H in 1000 records in the FIRM table.


[tt]
Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' "The goal of the life is the struggle itself"
[/tt]

 

[MichaelRed]

Which seems to re-enforce the rambling discussion points.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over