Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    2

    Question Unanswered: Slow Response - Using Macro for Multiple "Transfer Spreadsheets" from Access to Excel

    Hi, I have been using the Transfer spreadsheet function - within a Macro in MS Access - to transfer multiple outputs to Excel. This has worked very well to date.

    The issue I have is that it seems the more "Transfers" I do within a Macro - the longer the process seems to take (it seems to be exponential) to the point where the process seems to hang completely. Running each query individually within MS Access is almost instant and all are working fine individually.

    1. Is there a buffer type issue that I am hitting?
    2. Is there a way of dealing with this.

    Any thoughts greatly appreciated.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by mrbc33 View Post
    ...The issue I have is that it seems the more "Transfers" I do within a Macro - the longer the process seems to take (it seems to be exponential)
    Would you really expect this to behave differently? At a given speed, doesn't it take you longer to drive 10 miles than to drive 5 miles? And longer still to drive 20 miles?

    With today's super-fast processors we tend to forget about things like processing time, and up to a certain point things seem almost instantaneous, but at some point this ideal behavior stops, as it apparently has in your case.

    I suspect by both the slowing down and the "hanging" that you are reaching your system's limits. Someone else may have suggestions on tweaking your system settings, but you could try inserting a DoEvents command between the running of each transfer. This should allow one transfer to complete before the next starts, which may alleviate the problem to some degree.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jun 2010
    Posts
    2

    Resp

    I would expect it to work differently: 1+1+1 = 3 is expected - this is 1+1+1 = 30 or nothing at all..... It seems like the next transfer commences before the previous one has completed.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by mrbc33 View Post
    ... It seems like the next transfer commences before the previous one has completed.
    I suspect you're right, which is why I said


    Quote Originally Posted by Missinglinq View Post
    ...you could try inserting a DoEvents command between the running of each transfer. This should allow one transfer to complete before the next starts, which may alleviate the problem to some degree.
    Have you tried the above solution? Access runs code asynchronously, which is to say if you have

    Command1
    Command2
    Command3

    Access executes Command1 then Command2 then Command3 without waiting for the previous command to complete! The DoEvents command allows one command/code to complete before proceeding to the next command.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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