A kind of proces or deamon

[nkamp]

A kind of proces or deamon or thread

Hello,

I'm new to Access.
I have in a param table where I have put some times in it. I want every day at the times which are filled in de param table, execute a query.

Let say:
record 1 in param table: 08:00:00
record 2 in param table: 10:00:00
record 3 in param table: 12:00:00

I suppose I have to made a macro or some VBA code in Access or whatever, which is running in the application background and doing the query on the param table. The result is then the three records.
Then testing if the time is already reached. If it is reached then execute the query to export data to a XML file, write the status in the tabel that the query for that day/time is executed.
Then it can sleep for a while and wake's up a half an hour later.

I hope that I'm clear what I mean. My issue is how do you write a program which is running in the background and let say sleeps a certain time and wake up. execute some code and is sleeping again?
What is the right way to do that?
- can it be done in Access?
- must I create a process?
- must I do it by a thread? but how
- must I create a service? (I have never done this)

Can somebody help me with some advise or better some code?

Thanks in advance.

Nico

 

[selevanm]

Sleep

Not sure if this is the correct way to do it or not but this has worked for me in the past.

Option Compare Database
Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
________________________________________________________________
Function Blah()
.......
........


The line in red is decalred at the top of my module, it uses the system library sleep function....

Then you call the sub and pass it a value in milliseconds like the following...

If objNotesDocument Is Nothing Then
Sleep (300000)
Set objNotesDocumentCollection = objNotesDatabase.ALLDOCUMENTS
Set objNotesDocument = objNotesDocumentCollection.GETFIRSTDOCUMENT
End If

Like I said not sure if this is the right way to do it but it works, if someone else has a better way, I would love to hear it as well.

Matt

 

[nkamp]

Hello Matt,

I think this is the otherway around. The function is going to sleep for a while and after the sleep period its going further or do I mis understand you

What I need is a function/process which starts automatically after a certain time?

Nico

 

[selevanm]

Nico

I was assuming you were already in the function.

Here are my thoughts,

Main()

--determine the amt of times you need to execute the qry, i.e. how
many time records are in the param table = TotalNumberOfRecords

do while i < TotalNumberOfRecords
blnRun = CheckParamTable()

if blnRun = True 'the qry ran
then Sleep(30mins)
i = i + 1 'increment here because the qry ran
else
Sleep(time) ' sleep for x amt time and check again
end if
Loop


CheckParamTable()
--check to see if the time now matches time to execute the query from the params table
if yes execute qry, return true
else return false

This is just a rough idea of some psuedocode that may help. Once you have went through all of the time records in the param table the Main function can end and the program can be done. Obviously more code would have to be added to figure out matching the times in the param table, executing the qry, etc. To kick off the main method you can do that via a .bat file through the OS scheduler I believe but am not 100% on that. Am I getting closer to what you were looking for?

Matt

 

[nkamp]

Hello matt,

Oke, that's working so far.
But If add this code, then my whole access application (=main thread) is sleeping or dead.
So what I really need is a kind of a thread in VBA/Access. So that I have the main thread and a subthread which is started in the main thread.

What I not understand is, am I the first one with this problem?

Option Compare Database
                                                                
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private StartThread As Boolean

Public Sub cmdStartThread_Click()
  Dim i As Integer
  
  
  Do While (StartThread)
    If (i < 4) Then
      i = i + 1
      Forms!frmOutput.txtStream1 = i
      'MsgBox "De waarde van i is " & i
    Sleep (1000)
    Else
      i = i - 1
      Forms!frmOutput.txtStream1 = i
      'MsgBox "Else tak wordt doorlopen"
    End If
  Loop
  
  
End Sub

Private Sub cmdStopThread_Click()

If (StartThread = False) Then
  StartThread = True
Else
  StartThread = False
End If
  Forms!frmOutput.txtStream2.Value = StartThread
End Sub

Nico.

 

[selevanm]

Nico,

I am not familiar with the multi threading capability within VBA or even if it is supported. What I have done in the past is use the Shell command to kick off other processes via .bat files. For example i create the following .bat file
"O:\Apps\office 2000\Pfiles\MSOffice\Office\MSACCESS.exe" "<pathname\DailyMartCheck.mdb>" /x CheckMarts which executes a macro within the DailyMartCheck.mdb call CheckMarts. The macro kicks off code within my application. Could you shell out the sub thread that you need which will do all of your checking, querying, and sleeping while keeping your main thread application running?