Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004

    Question Unanswered: Refresh query fails

    I use a lot of VBA macros to retrieve data from a large database and manipulate it in Excel. One of the commands I use routinely is "QueryTable.Refresh". Most of the time, it works perfectly.

    Occassionally, when the file that I'm extracting the data from is very large, the macro stops with a "Runtime Error 1004 - General ODBC Error". When I try to debug it, I find that it has stopped at the "QueryTable.Refresh" command. Sometimes I can continue running it from this point. Other times, when I try to continue running it, I find that it has lost it's connect string. I have to manually enter it.

    Any suggestions how to avoid this error?


  2. #2
    Join Date
    Nov 2003
    Sounds like your connection is time out with a large amount of data. Try increasing the timeout amount to something really large and see if that helps.

  3. #3
    Join Date
    Nov 2004
    I've tried that. In the VBA macro, I've included this command:

    Application.ODBCTimeout = 1000

    The result was the same. Any other suggestions?

  4. #4
    Join Date
    Nov 2004
    timeout = 0 'unlimitted


    try retrieving the data in pieces (I like this better)

    top 20 percent...order by pk
    top 20 percent * from myTable where pk>lastPkRetrieved...order by pk
    to err is human ; to really mess things up requires a computer

Posting Permissions

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