VBA Question

  • Thread starter Thread starter Matt Weyland
  • Start date Start date
M

Matt Weyland

I have a DB. Its sole and only purpuse is to monitor
network connectivity and write the information to a
table. Not the best way to impliment but I work for the
Gov and am constrained by limited number of development
tools.

So far I have:

created a method to use the IMCP.dll methods to recreate
the funtion of PING

created a class to store informtion for each instance it
is created, i.e. IP, AppName, upTime ,and downTime amongst
others

For each instance of the class I want to do the following:
Mon - Fri 8-6 ping its respective IP, capture the result
and log in a DB. If the IP is up wait 5 minutes run
again, if the IP is down, wait 1 minute run again util the
IP is back up again.

My problem is in the waiting part. I tried using sapisleep
from kernel32 and pass in the ms to wait but this makes
the whole system sleep. My next thought was to use the
gettickcount also frm kernel32 and use that like a timer,
but that too would hog resources as it passes through the
loop for each iteration of the for next loop.

Does anyone have any ideas or suggestions.

Thanks

Matt Weyland
Database Analyst

please post responses here or reply to:
mweyland at mnqio dot sdps dot org


ps. MS nice to allow for annonymous emails. I was
getting 1500 pieces of SPAM a week because of this
newsgroup
 
I'm not sure if this is what you meant by "using sapisleep
from kernel32" but this is the call I use when I need to
pause the code:

'*** Place declaration in a module
Public Declare Sub SleepAPI Lib "Kernel32" Alias "Sleep"
(ByVal timeInMs As Long)

'*** Example call from a Sub to pause for 60 seconds
'*** Note: time is in MS, 1 second = 1000 MS
SleepAPI 60000
 
My problem is in the waiting part. I tried using sapisleep
from kernel32 and pass in the ms to wait but this makes
the whole system sleep. My next thought was to use the
gettickcount also frm kernel32 and use that like a timer,
but that too would hog resources as it passes through the
loop for each iteration of the for next loop.
Click to expand...

So, you are looking for a way to wait without causing the thread to lock
up? If so, look at the KillTimer, SetTimer API functions. These functions
require a callback to be set up (AddressOf) which is easier if you are
using Access 2000+. If you want sample code, look at the source provided in
this utility.

