ChrisO
Registered User.
- Local time
- Today, 20:55
- Joined
- Apr 30, 2003
- Messages
- 3,202
High-speed Timestamps.
Forward:
a. Access has a limitation on timestamps of 1 second and it is difficult to query below that limitation. Even querying on 1 second can be fraught with error.
b. Some systems, process control systems, can write data to a Jet table faster than Access, Jet or VBA can retrieve them in the order of them being written based purely on a resolution of a second timestamp.
c. In order to overcome the limitation, of order within the write second, an extra field is necessary.
d. That field is, generally speaking, filled with a value which is the time offset within that second. The time offset starts at 0 offset, at start of second, and progresses at the rate of resolution in time of the offset clock.
e. The offset clock is normally the API call:
Public Declare Function timeGetTime Lib "Winmm.dll" () As Long
But it has a limitation of being between 10 to 15 milliseconds meaning that, currently in that time frame, Jet can store about 15 to 30 records. That means that there is no way to sort on timestamp within the API call time frame and one has to use the primary key to sort. It also means that no knowledge can be gained from the sample times as to their difference in time within that time frame. Hence, for instance, a process control system may log an alarm sequence of, say, 10 records, and there would be no way to determine the first failure alarm which caused it.
f. There is another API call which does a better job:
Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As LARGE_INTEGER) As Long
This API can, currently, return approximately 0.3 milliseconds resolution into the second offset on record write in the attached demo. What that means is that each record is time stamped across two fields, the second stamp and the millisecond offset stamp into that second.
The attached demo relies on the QueryPerformanceCounter function as supplied by the KPD-Team 2001. If you intend to use it, please leave all of their credits in place.
The demo includes a procedure to create sample data of 10000 records. Best leave that alone and start with the query of the data.
The query is, by necessity for this demo, done starting in VBA with procedure:-
Private Sub GetRecordset()
Tested with:-
Version..............: Access 2003.
Regional settings.: English, French, German.
Error handling…...: None.
References……..…: None.
If you have any questions, please ask them in the appropriate forum.
Chris.
Forward:
a. Access has a limitation on timestamps of 1 second and it is difficult to query below that limitation. Even querying on 1 second can be fraught with error.
b. Some systems, process control systems, can write data to a Jet table faster than Access, Jet or VBA can retrieve them in the order of them being written based purely on a resolution of a second timestamp.
c. In order to overcome the limitation, of order within the write second, an extra field is necessary.
d. That field is, generally speaking, filled with a value which is the time offset within that second. The time offset starts at 0 offset, at start of second, and progresses at the rate of resolution in time of the offset clock.
e. The offset clock is normally the API call:
Public Declare Function timeGetTime Lib "Winmm.dll" () As Long
But it has a limitation of being between 10 to 15 milliseconds meaning that, currently in that time frame, Jet can store about 15 to 30 records. That means that there is no way to sort on timestamp within the API call time frame and one has to use the primary key to sort. It also means that no knowledge can be gained from the sample times as to their difference in time within that time frame. Hence, for instance, a process control system may log an alarm sequence of, say, 10 records, and there would be no way to determine the first failure alarm which caused it.
f. There is another API call which does a better job:
Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As LARGE_INTEGER) As Long
This API can, currently, return approximately 0.3 milliseconds resolution into the second offset on record write in the attached demo. What that means is that each record is time stamped across two fields, the second stamp and the millisecond offset stamp into that second.
The attached demo relies on the QueryPerformanceCounter function as supplied by the KPD-Team 2001. If you intend to use it, please leave all of their credits in place.
The demo includes a procedure to create sample data of 10000 records. Best leave that alone and start with the query of the data.
The query is, by necessity for this demo, done starting in VBA with procedure:-
Private Sub GetRecordset()
Tested with:-
Version..............: Access 2003.
Regional settings.: English, French, German.
Error handling…...: None.
References……..…: None.
If you have any questions, please ask them in the appropriate forum.
Chris.
