Link to home
Start Free TrialLog in
Avatar of Dale James
Dale James

asked on

How to create delay of VBA code execution

Hello Team

I have been running a reports automation from MS Access. Nothing to complicated, just using access to open up particular files in Excel, delete specific detail and then transferring query detail back to Excel.

This process has been working fine for quite sometime but recently there has been a system update within the Citrix operating system and since the update my automation has been crashing when it gets to the DoCmd. TransferSpreadsheet method.

What I have discovered is, if I step through the code one line at at time or even apply a break before the  DoCmd. TransferSpreadsheet methods are reached, the code will continue to be processed without any issues.

From this observation, I am beginning to think that it may be a delay occurring within the Citrix environment as the only difference as far as I am aware of is the speed at which the code is processed in comparison to the step code execution and the event trigger code execution.

If the issue is due to the speed at which the code is being processed or attempting to be processed from the trigger event, can anyone suggest what would be the best way to create a delay in the processing of the code before it reaches the TransferSpreadsheet method as it's as though extra time is needed before the TransferSpreadsheet code is executed.

Thanks in advance.

Dale
ASKER CERTIFIED SOLUTION
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dale James
Dale James

ASKER

Thanks John and Jim....just going to apply your suggestions to see if the delay stops automation error from occurring.

Will get back with results soon.

Thanks

Dale
You can combine the two methods to obtain a very low load on the CPU while leaving the application responding.

Browse to the paragraph Simulating dialogue mode in my article:

Modern/Metro style message box and input box for Microsoft Access 2013+
Dale,

   Just on one point; you are far better off to look for some type of explicit event rather than placing a pause in code.   i.e., look for a file you can open in exclusive mode (no other app is touching it then), check for existence of a file in a directory, wait for a shelled process to finish, etc.  Waiting a set amount of time is always tricky and it may break at some point in the future once again.

  We might be able to suggest something better if you give us a little more detail on exactly what it is you are trying to do, or show us the code.

Jim.
All you probably need is the DoEvents prior to the DoCmd.
For performance reasons, avoid using DoCmd in a loop to wait. It will crush all the other apps running on your PC.

The Sleep command is the correct approach. See my paper on the difference including how to see the performance difference from the Windows Task Manager. Code is provided to wait for a number of seconds or a specific time.

http://fmsinc.com/MicrosoftAccess/modules/examples/AvoidDoEvents.asp