?
Solved

MS Access Timer Event for long process?

Posted on 2012-08-15
20
Medium Priority
?
1,163 Views
Last Modified: 2012-08-15
I am developing an app that needs to regulary read emails and load the data into an MS Access database. It is preferred that the emails get loaded as quickly as possible so ideally I would like to run the loop as often as possible. Unfortunately with just 9 emails in the system the process takes as long as about 80 seconds. This gives rise to the following questions:
1. If I set the timer to run every 90 seconds and the process takes longer than 90 seconds to run, does this mess up the timer?
2. Does a MS Access form timer run in the same thread as the rest of the processing, in which case the process will force the user to stop and wait for an 80 second process to occur every 90 seconds, clearly unacceptable.

Any suggestions/options, please????
0
Comment
Question by:Rob4077
  • 10
  • 4
  • 3
  • +3
20 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 668 total points
ID: 38295180
The timer event won't get messed up.  It will fire as scheduled.

However it may lead to your process starting multiple times before finishing.

To avoid this you can turn the timer off at the beginning of the code and restart it at the end after making any needed checks that data processes etc are done.
0
 

Author Comment

by:Rob4077
ID: 38295209
That's what I was hoping. Thanks very much.

Just confirming, though, does it run in a separate thread? When the email code runs in a standard module (while I test and configure it to do what I need) it prevents MS Access from doing anything else until it has finished running. If I put that in a form timer event and it runs in a separate thread then there should be no problem but if it locks up access then there is a problem.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 336 total points
ID: 38295262
There is no published information on this topic (well I've never found any) but in principle Access is single threaded.  So unless you can test and show otherwise, then your UI will halt until each timer event completes.

If this is important then you should look for a way of launching a separate process outside your current Access session to do the import (a different Access session running a separate application might work, for example).
However if you are using the same table that is being updated by the import in your main UI then you still have contention.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Rob4077
ID: 38295405
Thanks for the extra comments Peter. The reason I wondered is that in one of my earlier questions Jim Dettman said <<although it appears not, VBA is multi-threaded.>>. I wasn't sure if he meant it works as if it is multi-threaded but really isn't, or if it meant that timer events really do work in a separate thread. As suggested I will drop the code into a form timer and see how it works.
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27508631.html#a37344387
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 668 total points
ID: 38295410
<< unless you can test and show otherwise  >>

Like Pete, I haven't seen any documentation on this.  I would definitely suggest just trying it -- in a controlled environment.

Access VBA has a DoEvents command which to my understanding says "Allow all code in this and other events to finish processing before executing the next command".  The pressence of the DoEvents command has always led me to believe (possibly incorrectly) that Access  will intersperse commands from different events (ie: having commands from different events executing sequentially, but not necessarily grouped by event).

Anyhow IMO it is worth testing (even just out of academic interest) to see whether any delay (or screen flickering which is commonly associated with timer event code) is acceptable.
0
 

Author Comment

by:Rob4077
ID: 38295434
Thanks for your comments.

I tested it and sure enough, it locks up the whole system while it's doing it's bit. When there's as little as two tiny messages in there it takes about 11 seconds to run the download and my timer is currently set to 15 seconds so it doesn't give much time between events to do anything. I could throw a DoEvents in the timer event in the middle of the download process but the bulk of the process is taken up in just a single command step so, if my understanding of the DoEvents command is correct, it's not going to help much. i.e. if I issue the command to do the download followed by a doEvents, the DoEvents wont happen until the download is completed, even though the download is being handled by a separate Active-X function - does that sound right?

It's beginning to sound like the only real solution is to run a second program in a second instance of MS Access to handle the process. Do you have any suggestions. The code reads an email address every xx seconds and dumps any emails it finds into an MS Access table. I can get the main app to launch a second MS Access App, minimised, or I suppose I could write a VBScript to loop around and do it. What would you suggest?
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 996 total points
ID: 38295437
<<Access VBA has a DoEvents command which to my understanding says "Allow all code in this and other events to finish processing before executing the next command".  >>

 No, your mixing that up with dbEngine.Idle I believe, which allows JET background processing to catch up before execution continues.

 DoEvents yields processing time to the OS and pauses Access.  This appears to let Access catch up in some respects in that the OS has a chance to catch up on sending messages to the Access window.

 DoEvents is typically used for the wrong thing and is overused.  If you have to resort to DoEvents to make your Access code work right and your not using an external program (ie. Shell()) or a COM object, then your doing something wrong.  Normally there is no reason to use DoEvents outside of that.

<<st confirming, though, does it run in a separate thread? When the email code runs in a standard module (while I test and configure it to do what I need) it prevents MS Access from doing anything else until it has finished running. If I put that in a form timer event and it runs in a separate thread then there should be no problem but if it locks up access then there is a problem. >>

 You should as Miriam said disable the timer or set a flag to prevent your timer event from firing off your process more then once at the same time.

Jim.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38295442
<< if my understanding of the DoEvents command is correct, it's not going to help much. >>

That is correct - it just makes other 'outstanding' event code finish before executing the next line.

Edit -

I missed Jim's comment above this.
0
 

Author Comment

by:Rob4077
ID: 38295485
Thanks for all of the comments.  

Miriam's suggestion makes sense but it still leaves the user waiting an extended period of time (I have seen the process take almost 80 seconds to download emails) before it will allow the user to continue their usual tasks. Since timer events don't run in a second thread I need another solution, either a second MS Access app or a smaller VBScript loop. Which option is the more robust and professional?

The Active-X I am using will run in VB Script and the following VB Script code will READ variables from a MS Access table. I just need to figure out how to add new records but I am guessing that's feasible. i.e use my MS Access app to start a VBScript that:
1) reads a value from a table to find out the location of the BE
2) loops continuously reading emails using my Active-X and dropping them into the MS Access table.


  Dim appAccess
   Dim strDBNameAndPath
   Dim dbe
   Dim dbs
   Dim strSQL

   Set appAccess = _
      WScript.CreateObject("Access.Application")
   Set fso = _
      WScript.CreateObject("Scripting.FileSystemObject")
   strDBNameAndPath= "G:\Documents\Northwind.accdb"
   
On Error Resume Next

   Set fil = fso.GetFile(strDBNameAndPath)
   If fil Is Nothing Then
      strPrompt = "Can't find " & strDBNameAndPath& _
         "; canceling"
      MsgBox strPrompt, vbCritical + vbOKOnly
      Quit
   Else
      Set dbe = appAccess.DBEngine
      Set dbs = dbe.OpenDatabase(strDBNameAndPath)
   
      Set rst = dbs.OpenRecordset("tblCategories")
      Do Until rst.EOF
         MsgBox rst.[CategoryName]
         rst.MoveNext
      Loop
      rst.Close
   
   End If

   Set rst = Nothing
   Set appAccess = Nothing
0
 
LVL 85
ID: 38295487
Is the db used for anything other than email processing? If so, then you'd be better off using the "outside process" setup to do this. Build another database (or better yet, a .NET applet) that will manage this for you, then call out to the external process as neded.
0
 

Author Comment

by:Rob4077
ID: 38295518
Wow. I spent some time a while ago trying to get a handle on .NET but did not get very far.

What happens in this app is that:
1) until recently messages would arrive by SMS and were picked up by a separate VB program that would read the messages from the aircard and drop them into an MS Access db.
2) from there a MS Access program would read the messages interpret (decode/parse) them and take appropriate action using a form with a timer event that fired every 15 seconds. That event obviously only ever took microseconds to complete the task so it didn't interfere with the user at all.
3) now messages are coming in via email as well so I need to get those messages into the same table. I was originally hoping to read emails and process them in the same loop but the delay is too long, especially now that I know timer events don't run in a separate thread.
0
 