[ http://www.mvps.org/access/modules/mdl0042.htm ]

-- Dev
 
-----Original Message-----
My problem is in the waiting part. I tried using sapisleep
from kernel32 and pass in the ms to wait but this makes
the whole system sleep. My next thought was to use the
gettickcount also frm kernel32 and use that like a timer,
but that too would hog resources as it passes through the
loop for each iteration of the for next loop.
Click to expand...

So, you are looking for a way to wait without causing the thread to lock
up? If so, look at the KillTimer, SetTimer API functions. These functions
require a callback to be set up (AddressOf) which is easier if you are
using Access 2000+. If you want sample code, look at the source provided in
this utility.

[ http://www.mvps.org/access/modules/mdl0042.htm ]

-- Dev

.
Click to expand...

Dev,

Yes I am looking for a way to wait without having the
thread lockup.

I saw that information on the MVP website, unfortunately I
don't have VB at work here and cant read the BAS or CLS
files included.

do you have any suggestions on where to look for example
code or can you assist.

Thanks

mweyland at mnqio dot sdps dot org
 
I saw that information on the MVP website, unfortunately I
don't have VB at work here and cant read the BAS or CLS
files included.
Click to expand...

You don't need to have VB installed.. You can use Notepad to view those
files. In any case, this should get you started... (You could've also
inserted those files in a VBA project)...

' *** Code Start ***
Private Declare Function apiKillTimer Lib "user32" _
Alias "KillTimer" _
(ByVal hwnd As Long, _
ByVal nIDEvent As Long) _
As Long

Private Declare Function apiSetTimer Lib "user32" _
Alias "SetTimer" _
(ByVal hwnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) _
As Long

Private mlngTimerID As Long

Sub sHookTimerCallback()
Const conDELAY = 1000& * 25&

'set up a callback so that our function gets called
'every conDELAY seconds with conRESOLUTION accuracy
mlngTimerID = apiSetTimer(0&, 0&, conDELAY, _
AddressOf sTimeProcCallback)

End Sub

Sub sUnhookTimerCallback()
'Unhook the callback so we can close the form and terminate app
If mlngTimerID > 0 Then _
Call apiKillTimer(0&, mlngTimerID)
End Sub

Sub sTimeProcCallback(ByVal hwnd As Long, _
ByVal uMsg As Long, _
ByVal idEvent As Long, _
ByVal dwTime As Long)

' This routine will get called
' once SetTimer is called until
' it's removed with KillTimer call
'
End Sub
' *** Code End ****

-- Dev
 
Thanks for the information. One problem though. I keep
getting an incorrect use of AddressOf

I tweaked sHookTimerCallback to pass in how long I want it
to wait dependent on the result.

Am I understanding this correctly?
- AddressOf returns the location in memory of an object?

If this is the case, where is sTimProcCallback created?
If it hasn't been created does it still live in memory?

- I am assuming I add the sHookTimerCallback() method to
my sub that employs the logic for which it is to run.
Either x or y minutes depending on the outcome.

Thanks
 
Thanks for the information. One problem though. I keep
getting an incorrect use of AddressOf
Click to expand...

What error are you getting? Which version of Access are you using? Also,
did you copy this code to a standard module?
Am I understanding this correctly?
- AddressOf returns the location in memory of an object?
Click to expand...

Not quite... that's done by strptr, varptr etc. AddressOf returns the
function pointer.
If this is the case, where is sTimProcCallback created?
If it hasn't been created does it still live in memory?
Click to expand...

This detail is handled by VBA behind the scenes.. You don't have to worry
about it. VBA holds the code in memory and the AddressOf operator allows
you to pass the function's memory location to an API that expects a
function pointer.

- I am assuming I add the sHookTimerCallback() method to
my sub that employs the logic for which it is to run.
Either x or y minutes depending on the outcome.
Click to expand...

Correct.. Don't forget to call unhook (KillTimer) when the form/app
terminates.

-- Dev
 
You might consider using the timer event on a form to call the code.

You can set a form with a timer as to when to trigger the code, and no
lockup occurs.
 
-----Original Message-----


What error are you getting? Which version of Access are you using? Also,
did you copy this code to a standard module?
Click to expand...

I intially copied this into a class module, but when I
moved it over it to a standard module the error ceased.
The error I was getting was "Incorrect use of AddressOf"
and it wouldn't compile.
Not quite... that's done by strptr, varptr etc. AddressOf returns the
function pointer.


This detail is handled by VBA behind the scenes.. You don't have to worry
about it. VBA holds the code in memory and the AddressOf operator allows
you to pass the function's memory location to an API that expects a
function pointer.



Correct.. Don't forget to call unhook (KillTimer) when the form/app
terminates.

-- Dev

.
Click to expand...


I did all of this and I am getting pretty much the same
result as with ssleep. I am beginning to wonder if this
may be beyond the abilility of VBA. If you still have a
class and it is doing a for next loop, the active class
seems to lock the form.

I would ideally like to have each instance of the class
keep time for itself dependent on the information it
recieves back from PING. I seem to only be able to do
this with one class at a time.

I did have another question. What does the & proceding
the definition of CONSTANTS mean?
 
Dev,

I wrote a class called stopwatch. it just starts and
stops and returns the elapsed time.

I added this to a test sub in an empty module
sub testx()
sw.StartClock
Debug.Print "Start wait"
sHookTimerCallback
Debug.Print "Stop wait"
sw.StopClock
Debug.Print sw.getElapsedTime
sUnhookTimerCallback
sub end

regardless of what I set the Constant in sHookTimerCallback
(Const conDELAY = 1000& * 25&)

it always returns the same value 0.

There is no wait that occurs

I put the code you provided in a module called modTimer
verbatim.

Am i implimenting wrong?

Your help is appreciated.
 
I am beginning to wonder if this
may be beyond the abilility of VBA. If you still have a
class and it is doing a for next loop, the active class
seems to lock the form.
Click to expand...

That's because code is executing. You'll have to yield cpu to other
processes by using DoEvents in this loop, but it isn't the most efficient
way.
I would ideally like to have each instance of the class
keep time for itself dependent on the information it
recieves back from PING. I seem to only be able to do
this with one class at a time.
Click to expand...

I'm afraid I'm confused on this item. In the code I'd posted, the timer
callback it sets up is global, not instance based. If you want it to be
instance based, then you have to modify more code. Also, based on your
other post, once you call sHookTimerCallback, after whatever delay you
specified, Windows will automatically call sTimeProcCallback, enabling
you to execute code after every n milliseconds/seconds/minutes. This
does not eat up any CPU cycles. This process continues until you call
sUnhookTimerCallback. You can try specifying a wait for 10 seconds and
put in a debug message in sTimeProcCallback. Now call sHookTimerCallback
from debug window to see the TimerProc work.
I did have another question. What does the & proceding
the definition of CONSTANTS mean?
Click to expand...

That the numeric value for that constant should be treated as a Long.

-- Dev
 
Back
Top