Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Unanswered: Code to busy to execute some statements?

    Hi guys,

    Difficult to explain this one really...

    I have an import script that pulls in, sorts through, appends, deletes, all that jazz.

    Script works great, only thing is, due to the amount of data, and the quantity of manipulation/checks that need to be carried out, it can take some time to finish executing completely.

    This isn't a problem, and is to be expected...

    What is a problem, is that Access seems to hang while this is occuring.

    I toggle on a red box on my form that indicates that the import is going on, but it doesn't move... So there's no feedback to tell the user that Access hasn't crashed, just that it is busy.


    What I want to happen, and what I've set up, are 4 red boxes, that have their visibility toggled on and off, to 'move' in the following sequence:

    Code:
    O x x x    .5 secs later...
    x O x x    .5 secs later...
    x x O x    .5 secs later...
    x x x O    .5 secs later...
    O x x x    .5 secs later...
    and so on.


    The problem is, that access seems to be dedicating all its available threads to performing the import operation, and fails to update the boxes until a msgbox appears, in which case the threads (or a thread) are freed, and the boxes will activate.


    I also thought it might be too much stress on Access to turn the visibility of objects on and off, so I created a label, and set each loop of the main while loop to indicate which recordset.absolutePosition it was on, and that would also skip about 30-40 records at a time.

    The only thing I can get to update on each loop is a MsgBox...

    Code:
    MsgBox "Current Record is: " & rs.absolutePosition
    Will present a popup for every single record.


    I've just remembered about the Form.repaint function, which will achieve what I'm after...

    However, I can imagine that at some point in the not so distant future, I would have cause for manually adjusting thread priority... I know that Java and C++ have capabilities for these functions, but does VB/VBA at all?

    I can imagine if I wanted to run simultaneous queries then this would come in very handy.

    Is this possible through some advance API's? Or am I shit outta luck?


    Thanks guys.
    Looking for the perfect beer...

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you feel adventurous and have a way to restore your system (backup, install CD, Ghost image, etc. ) you could try playing with some registry keys:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engi nes\Jet 3.5\Threads
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Jet 3.x\Threads
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Jet 4.0\Threads

    Theorically increasing the values should allow the simultaneous execution of more queries. I once tried and while slightly increasing the default values (3 on my system) to 4 or 5 did not seem to change anything as far as the performances of Access were concerned, higher values crashed Access and, in one occasion, hung the system (BSOD).

    On the other hand, there's a site (in French) that explain how to use APIs to create several theads in a VBA program (sample code provided). I never tried, though:
    Access - Les Sources

    An interesting site about multi-threading In VB5 and VB6 (should work for VBA too) is:
    FreeVBCode code snippet: A Thread to Visual Basic: Multi-Threading In VB5 and VB6
    Have a nice day!

  3. #3
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Wow, thanks a lot!

    I'll have a read through them and see what can be done.

    What I've found moving from Java to VBA is that the more low level abilities of the language seem to be very poorly documented, if at all.

    I realise that Java has probably the largest set of online documentation in terms of its reference libraries, etc. But it's always a nice revelation when you find that a language has more to offer than meets the eye.

    Very interesting, thanks again!

    End of the week anyway, have a good one, it's on me!
    Looking for the perfect beer...

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider using doevents in a loop
    the access ui often lags behind things going on in background. sometimes the UI doesn't update presumably becuase the scheduler decides its got more important thing to do
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •