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
CitrixMicrosoft AccessVBA

Avatar of undefined
Last Comment
Luke Chung

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
John Tsioumpris

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Gustav Brock

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+
Jim Dettman (EE MVE)

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Martin Liss

All you probably need is the DoEvents prior to the DoCmd.
Luke Chung

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