Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Out of memory - External Data (XL97/SR-2)

    I'm getting a sporadic 'Out of memory' error when some VBA code runs in XL97 to refresh a query table that's linked to an external data source. The code always works fine in XL2000; in XL97 it sometimes retrieves the data, but usually fails [Run-time error '7' - Out of memory]. The relevant code is an extended line assigning a value to the .Sql property, followed by a .Refresh statement - which is where the error is triggered. Success or failure seems to depend on what data the SQL statements retrieve, but I can't nail it down.

    Has anybody run into this problem? I can't find any reference to it in the MS Knowledge Base.

    TIA

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    An answer & a question

    In case anybody else runs in to this, I made a change that stops the error message from appearing. The worksheet has 2 command buttons. I'd created them both from the 'Control Toolbox' toolbar. When I replaced them with buttons from the Forms toolbar, the problem went away!

    This reminds me of a question that I've always meant to ask: which are the preferred set of controls to use when you're adding buttons, drop-downs, etc. to a worksheet? It would seem that the controls from the Control Toobox are more powerful, but they're more likely to create problems. At this point I'm thinking it's best to go with the Forms controls if at all possible.

    Thanks for any clarifications (Legare?).

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: An answer & a question

    The Forms controls are included with later releases of Office purely for backward's compatibilty and the official position would be that the Control Toolbox provide the tools you need.

    These are ActiveX controls and should not really be causing you problems, and the probem most probably lies in perhaps some settings in your registry - difficult to know exactly.

    Do you have any problems with using theses controls on userforms (in VBA) ?

    Andrew C

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: An answer & a question

    Thanks for the reply, Andrew. I had the impression that the Forms controls were 'older technology', so when I think about it I have been trying to use the ActiveX controls from the Control Toolbox. To answer your question, they work fine for me on Userforms.
    The 'Out of memory' problem, though, was occurring on multiple XL97 machines, so I doubt that it's registry related. Also, certain SQL retrievals work OK but not others. It's not related to the number of rows retrieved - it can fail when trying to retrieve 10 or so records, but happily pull in two or three hundred. It's also consistent: a given retrieval will either work or fail consistently.
    Very strange. Possibly tied to some memory alignment issue(?). Or maybe planetary alignment...

Posting Permissions

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