We help IT Professionals succeed at work.

How to create delay of VBA code execution

Dale James
Dale James asked
on
67 Views
Last Modified: 2020-09-28
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
Comment
Watch Question

Software & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Dale JamesTherapist

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
All you probably need is the DoEvents prior to the DoCmd.
Luke ChungPresident
CERTIFIED EXPERT

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.