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
    Messages:
    3
    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
    Messages:
    3
    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?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jackie

    System Resource exceeded

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

    System Resource Exceeded Error

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

    System Resource Exceeded??

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

    ACC2002: System resource exceeded (3035)

    Patrick, Oct 8, 2003, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    5,854
    John Viescas
    Oct 9, 2003
  5. BlueWolverine

    3035 Exceeded System Resources - Query Too Complex

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

Share This Page