Using ADO Asynchronously in Microsoft Access to Speed up Forms
Our presenter for the next meeting, on March 12th, is Ben Clothier. Ben will talk to us about using ADO asynchronously in Access.
Have you had scenarios where you need to load large amount of data but you don’t want to make users wait for it? We will cover how we can improve the responsiveness of the forms by using asynchronous ADO to collect the data in background while allowing the users to interact with the form meanwhile. This involves using events with ADO’s objects.
Ben is a Solution Architect at IT Impact, an Access MVP since 2009, and a SharePoint Technology Specialist. He has worked on business applications from survey tracking to worldwide supply control databases. When he’s not working, he’s probably reviewing or writing something about Access development!
Click here for the meeting info:
Note we now use Microsoft teams for our meetings.
Is it possible to execute a query from VBA and simultaneously have a timer running on the form? I want the user to be able to see how long the query has been running. I’m talking about queries that may take 5+ minutes to run. Even better would be a way to cancel the running query. I have researched on how to do this and run into dead ends because VBA is single-threaded.
(please don’t suggest query optimization as a solution)
Yes, to some extent you can. You would need to make use of FetchProgress event for example. However, that may require you to make use of DoEvents? Me.Painting? Application.Echo? to ensure that the display is updated as it progress. Note that this usually works well enough when the form is mainly idling and waiting on the users’ input. However, if it’s busy doing things, we can miss event notifications as I alluded to in the presentation.
Yes, you can cancel an async process using Cancel method.
If you need to do this using DAO rather than ADO, then you need to be extra creative. One possible method is to make use of DAO.PrivDbEngine instance executed in a VBScript script. This blog post might be relevant in describing how you can “listen” to the VBScript script via a temporary text file.