Non-Integer time measurement

[Zwoker]

Hi All,

I'm using MS Access 2003. I have been trying to find out why portions of my VBA code have been running slower than expected, using code like the following:

Dim StartTime, EndTime As Date
Dim TimeDelta As Single
 
StartTime = Time
<Call some procedure>
EndTime = Time
TimeDelta = EndTime - StartTime
 

The problem I have is that TimeDelta is always an integer number of seconds, after I multiply it by 86400 (the number of seconds in a day). Is there a way of getting the fractional portion of elapsed time, using a different mechanism? Or is this as accurate as VBA/MS Access gets?


Thanks.

[Scott Price]

One point that you likely aren't aware of is that VBA requires a specific type declaration for each variable, otherwise it defaults to Variant data type.

Thus your first line is resulting in one Variant and one Date variable.

It's possible that the resulting unintended type coercion is causing your integer conversion. Try changing this and then trying again.

Regards,
Scott

[Zwoker]

Hi Scott,

Thanks for the reply.

Just to be sure I understand what you wrote, are you saying that the following two blocks of code are not identical from a VBA code perspective?

Dim StartTime, EndTime As Date

Dim StartTime As Date
Dim EndTime As Date

Presuming that IS what you meant, I made all the variables involved in the time calculations have explicit AS "type" declarations, but the result is still the same. I get integer seconds calculated (allowing for rounding - some come out as 1.999999, as an example).

I googled a bit on this before posting, and what little I learned seemed to imply that the integer nature of the internal Time function was normal.

Any other ideas? Can I create a timer type variable on the fly and use that? If I was writing this in Delphi I'd try something like that. :)

[Scott Price]

You are right! In VBA (as opposed to other programming languages) the two variable declarations are not equal!

The Date data type provides an Integer value of the date passed to it. With Access there is a notorious problem using floating-point calculations, especially when there are any type conversions, of possible rounding errors.

Try passing your calculations through a specific forced conversion to double: i.e. CDbl([numbervalue]) before doing your calculations.

Regards,
Scott

[Scott Price]

As for the second part of your question, I'm not quite clear on what you are attempting to do.

You can take advantage of a timer in vba, but I'm not sure how that applies to what you are doing?

Regards,
Scott

[ADezii]

Hi All,

I'm using MS Access 2003. I have been trying to find out why portions of my VBA code have been running slower than expected, using code like the following:

Dim StartTime, EndTime As Date
Dim TimeDelta As Single
 
StartTime = Time
<Call some procedure>
EndTime = Time
TimeDelta = EndTime - StartTime
 

The problem I have is that TimeDelta is always an integer number of seconds, after I multiply it by 86400 (the number of seconds in a day). Is there a way of getting the fractional portion of elapsed time, using a different mechanism? Or is this as accurate as VBA/MS Access gets?


Thanks.

If you don't mind me saying, you are using the completely wrong algorithm for accurately timing processes. Take a few minutes, and read this prior Tip:
timeGetTime() API Function

[Zwoker]

Thanks ADezii.

The example in that thread is exactly what I was looking for. I can now accurately measure where all the time in going in my VBA code.

[NeoPa]

The point that ADezii makes in the article about running the procedure through a loop is the one I was going to make. His article is the Bees Knees though :) Everything in one place.

[missinglinq]

His article is the Bees Knees though :)

That's what I love about you Brits, you're so naturally Retro! I bet your local still has Glenn Miller on the juke box!

;0)>

[NeoPa]

Sorry, he must be from before my time Linq ;)

[ADezii]

Sorry, he must be from before my time Linq ;)

We are talking about Glen who, guys?

[NeoPa]

Nice try ADezii. No-one's believing you, you know :D