Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Not enough disk space or memory

Discussion in 'Information Technology' started by Tom Ellison, Jul 28, 2006.

  1. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    In trying to automate a series of steps in VBA, I have a step:

    DoCmd.RunSql("DELETE * FROM SomeTable WHERE SomeColumn = "Some Value")

    I get a warning with which I'm not familiar:

    There isn't enough disk space or memory to undo the data changes this action
    query is about to make.

    Do you want to run this action query anyway?
    For information on freeing disk space or freeing memory, search the
    Microsoft Windows Help index for 'disk space, freeing or 'memory,
    troubleshooting.'

    Yes No

    This confuses me. Under what conditions will access undo the results of an
    action query after it has been performed?

    I have tens of gigabytes available on the only volume this computer
    posseses. I presume that isn't the problem, so it must be that my memory is
    short. I have a gigabyte of ram and there's nothing else running on my XP
    system but Access, and a few rather standard background things. I'm saying
    I have a large amount of free memory.

    Anyway, what is this about? I'd like to have it not stop at all. I'm
    trying to get a long list of things done while I sleep.

    And thanks very much.

    Tom Ellison
     
  2. Loading...

    Similar Threads Forum Date
    right, enough of that! Blogs Oct 16, 2015
    Arts/Society L-FRESH The LION: "Australia doesn't take racism seriously enough" Language, Arts & Culture May 25, 2014
    Arts/Society Gurudwara done enough or not ? Language, Arts & Culture Sep 20, 2013
    ‘Enough was Enough’, Says Pioneer of Respect to Sikh Turban Campaign Sikh Youth Feb 8, 2013
    India Anand Marriage Act amendment not enough: US Sikh bodies Breaking News May 25, 2012

  3. Ken Snell \(MVP\)

    Ken Snell \(MVP\)
    Expand Collapse
    Guest

    Tom -

    That is one of the "built-in" warnings that ACCESS can display when running
    action queries. It simply means that Jet won't be able to "remember" all the
    changes being made because there are too many records being affected, so it
    warns you that it cannot undo the action should an error occur partway
    through the query's run.

    You can turn that message off by the SetWarnings action before you run the
    query:

    DoCmd.SetWarnings False
    DoCmd.RunSql("DELETE * FROM SomeTable WHERE SomeColumn = "Some Value")
    DoCmd.SetWarnings True

    --

    Ken Snell
    <MS ACCESS MVP>



    "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    news:%23dTbh2tdGHA.3556@TK2MSFTNGP02.phx.gbl...
    > In trying to automate a series of steps in VBA, I have a step:
    >
    > DoCmd.RunSql("DELETE * FROM SomeTable WHERE SomeColumn = "Some Value")
    >
    > I get a warning with which I'm not familiar:
    >
    > There isn't enough disk space or memory to undo the data changes this
    > action query is about to make.
    >
    > Do you want to run this action query anyway?
    > For information on freeing disk space or freeing memory, search the
    > Microsoft Windows Help index for 'disk space, freeing or 'memory,
    > troubleshooting.'
    >
    > Yes No
    >
    > This confuses me. Under what conditions will access undo the results of
    > an action query after it has been performed?
    >
    > I have tens of gigabytes available on the only volume this computer
    > posseses. I presume that isn't the problem, so it must be that my memory
    > is short. I have a gigabyte of ram and there's nothing else running on my
    > XP system but Access, and a few rather standard background things. I'm
    > saying I have a large amount of free memory.
    >
    > Anyway, what is this about? I'd like to have it not stop at all. I'm
    > trying to get a long list of things done while I sleep.
    >
    > And thanks very much.
    >
    > Tom Ellison
    >
    >
     
  4. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Hey Key,

    Thanks.

    I hadn't thought of the fact that Access can back out if an error occurs
    part way through. I'm sure now you're right about this.

    I can use the SetWarnings. But for my purpose, I'd like to just turn them
    off, permanently. I was looking thought the options for this. Maybe I'll
    find it now.

    See you soon, I hope.

    Tom


    "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
    news:%23dF2vhudGHA.2188@TK2MSFTNGP05.phx.gbl...
    > Tom -
    >
    > That is one of the "built-in" warnings that ACCESS can display when
    > running action queries. It simply means that Jet won't be able to
    > "remember" all the changes being made because there are too many records
    > being affected, so it warns you that it cannot undo the action should an
    > error occur partway through the query's run.
    >
    > You can turn that message off by the SetWarnings action before you run the
    > query:
    >
    > DoCmd.SetWarnings False
    > DoCmd.RunSql("DELETE * FROM SomeTable WHERE SomeColumn = "Some Value")
    > DoCmd.SetWarnings True
    >
    > --
    >
    > Ken Snell
    > <MS ACCESS MVP>
    >
    >
    >
    > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    > news:%23dTbh2tdGHA.3556@TK2MSFTNGP02.phx.gbl...
    >> In trying to automate a series of steps in VBA, I have a step:
    >>
    >> DoCmd.RunSql("DELETE * FROM SomeTable WHERE SomeColumn = "Some Value")
    >>
    >> I get a warning with which I'm not familiar:
    >>
    >> There isn't enough disk space or memory to undo the data changes this
    >> action query is about to make.
    >>
    >> Do you want to run this action query anyway?
    >> For information on freeing disk space or freeing memory, search the
    >> Microsoft Windows Help index for 'disk space, freeing or 'memory,
    >> troubleshooting.'
    >>
    >> Yes No
    >>
    >> This confuses me. Under what conditions will access undo the results of
    >> an action query after it has been performed?
    >>
    >> I have tens of gigabytes available on the only volume this computer
    >> posseses. I presume that isn't the problem, so it must be that my memory
    >> is short. I have a gigabyte of ram and there's nothing else running on
    >> my XP system but Access, and a few rather standard background things.
    >> I'm saying I have a large amount of free memory.
    >>
    >> Anyway, what is this about? I'd like to have it not stop at all. I'm
    >> trying to get a long list of things done while I sleep.
    >>
    >> And thanks very much.
    >>
    >> Tom Ellison
    >>
    >>

    >
    >
     
  5. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi, Tom.

    > I can use the SetWarnings. But for my purpose, I'd like to just turn them
    > off, permanently.


    You don't need to turn them off to avoid these messages. Try:

    CurrentDb().Execute "DELETE * " & _
    "FROM SomeTable " & _
    "WHERE SomeColumn = 'Some Value';", dbFailOnError


    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
    http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
    info.


    "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    news:uxu7jWvdGHA.4892@TK2MSFTNGP02.phx.gbl...
    > Hey Key,
    >
    > Thanks.
    >
    > I hadn't thought of the fact that Access can back out if an error occurs
    > part way through. I'm sure now you're right about this.
    >
    > I can use the SetWarnings. But for my purpose, I'd like to just turn them
    > off, permanently. I was looking thought the options for this. Maybe I'll
    > find it now.
    >
    > See you soon, I hope.
    >
    > Tom
    >
    >
    > "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
    > news:%23dF2vhudGHA.2188@TK2MSFTNGP05.phx.gbl...
    >> Tom -
    >>
    >> That is one of the "built-in" warnings that ACCESS can display when
    >> running action queries. It simply means that Jet won't be able to
    >> "remember" all the changes being made because there are too many records
    >> being affected, so it warns you that it cannot undo the action should an
    >> error occur partway through the query's run.
    >>
    >> You can turn that message off by the SetWarnings action before you run
    >> the query:
    >>
    >> DoCmd.SetWarnings False
    >> DoCmd.RunSql("DELETE * FROM SomeTable WHERE SomeColumn = "Some Value")
    >> DoCmd.SetWarnings True
    >>
    >> --
    >>
    >> Ken Snell
    >> <MS ACCESS MVP>
    >>
    >>
    >>
    >> "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    >> news:%23dTbh2tdGHA.3556@TK2MSFTNGP02.phx.gbl...
    >>> In trying to automate a series of steps in VBA, I have a step:
    >>>
    >>> DoCmd.RunSql("DELETE * FROM SomeTable WHERE SomeColumn = "Some Value")
    >>>
    >>> I get a warning with which I'm not familiar:
    >>>
    >>> There isn't enough disk space or memory to undo the data changes this
    >>> action query is about to make.
    >>>
    >>> Do you want to run this action query anyway?
    >>> For information on freeing disk space or freeing memory, search the
    >>> Microsoft Windows Help index for 'disk space, freeing or 'memory,
    >>> troubleshooting.'
    >>>
    >>> Yes No
    >>>
    >>> This confuses me. Under what conditions will access undo the results of
    >>> an action query after it has been performed?
    >>>
    >>> I have tens of gigabytes available on the only volume this computer
    >>> posseses. I presume that isn't the problem, so it must be that my
    >>> memory is short. I have a gigabyte of ram and there's nothing else
    >>> running on my XP system but Access, and a few rather standard background
    >>> things. I'm saying I have a large amount of free memory.
    >>>
    >>> Anyway, what is this about? I'd like to have it not stop at all. I'm
    >>> trying to get a long list of things done while I sleep.
    >>>
    >>> And thanks very much.
    >>>
    >>> Tom Ellison
    >>>
    >>>

    >>
    >>

    >
    >
     
  6. Ken Snell \(MVP\)

    Ken Snell \(MVP\)
    Expand Collapse
    Guest

    Tools | Options | Edit/Find (tab) | Confirm
    --

    Ken Snell
    <MS ACCESS MVP>


    "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    news:uxu7jWvdGHA.4892@TK2MSFTNGP02.phx.gbl...
    > Hey Key,
    >
    > Thanks.
    >
    > I hadn't thought of the fact that Access can back out if an error occurs
    > part way through. I'm sure now you're right about this.
    >
    > I can use the SetWarnings. But for my purpose, I'd like to just turn them
    > off, permanently. I was looking thought the options for this. Maybe I'll
    > find it now.
    >
    > See you soon, I hope.
    >
    > Tom
    >
    >
    > "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
    > news:%23dF2vhudGHA.2188@TK2MSFTNGP05.phx.gbl...
    >> Tom -
    >>
    >> That is one of the "built-in" warnings that ACCESS can display when
    >> running action queries. It simply means that Jet won't be able to
    >> "remember" all the changes being made because there are too many records
    >> being affected, so it warns you that it cannot undo the action should an
    >> error occur partway through the query's run.
    >>
    >> You can turn that message off by the SetWarnings action before you run
    >> the query:
    >>
    >> DoCmd.SetWarnings False
    >> DoCmd.RunSql("DELETE * FROM SomeTable WHERE SomeColumn = "Some Value")
    >> DoCmd.SetWarnings True
    >>
    >> --
    >>
    >> Ken Snell
    >> <MS ACCESS MVP>
    >>
    >>
    >>
    >> "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    >> news:%23dTbh2tdGHA.3556@TK2MSFTNGP02.phx.gbl...
    >>> In trying to automate a series of steps in VBA, I have a step:
    >>>
    >>> DoCmd.RunSql("DELETE * FROM SomeTable WHERE SomeColumn = "Some Value")
    >>>
    >>> I get a warning with which I'm not familiar:
    >>>
    >>> There isn't enough disk space or memory to undo the data changes this
    >>> action query is about to make.
    >>>
    >>> Do you want to run this action query anyway?
    >>> For information on freeing disk space or freeing memory, search the
    >>> Microsoft Windows Help index for 'disk space, freeing or 'memory,
    >>> troubleshooting.'
    >>>
    >>> Yes No
    >>>
    >>> This confuses me. Under what conditions will access undo the results of
    >>> an action query after it has been performed?
    >>>
    >>> I have tens of gigabytes available on the only volume this computer
    >>> posseses. I presume that isn't the problem, so it must be that my
    >>> memory is short. I have a gigabyte of ram and there's nothing else
    >>> running on my XP system but Access, and a few rather standard background
    >>> things. I'm saying I have a large amount of free memory.
    >>>
    >>> Anyway, what is this about? I'd like to have it not stop at all. I'm
    >>> trying to get a long list of things done while I sleep.
    >>>
    >>> And thanks very much.
    >>>
    >>> Tom Ellison
    >>>
    >>>

    >>
    >>

    >
    >
     
  7. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi, Tom.

    > Under what conditions will access undo the results of an action query
    > after it has been performed?


    Access will undo the results of an action query whenever the transaction is
    rolled back. This can happen whenever an error occurs while the action
    query is running during an implicit transaction, or when the operation
    occurs inside an explicitly declared transaction, and then the Rollback
    command is issued instead of CommitTrans.

    > I have tens of gigabytes available on the only volume this computer
    > posseses. I presume that isn't the problem, so it must be that my memory
    > is short.


    This generally is not the case. A temporary 2 GB database file is created
    on your hard drive to hold the data from the transaction until it's
    finished, whereupon the results are placed in your database file. Your
    query is exceeding the allotted space for the transaction within this
    temporary database file, so Jet is asking your permission to recycle the
    empty data pages in order to compensate.

    > I'd like to have it not stop at all. I'm trying to get a long list of
    > things done while I sleep.


    There are several ways to avoid this problem, either within VBA code (as
    suggested elsewhere in this thread), or via the Windows Registry by changing
    the MaxLocksPerFile option, or setting the query's UseTransaction Property
    to No. This last choice is generally not the preferred method, as any
    changes made to the data during the operation will remain, even if the query
    fails at some point.

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
    http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
    info.


    "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    news:%23dTbh2tdGHA.3556@TK2MSFTNGP02.phx.gbl...
    > In trying to automate a series of steps in VBA, I have a step:
    >
    > DoCmd.RunSql("DELETE * FROM SomeTable WHERE SomeColumn = "Some Value")
    >
    > I get a warning with which I'm not familiar:
    >
    > There isn't enough disk space or memory to undo the data changes this
    > action query is about to make.
    >
    > Do you want to run this action query anyway?
    > For information on freeing disk space or freeing memory, search the
    > Microsoft Windows Help index for 'disk space, freeing or 'memory,
    > troubleshooting.'
    >
    > Yes No
    >
    > This confuses me. Under what conditions will access undo the results of
    > an action query after it has been performed?
    >
    > I have tens of gigabytes available on the only volume this computer
    > posseses. I presume that isn't the problem, so it must be that my memory
    > is short. I have a gigabyte of ram and there's nothing else running on my
    > XP system but Access, and a few rather standard background things. I'm
    > saying I have a large amount of free memory.
    >
    > Anyway, what is this about? I'd like to have it not stop at all. I'm
    > trying to get a long list of things done while I sleep.
    >
    > And thanks very much.
    >
    > Tom Ellison
    >
    >
     
  8. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    To add some to Gunny's excellent advice, please see the following KB article:

    You may receive a "There isn't enough disk space or memory" error message
    when you perform an operation on an Access table

    http://support.microsoft.com/kb/286153/


    Tom Wickerath, Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "'69 Camaro" wrote:

    > Hi, Tom.
    >
    > > I can use the SetWarnings. But for my purpose, I'd like to just turn them
    > > off, permanently.

    >
    > You don't need to turn them off to avoid these messages. Try:
    >
    > CurrentDb().Execute "DELETE * " & _
    > "FROM SomeTable " & _
    > "WHERE SomeColumn = 'Some Value';", dbFailOnError
    >
    >
    > HTH.
    > Gunny
    >
    > See http://www.QBuilt.com for all your database needs.
    > See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
    > http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
    > info.
     
  9. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi, Ken.

    > Tools | Options | Edit/Find (tab) | Confirm


    Turning off the confirmation messages won't affect whether or not the lack
    of disk space/memory confirmation message appears while running an action
    query. This situation will still require manual input from the user before
    proceding with the rest of the action query. Two ways to avoid this message
    while running an action query in VBA is to either turn warnings off before
    running the RunSQL method, as you suggested, or to use the Execute method
    with the dbFailOnError argument, as I suggested. The Execute method forces
    Jet to allocate more space in the temporary 2 GB database file where it
    writes the data for the transaction, thereby avoiding the "Not enough disk
    space or memory" message.

    There are a few other benefits to using the Execute method rather than the
    RunSQL method, as listed by Allen Browne on the following Web page,
    including the fact that the query runs faster:

    http://groups.google.com/group/micr...5290/fbed3995447ec827?&hl=en#fbed3995447ec827

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
    http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
    info.


    "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
    news:OmCUVvvdGHA.1656@TK2MSFTNGP02.phx.gbl...
    > Tools | Options | Edit/Find (tab) | Confirm
    > --
    >
    > Ken Snell
    > <MS ACCESS MVP>
    >
    >
    > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    > news:uxu7jWvdGHA.4892@TK2MSFTNGP02.phx.gbl...
    >> Hey Key,
    >>
    >> Thanks.
    >>
    >> I hadn't thought of the fact that Access can back out if an error occurs
    >> part way through. I'm sure now you're right about this.
    >>
    >> I can use the SetWarnings. But for my purpose, I'd like to just turn
    >> them off, permanently. I was looking thought the options for this.
    >> Maybe I'll find it now.
    >>
    >> See you soon, I hope.
    >>
    >> Tom
    >>
    >>
    >> "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
    >> news:%23dF2vhudGHA.2188@TK2MSFTNGP05.phx.gbl...
    >>> Tom -
    >>>
    >>> That is one of the "built-in" warnings that ACCESS can display when
    >>> running action queries. It simply means that Jet won't be able to
    >>> "remember" all the changes being made because there are too many records
    >>> being affected, so it warns you that it cannot undo the action should an
    >>> error occur partway through the query's run.
    >>>
    >>> You can turn that message off by the SetWarnings action before you run
    >>> the query:
    >>>
    >>> DoCmd.SetWarnings False
    >>> DoCmd.RunSql("DELETE * FROM SomeTable WHERE SomeColumn = "Some Value")
    >>> DoCmd.SetWarnings True
    >>>
    >>> --
    >>>
    >>> Ken Snell
    >>> <MS ACCESS MVP>
    >>>
    >>>
    >>>
    >>> "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    >>> news:%23dTbh2tdGHA.3556@TK2MSFTNGP02.phx.gbl...
    >>>> In trying to automate a series of steps in VBA, I have a step:
    >>>>
    >>>> DoCmd.RunSql("DELETE * FROM SomeTable WHERE SomeColumn = "Some Value")
    >>>>
    >>>> I get a warning with which I'm not familiar:
    >>>>
    >>>> There isn't enough disk space or memory to undo the data changes this
    >>>> action query is about to make.
    >>>>
    >>>> Do you want to run this action query anyway?
    >>>> For information on freeing disk space or freeing memory, search the
    >>>> Microsoft Windows Help index for 'disk space, freeing or 'memory,
    >>>> troubleshooting.'
    >>>>
    >>>> Yes No
    >>>>
    >>>> This confuses me. Under what conditions will access undo the results
    >>>> of an action query after it has been performed?
    >>>>
    >>>> I have tens of gigabytes available on the only volume this computer
    >>>> posseses. I presume that isn't the problem, so it must be that my
    >>>> memory is short. I have a gigabyte of ram and there's nothing else
    >>>> running on my XP system but Access, and a few rather standard
    >>>> background things. I'm saying I have a large amount of free memory.
    >>>>
    >>>> Anyway, what is this about? I'd like to have it not stop at all. I'm
    >>>> trying to get a long list of things done while I sleep.
    >>>>
    >>>> And thanks very much.
    >>>>
    >>>> Tom Ellison
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     

Share This Page