Author Comment

by:Rob4077
ID: 38295521
I may have to use a second MS Access app linked to the same BE and just launch it minimised
0
 

Author Comment

by:Rob4077
ID: 38295693
I have been thinking about using a second MS Access app to handle the importing of emails but I am concerned about it being closed by accident. If a user closes it there is no way the master app will know. The other thing I can do is launch a second minimised app every minute or so to check the emails and then close itself automatically but that's going to cause constant activity on the task bar. Does anyone know if:
1) VB Script code to read/write to a database will work with MS Access 2007 runtime?
2) Would VB Script code indicated above prevent another app from being simultaneously linked into the same BE tables?
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 996 total points
ID: 38295794
<<I have been thinking about using a second MS Access app to handle the importing of emails but I am concerned about it being closed by accident. If a user closes it there is no way the master app will know. >>

  That's easy to handle in a number of ways:

1. Have code to check if the app is up using WinAPI's (I have that here and working - have used it for years).

2. Have the background app write a text file every cycle (or use some other flag like a record in a table, a field, etc) and check it.  If it hasn't updated in xxx seconds, launch another instance of the app.  This would be problematic though if for some reason the first instance was stalled (it's not really telling you if the app exists already or not).

<<The other thing I can do is launch a second minimised app every minute or so to check the emails and then close itself automatically but that's going to cause constant activity on the task bar.>>

 and of course that's the other way to handle it.

<<1) VB Script code to read/write to a database will work with MS Access 2007 runtime?>>

 No reason it should not, but I've never tried it.  The runtime version simply means you can't open objects in design view.

<<2) Would VB Script code indicated above prevent another app from being simultaneously linked into the same BE tables? >>

  No as long as the open mode was shared.

Jim.
0
 

Author Comment

by:Rob4077
ID: 38295914
From what you've said, I think the easiest way to do it is to use the VBScript as I already have the code to do that.

There is only one drawback with that. I was going to buy SageKey so I could distribute the app with MS Access 2007 Runtime included and not have to worry if they already have another version of MS Access on their computer (to avoid re-registration). However I don't know what the impact of running my VBScript on a PC with both the runtime and another version of MS Access is going to be. Do you know the impact, or will I have to worry about it when it happens.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38295951
I'm wondering what process you use to grab your emails. 80 seconds for just 9 emails seems rather long.
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 996 total points
ID: 38295981
Not sure 100% on the impact, but I don't believe it mixing with different versions of Access will be your problem.

It will be security and if the user has a locked down system.  Sometimes, such systems don't allow vbscripts to run because it's a way for a virus to come in through an e-mail.

Jim.
0
 

Author Comment

by:Rob4077
ID: 38295990
It seems every option I try fails. I just tried installing MS Access Free Runtime on a virtual machine that is running MS Access 2002 and unfortunately my VBScript wont run.

And since I don't like the TaskBar constantly "flickering", looks like I may have to opt for the WinAPI's option.

I am going to have to close this question before it becomes even harder to award points. The orginal question has been answered so I will have to raise a new question for the WinAPI's

Thanks to everyone for your comments. I will try to award points as fairly as I can, keeping in mind my original questions
0
 
LVL 58
ID: 38295998
Yeah, sometimes it's really hard to get the job done.  The more time marches on, the more difficult the tasks become.

 And here were are more or less trying to do the same types of things we did ten-fifteen years ago, only now we do it "better".

 And no points here please.  I was just tossing in a few odd comments.

Jim.
0
 

Author Comment

by:Rob4077
ID: 38296019
Sorry irogSinta, I missed your comment earlier. I am using ChilKat (http://www.chilkatsoft.com/email-activex.asp). I tried Codestone as well but that didn't seem any better.

Jim, it's always hard trying to be fair with points so I try to share comments with any comment that makes a worthwhile contribution to my understanding. I will now raise a question on " check if the app is up using WinAPI's "
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question