By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,183 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

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

P: 49
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub ChkDownTimeCode_Click()
  3. Dim RecordCount As Integer
  4. Dim CurrRecord As Integer
  5. Dim TagNamestr As String
  6. Dim TagValueint As Integer
  7. Dim StartProd As Integer
  8. Dim EndProd As Integer
  9. Dim SQLStg As String
  10. Dim dbs As DAO.Database
  11. Dim DownTimeNotZero As DAO.Recordset
  14. Set dbs = DBEngine.Workspaces(0).Databases(0)
  16. SQLStg = "SELECT dbo_Link_Data.Asset_Number, dbo_Link_Data.DT_Cat_VALUE, " & _
  17.          "dbo_Link_Data.DT_Code_VALUE, dbo_Link_Data.Downtime_VALUE, " & _
  18.          "dbo_Link_Data.Produced_VALUE, dbo_Link_Data.QUALITY, dbo_Link_Data.TIMESTAMP1, " & _
  19.          "dbo_MachineTags.DT_Code, dbo_MachineTags.DT_Cat " & _
  20.          "FROM dbo_MachineTags INNER JOIN dbo_Link_Data ON dbo_MachineTags.AssetNumber = " & _
  21.          "dbo_Link_Data.Asset_Number " & _
  22.          "WHERE (((dbo_Link_Data.DT_Code_VALUE)<>0) AND ((Hour([TIMESTAMP1]))=Hour(Time())) " & _
  23.          "AND ((Minute([TIMESTAMP1])-Minute(Time()))<2)) " & _
  24.          "OR (((dbo_Link_Data.DT_Cat_VALUE)<>0) AND ((Hour([TIMESTAMP1]))=Hour(Time())) " & _
  25.          "AND ((Minute([TIMESTAMP1])-Minute(Time()))<2));"
  27. Set DownTimeNotZero = dbs.OpenRecordset(SQLStg, dbOpenSnapshot)
  29. RecordCount = DownTimeNotZero.RecordCount
  30. CurrRecord = 1
  32. If RecordCount > 0 Then
  33.     While RecordCount <> CurrRecord
  34.         CurrMachine = DownTimeNotZero("Asset_Number")
  35.         StartProd = DownTimeNotZero("Produced_VALUE")
  36.         'While is moving to last record for machine to get end production
  37.         While RecordCount <> CurrRecord And CurrMachine = DownTimeNotZero("Asset_Number")
  38.             CurrRecord = CurrRecord + 1
  39.             DownTimeNotZero.MoveNext
  40.         Wend
  41.         EndProd = DownTimeNotZero("Produced_VALUE")
  42.         If (EndProd - StartProd) >= 3 Then  'Reset downtime codes to zero (0)
  43.             TagNamestr = DownTimeNotZero("DT_Cat")  'Machine Tag Name
  44.             TagValueint = 0
  45.             channel = Application.DDEInitiate("kepdde1", "_ddedata")
  46.             Application.DDEPoke channel, TagNamestr, TagValueint
  47.             Application.DDETerminate channel
  48.             TagNamestr = DownTimeNotZero("DT_Code") 'Machine Tag Name
  49.             TagValueint = 0
  50.             channel = Application.DDEInitiate("kepdde1", "_ddedata")
  51.             Application.DDEPoke channel, TagNamestr, TagValueint
  52.             Application.DDETerminate channel
  53.         End If
  54.         DownTimeNotZero.MoveNext
  55.     Wend
  56. End If
  58. 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?


Mar 8 '12 #1
Share this Question
Share on Google+
8 Replies

Expert Mod 10K+
P: 12,342
What is the code doing and why does it need to run every minute?
Mar 8 '12 #2

P: 49
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:

Expand|Select|Wrap|Line Numbers
  1. While 1 = 1 do
  2.   Set var1 = minute(time())
  3.   If var1 <> minute(time()))
  4.     Put code here
  5.   End if
  6. Wend
Mar 8 '12 #3

P: 49
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.
Mar 8 '12 #4

Expert Mod 10K+
P: 12,342
And what's the down side if the operator forgets to reset the down time code and your process doesn't run?
Mar 8 '12 #5

P: 49
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.
Mar 8 '12 #6

Expert Mod 10K+
P: 12,342
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?
Mar 8 '12 #7

P: 49

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.
Mar 8 '12 #8

Expert Mod 15k+
P: 31,308
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.
Mar 8 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.