Anyone know a "Super DoEvents" or a way to force OLEDBConnection to update its refreshing property?

Tobriand

New Member
Joined
Dec 20, 2012
Messages
7
Hi All,

I've discovered recently that DoEvents doesn't yield to all events - for instance, OLEDBConnection objects do not update whilst VBA code is running when their state has been changed during a .Calculate call made from VBA, even if followed by a DoEvents loop. Aside from using scheduled firing of code (OnTime, SetTimer) - which is suitable some of the time, but not always - are there any APIs anyone can think of that would allow me to manually yield execution totally, and resume mid-procedure - something which would have a similar effect as:

Code:
Private mblnTotalYieldInProcess As Boolean

Public Sub ReCalc()
    If mblnTotalYieldInProcess Then Goto AfterYield

    <... Preceding actions here ...>

    blnInitialState = oCN.OLEDBConnection.Refreshing
    mblnTotalYieldInProcess = True
    Application.OnTime Now, "ReCalc"
    Exit Sub

AfterYield:
    mblnTotalYieldInProcess = False
    blnFinalState = oCN.OLEDBConnection.Refreshing

    <... Rest of code here ...>

End Sub
But a bit less horrible. This approach will almost certainly work, but for a variety of reasons there's a fair chance I might have two of these running asynchronously, and that could get very messy very quickly. I can think of a few hacks to separate them (arrays of UDTs containing a GUID and the boolean, for instance, and an optional strGUID argument for ReCalc) but they feel like hacks. Does anyone know of any better approach than this?

I should also point out, I haven't actually tried Application.OnTime Now; I'm using Application.OnTime Now + CDate("00:00:01"), which obviously is a much longer pause. I'm not 100% certain that scheduling immediate execution will yield enough, though, so completely different approaches would be better.

Thanks in advance,
Toby

EDIT: Formatting
 

Forum statistics

Threads
1,081,417
Messages
5,358,553
Members
400,503
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top