0

I am reading about options that will allow me to run a macro without locking down my session of Access until it is done running. I have some VBA macros for Access that execute SQL SELECT and UPDATE statements and can take almost an hour to run. This currently locks down Access for me until it is done running.

I am seeing what options there are so that I can the macro and and still use Access while it finishes running the report in the background. I came across DoEvents() as an option, but just about everything says not to us it. So what are some good alternatives?

cjones
  • 8,384
  • 17
  • 81
  • 175
  • 2
    Macro running for hours... Access has a limit of 2GB DB size, unless you link many databases together... Maybe your macro needs some optimization. Or you database does. Do you use proper indexes? Use SQL wherever you can instead of macros. – vacip Jan 12 '16 at 23:28

1 Answers1

3

DoEvents has its uses, e.g. if you have a long running loop and want to give the user a "Cancel" button that he can actually click.

But in your use case the best option is:

  • Make a copy of your frontend database
  • Run your VBA code in one instance of Access with the first frontend
  • Start a second instance of Access, open the second frontend, do whatever you want (well, you shouldn't do something that locks tables used by the first instance).
Andre
  • 26,751
  • 7
  • 36
  • 80