Run time Error 3035, System resource exceeded

Discussion in 'Microsoft Access Queries' started by Ryan, Dec 3, 2008.

  1. Ryan

    Ryan Guest

    I have this union query in access which produces a Run Time 3035 System
    Resource Exceeded error. If I remove one of the queries from the Union it
    works, but I have 10 more to add. Each of the queries have 24 columns. The
    result of the Union Query is passed to a Crosstab query. I tried changing
    the MaxLocksPerFile in the registry editor but that didnt help. Any
    suggestions??
    select * from B03ChargesPD
    UNION ALL
    select * from B04ChargesPD
    UNION ALL
    select * from C10ChargesPD
    UNION ALL
    select * from C11ChargesPD
    UNION ALL
    select * from C12ChargesPD
    UNION ALL
    select * from C16ChargesPD
    UNION ALL
    select * from C18ChargesPD
    UNION ALL
    select * from C24ChargesPD
    UNION ALL
    select * from C25ChargesPD
    UNION ALL
    select * from C27ChargesPD
    UNION ALL
    select * from C28ChargesPD
    UNION ALL
    select * from C29ChargesPD
    UNION ALL
    select * from C30ChargesPD
    UNION ALL
    select * from C31ChargesPD
    UNION ALL
    select * from C32ChargesPD
    UNION ALL
    select * from C32ChargesPD
    UNION ALL
    select * from C36ChargesPD
    UNION ALL
    select * from C37ChargesPD
    UNION ALL
    select * from C38ChargesPD
    UNION ALL
    select * from C39ChargesPD
    UNION ALL
    select * from C40ChargesPD
    UNION ALL
    select * from C41ChargesPD
    UNION ALL
    select * from C42ChargesPD
    UNION ALL
    select * from C43ChargesPD
    UNION ALL
    select * from C45ChargesPD
    UNION ALL
    select * from C46ChargesPD
    UNION ALL
    select * from C47ChargesPD
    UNION ALL
    select * from C48ChargesPD
    UNION ALL
    select * from C50ChargesPD
    UNION ALL
    select * from C51ChargesPD
    UNION ALL
    select * from C52ChargesPD
    UNION ALL
    select * from C56ChargesPD;
     
    Ryan, Dec 3, 2008
    #1
    1. Advertisements

  2. Ryan

    Golfinray Guest

    The maximum nuber of fields in a query is 250.

    "Ryan" wrote:

    > I have this union query in access which produces a Run Time 3035 System
    > Resource Exceeded error. If I remove one of the queries from the Union it
    > works, but I have 10 more to add. Each of the queries have 24 columns. The
    > result of the Union Query is passed to a Crosstab query. I tried changing
    > the MaxLocksPerFile in the registry editor but that didnt help. Any
    > suggestions??
    > select * from B03ChargesPD
    > UNION ALL
    > select * from B04ChargesPD
    > UNION ALL
    > select * from C10ChargesPD
    > UNION ALL
    > select * from C11ChargesPD
    > UNION ALL
    > select * from C12ChargesPD
    > UNION ALL
    > select * from C16ChargesPD
    > UNION ALL
    > select * from C18ChargesPD
    > UNION ALL
    > select * from C24ChargesPD
    > UNION ALL
    > select * from C25ChargesPD
    > UNION ALL
    > select * from C27ChargesPD
    > UNION ALL
    > select * from C28ChargesPD
    > UNION ALL
    > select * from C29ChargesPD
    > UNION ALL
    > select * from C30ChargesPD
    > UNION ALL
    > select * from C31ChargesPD
    > UNION ALL
    > select * from C32ChargesPD
    > UNION ALL
    > select * from C32ChargesPD
    > UNION ALL
    > select * from C36ChargesPD
    > UNION ALL
    > select * from C37ChargesPD
    > UNION ALL
    > select * from C38ChargesPD
    > UNION ALL
    > select * from C39ChargesPD
    > UNION ALL
    > select * from C40ChargesPD
    > UNION ALL
    > select * from C41ChargesPD
    > UNION ALL
    > select * from C42ChargesPD
    > UNION ALL
    > select * from C43ChargesPD
    > UNION ALL
    > select * from C45ChargesPD
    > UNION ALL
    > select * from C46ChargesPD
    > UNION ALL
    > select * from C47ChargesPD
    > UNION ALL
    > select * from C48ChargesPD
    > UNION ALL
    > select * from C50ChargesPD
    > UNION ALL
    > select * from C51ChargesPD
    > UNION ALL
    > select * from C52ChargesPD
    > UNION ALL
    > select * from C56ChargesPD;
    >
     
    Golfinray, Dec 3, 2008
    #2
    1. Advertisements

  3. Ryan

    Ryan Guest

    There are only 24 fields. Each query has the same 24 columns.

    "Golfinray" wrote:

    > The maximum nuber of fields in a query is 250.
    >
    > "Ryan" wrote:
    >
    > > I have this union query in access which produces a Run Time 3035 System
    > > Resource Exceeded error. If I remove one of the queries from the Union it
    > > works, but I have 10 more to add. Each of the queries have 24 columns. The
    > > result of the Union Query is passed to a Crosstab query. I tried changing
    > > the MaxLocksPerFile in the registry editor but that didnt help. Any
    > > suggestions??
    > > select * from B03ChargesPD
    > > UNION ALL
    > > select * from B04ChargesPD
    > > UNION ALL
    > > select * from C10ChargesPD
    > > UNION ALL
    > > select * from C11ChargesPD
    > > UNION ALL
    > > select * from C12ChargesPD
    > > UNION ALL
    > > select * from C16ChargesPD
    > > UNION ALL
    > > select * from C18ChargesPD
    > > UNION ALL
    > > select * from C24ChargesPD
    > > UNION ALL
    > > select * from C25ChargesPD
    > > UNION ALL
    > > select * from C27ChargesPD
    > > UNION ALL
    > > select * from C28ChargesPD
    > > UNION ALL
    > > select * from C29ChargesPD
    > > UNION ALL
    > > select * from C30ChargesPD
    > > UNION ALL
    > > select * from C31ChargesPD
    > > UNION ALL
    > > select * from C32ChargesPD
    > > UNION ALL
    > > select * from C32ChargesPD
    > > UNION ALL
    > > select * from C36ChargesPD
    > > UNION ALL
    > > select * from C37ChargesPD
    > > UNION ALL
    > > select * from C38ChargesPD
    > > UNION ALL
    > > select * from C39ChargesPD
    > > UNION ALL
    > > select * from C40ChargesPD
    > > UNION ALL
    > > select * from C41ChargesPD
    > > UNION ALL
    > > select * from C42ChargesPD
    > > UNION ALL
    > > select * from C43ChargesPD
    > > UNION ALL
    > > select * from C45ChargesPD
    > > UNION ALL
    > > select * from C46ChargesPD
    > > UNION ALL
    > > select * from C47ChargesPD
    > > UNION ALL
    > > select * from C48ChargesPD
    > > UNION ALL
    > > select * from C50ChargesPD
    > > UNION ALL
    > > select * from C51ChargesPD
    > > UNION ALL
    > > select * from C52ChargesPD
    > > UNION ALL
    > > select * from C56ChargesPD;
    > >
     
    Ryan, Dec 3, 2008
    #3
  4. Ryan

    Ryan Guest

    I am able to run the Union Query, the problem occures when the union query is
    passed to the Crosstab query. Here is my crosstab query if it will help.

    PARAMETERS [Forms]![Parameters].[LocationFilter] Text ( 255 ),
    [Forms]![Parameters].[StartDateFilter2] Text ( 255 ),
    [Forms]![Parameters].[EndDateFilter2] Text ( 255 );
    TRANSFORM Sum(AllChargesPD.Charges) AS SumOfCharges
    SELECT AllChargesPD.[Financial Class], Sum(AllChargesPD.Charges) AS
    [Financial Class Total]
    FROM AllChargesPD
    WHERE (((InStr("," & Forms!Parameters.LocationFilter & ",","," & [Database]
    & ","))>0))
    GROUP BY AllChargesPD.[Financial Class]
    PIVOT AllChargesPD.Database;


    "Golfinray" wrote:

    > The maximum nuber of fields in a query is 250.
    >
    > "Ryan" wrote:
    >
    > > I have this union query in access which produces a Run Time 3035 System
    > > Resource Exceeded error. If I remove one of the queries from the Union it
    > > works, but I have 10 more to add. Each of the queries have 24 columns. The
    > > result of the Union Query is passed to a Crosstab query. I tried changing
    > > the MaxLocksPerFile in the registry editor but that didnt help. Any
    > > suggestions??
    > > select * from B03ChargesPD
    > > UNION ALL
    > > select * from B04ChargesPD
    > > UNION ALL
    > > select * from C10ChargesPD
    > > UNION ALL
    > > select * from C11ChargesPD
    > > UNION ALL
    > > select * from C12ChargesPD
    > > UNION ALL
    > > select * from C16ChargesPD
    > > UNION ALL
    > > select * from C18ChargesPD
    > > UNION ALL
    > > select * from C24ChargesPD
    > > UNION ALL
    > > select * from C25ChargesPD
    > > UNION ALL
    > > select * from C27ChargesPD
    > > UNION ALL
    > > select * from C28ChargesPD
    > > UNION ALL
    > > select * from C29ChargesPD
    > > UNION ALL
    > > select * from C30ChargesPD
    > > UNION ALL
    > > select * from C31ChargesPD
    > > UNION ALL
    > > select * from C32ChargesPD
    > > UNION ALL
    > > select * from C32ChargesPD
    > > UNION ALL
    > > select * from C36ChargesPD
    > > UNION ALL
    > > select * from C37ChargesPD
    > > UNION ALL
    > > select * from C38ChargesPD
    > > UNION ALL
    > > select * from C39ChargesPD
    > > UNION ALL
    > > select * from C40ChargesPD
    > > UNION ALL
    > > select * from C41ChargesPD
    > > UNION ALL
    > > select * from C42ChargesPD
    > > UNION ALL
    > > select * from C43ChargesPD
    > > UNION ALL
    > > select * from C45ChargesPD
    > > UNION ALL
    > > select * from C46ChargesPD
    > > UNION ALL
    > > select * from C47ChargesPD
    > > UNION ALL
    > > select * from C48ChargesPD
    > > UNION ALL
    > > select * from C50ChargesPD
    > > UNION ALL
    > > select * from C51ChargesPD
    > > UNION ALL
    > > select * from C52ChargesPD
    > > UNION ALL
    > > select * from C56ChargesPD;
    > >
     
    Ryan, Dec 3, 2008
    #4
  5. Ryan

    Ryan Guest

    Microsoft Access database specifications
    Number of tables in a query 32
    Could this be the reason? I would think this would be the reason, but the
    Union query will run, I only get the error when the crosstab query runs.


    "Ryan" wrote:

    > I have this union query in access which produces a Run Time 3035 System
    > Resource Exceeded error. If I remove one of the queries from the Union it
    > works, but I have 10 more to add. Each of the queries have 24 columns. The
    > result of the Union Query is passed to a Crosstab query. I tried changing
    > the MaxLocksPerFile in the registry editor but that didnt help. Any
    > suggestions??
    > select * from B03ChargesPD
    > UNION ALL
    > select * from B04ChargesPD
    > UNION ALL
    > select * from C10ChargesPD
    > UNION ALL
    > select * from C11ChargesPD
    > UNION ALL
    > select * from C12ChargesPD
    > UNION ALL
    > select * from C16ChargesPD
    > UNION ALL
    > select * from C18ChargesPD
    > UNION ALL
    > select * from C24ChargesPD
    > UNION ALL
    > select * from C25ChargesPD
    > UNION ALL
    > select * from C27ChargesPD
    > UNION ALL
    > select * from C28ChargesPD
    > UNION ALL
    > select * from C29ChargesPD
    > UNION ALL
    > select * from C30ChargesPD
    > UNION ALL
    > select * from C31ChargesPD
    > UNION ALL
    > select * from C32ChargesPD
    > UNION ALL
    > select * from C32ChargesPD
    > UNION ALL
    > select * from C36ChargesPD
    > UNION ALL
    > select * from C37ChargesPD
    > UNION ALL
    > select * from C38ChargesPD
    > UNION ALL
    > select * from C39ChargesPD
    > UNION ALL
    > select * from C40ChargesPD
    > UNION ALL
    > select * from C41ChargesPD
    > UNION ALL
    > select * from C42ChargesPD
    > UNION ALL
    > select * from C43ChargesPD
    > UNION ALL
    > select * from C45ChargesPD
    > UNION ALL
    > select * from C46ChargesPD
    > UNION ALL
    > select * from C47ChargesPD
    > UNION ALL
    > select * from C48ChargesPD
    > UNION ALL
    > select * from C50ChargesPD
    > UNION ALL
    > select * from C51ChargesPD
    > UNION ALL
    > select * from C52ChargesPD
    > UNION ALL
    > select * from C56ChargesPD;
    >
     
    Ryan, Dec 3, 2008
    #5
  6. Ryan

    drinski67

    Joined:
    Jan 21, 2008
    Likes Received:
    0
    Same message (Run-time error '3035': System Resource exceeded.) appear to me when I run Query in Access 2007:

    sSQL ="UPDATE TempTbl SET [fldID] = bmConvCode([fldID],2,4), [fldDesc] = bmConvCode([fldDesc],2,4);"

    where bmConvCode() is my function...

    I generateed SQL by code, a and execute by "CurrentDb.Execute sSQL"

    an it works fine for 24 similar update queries, and than stoped at 25th.

    Database have over 30 tabels and I generate SQL Update for every tabe and text field in table.
    Database size is about 350MB.

    How to finish the job?
     
    Last edited: Dec 13, 2008
    drinski67, Dec 13, 2008
    #6
  7. Ryan

    drinski67

    Joined:
    Jan 21, 2008
    Likes Received:
    0
    I found a solution:

    Application.DBEngine.SetOption dbMaxLocksPerFile, 1000000

    after that everythng is OK...

    Strange...

    because I Create Linked Tabel, execute SQL, and delete TableDef... so I always had one table in game... and 25th table is not event to long... it is one of shorter...

    Strange...

    Microsoft product...

    Strange...
     
    drinski67, Dec 13, 2008
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Jackie

    System Resource exceeded

    Jackie, Sep 9, 2003, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    450
    Jackie
    Sep 10, 2003
  2. Tim Dolloff

    System Resource Exceeded Error

    Tim Dolloff, Sep 16, 2003, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    507
    [MVP] S. Clark
    Sep 17, 2003
  3. berty

    System Resource Exceeded??

    berty, Sep 25, 2003, in forum: Microsoft Access Queries
    Replies:
    0
    Views:
    535
    berty
    Sep 25, 2003
  4. Patrick

    ACC2002: System resource exceeded (3035)

    Patrick, Oct 8, 2003, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    6,215
    John Viescas
    Oct 9, 2003
  5. David Gibson

    "System Resource Exceeded" - message when running query

    David Gibson, Nov 13, 2003, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    455
    GreySky
    Nov 14, 2003
  6. Stu

    System Resource Exceeded

    Stu, Nov 28, 2003, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    524
    JIMMIE WHITAKER
    Nov 29, 2003
  7. ody

    system resource exceeded

    ody, Apr 28, 2004, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    195
    Van T. Dinh
    Apr 28, 2004
  8. BlueWolverine

    3035 Exceeded System Resources - Query Too Complex

    BlueWolverine, Mar 5, 2009, in forum: Microsoft Access Queries
    Replies:
    5
    Views:
    333
    BlueWolverine
    Mar 5, 2009
Loading...