Need to run code every minute, thinking I might have to use SQL Trigger?

[AndyB2]

Access 2010
SQL Sever 2005 express

Here is my code, works fine as is. But I need to run it once a minute, if not more often. From what I have found out by surfing the web I have one of three choices:

1) Use the timer in the forms property.
2) Use the PC scheduler and call access then autoexec.
3) Rewrite the code below into SQL and apply it to a trigger.

I do not like any of the above options because:

1) Forms timer is not that reliable from what I read, please tell me if I'm wrong.
2) PC scheduler opening and closing Access every minute doesn’t sound that great.
3) I have never written a trigger. And the SQL code requires comparing the previous record to the current record, sounds like my dog chasing its tail. I have seen code like this before, and it will be a stretch for me. Also lines 45-47, and 50-52 write data back out in a specific way to machines/PLC's on the floor. I am not sure how, or if, SQL can do this?

Private Sub ChkDownTimeCode_Click()
 
Dim RecordCount As Integer
Dim CurrRecord As Integer
Dim TagNamestr As String
Dim TagValueint As Integer
Dim StartProd As Integer
Dim EndProd As Integer
Dim SQLStg As String
Dim dbs As DAO.Database
Dim DownTimeNotZero As DAO.Recordset
 
 
Set dbs = DBEngine.Workspaces(0).Databases(0)
 
SQLStg = "SELECT dbo_Link_Data.Asset_Number, dbo_Link_Data.DT_Cat_VALUE, " & _
         "dbo_Link_Data.DT_Code_VALUE, dbo_Link_Data.Downtime_VALUE, " & _
         "dbo_Link_Data.Produced_VALUE, dbo_Link_Data.QUALITY, dbo_Link_Data.TIMESTAMP1, " & _
         "dbo_MachineTags.DT_Code, dbo_MachineTags.DT_Cat " & _
         "FROM dbo_MachineTags INNER JOIN dbo_Link_Data ON dbo_MachineTags.AssetNumber = " & _
         "dbo_Link_Data.Asset_Number " & _
         "WHERE (((dbo_Link_Data.DT_Code_VALUE)<>0) AND ((Hour([TIMESTAMP1]))=Hour(Time())) " & _
         "AND ((Minute([TIMESTAMP1])-Minute(Time()))<2)) " & _
         "OR (((dbo_Link_Data.DT_Cat_VALUE)<>0) AND ((Hour([TIMESTAMP1]))=Hour(Time())) " & _
         "AND ((Minute([TIMESTAMP1])-Minute(Time()))<2));"
 
Set DownTimeNotZero = dbs.OpenRecordset(SQLStg, dbOpenSnapshot)
 
RecordCount = DownTimeNotZero.RecordCount
CurrRecord = 1
 
If RecordCount > 0 Then
    While RecordCount <> CurrRecord
        CurrMachine = DownTimeNotZero("Asset_Number")
        StartProd = DownTimeNotZero("Produced_VALUE")
        'While is moving to last record for machine to get end production
        While RecordCount <> CurrRecord And CurrMachine = DownTimeNotZero("Asset_Number")
            CurrRecord = CurrRecord + 1
            DownTimeNotZero.MoveNext
        Wend
        EndProd = DownTimeNotZero("Produced_VALUE")
        If (EndProd - StartProd) >= 3 Then  'Reset downtime codes to zero (0)
            TagNamestr = DownTimeNotZero("DT_Cat")  'Machine Tag Name
            TagValueint = 0
            channel = Application.DDEInitiate("kepdde1", "_ddedata")
            Application.DDEPoke channel, TagNamestr, TagValueint
            Application.DDETerminate channel
            TagNamestr = DownTimeNotZero("DT_Code") 'Machine Tag Name
            TagValueint = 0
            channel = Application.DDEInitiate("kepdde1", "_ddedata")
            Application.DDEPoke channel, TagNamestr, TagValueint
            Application.DDETerminate channel
        End If
        DownTimeNotZero.MoveNext
    Wend
End If
 
End Sub
 

Basically I would like to know if the box I think I am in is correct and I will start to learn how to write a trigger/SQL. Or, if there is a different escape hatch I have not uncovered yet?

Thanks,

Andy2

[Rabbit]

What is the code doing and why does it need to run every minute?

[AndyB2]

How the data is created; data logger is polling any change to “Tags” from PLCS on the floor. So if we produce a wigged it is logged. Downtime reason codes entered by the operator are also logged. Just the operator sometimes forgets to change their codes back to zero(0) when they startup production.

This code grabs all data logged in the last 2 minutes with a downtime code not zero(0) then check to see if the production over that time is greater than 3. If that is true the code will reset the downtime codes to zero (0) for the operator.
I am tempted to just do something like:

While 1 = 1 do
  Set var1 = minute(time())
  If var1 <> minute(time()))
    Put code here
  End if
Wend
 

[AndyB2]

Realizing the above code needs to run in its own instance of access. Not the best solution, but could work if there isn't a better one.

[Rabbit]

And what's the down side if the operator forgets to reset the down time code and your process doesn't run?

[AndyB2]

We track downtime by machine/operator. If we are producing product with a downtime code not zero (0) it makes it hard to figure productivity.

Example 1: Operator 1 is scheduled to produce 100 widgets an hour. His actual downtime for the first hour is 15 min’s (8:30 > 8:45) and he produced 80 widgets, 80/75 (75% of a std. hour) = 106.6% eff.

Example 2: Same operator now forgets to reset his downtime and the data shows the downtime is 30 min’s (8:30 > 9:00) now the math is 80/50 (50% of a std. hour) = 160% eff.

[Rabbit]

You have a few options.

1) Schedule a SSIS package to run every minute to run an update query. This will only work if SQL Express comes with SSIS.

2) Schedule a VBS script on Windows to run an update query. You will have to use this option if you don't have SSIS.

3) Calculate the end of the down time from the timestamp field in the production table in a query. This has the benefit of running only when you need to see efficiency reports.

Doesn't the table that stores production data have a date/time stamp?

[AndyB2]

Thanks

1) A quick search of SSIS looks like it is available on 2008 and not 2005. At lease when I search MS/SQL 2005 SSIS, it sends me to 2008.

2) I need to see if I can find an example of a VBS script to run an Access update query.

3) As far as doing a calculation with the timestamp, that could happen. But I am building the backend to a website where multiple people will be running the efficiency reports concurrently multiple times a day. I would rather do the number crunching as away from the front end as possible.

[NeoPa]

I've never had problems dealing with a form's timer process, but then I always ensure there is always time to process what is required before it's next triggered. Basically, timer code should avoid dealing with the operator (as, if they fail to respond, the process can stall) and if there is any possible risk of a timer triggering before the previous one has concluded then always turn the timer off while processing and turn it back on only when completed.

That said, if it's a SQL update you're looking at (on a BE SQL database) then I expect SQL Server can handle that much more efficiently than Access can. I've never done a T-SQL trigger either, but I've seen nothing to cause me to think it's overly complicated. You may be surprised when you look into it.