error: system resources exceeded

Hi Every One,

i have a data base with 3.20MB ... with 6 tables and 4 querries i think! it is not so big! the recordes were 900 or less!
i have made it for our Library Department in our Center. BUT NOW, they brought me back the laptop, which had a database on it, and telling me that the laptop have been attact by a virus, and the data base does not work any more...and they do not have any backups!!

i'm out of my mind! i do not know what to do! the laptop must be formated, thats for sure...but i just copied the data base *.mdb and tried to run it on my PC then i get the error message: "system resources exceeded" and i just have to click on ok, The End!

what should i do? i just need one table in the data base, i tried to emport it from a new data base but the same message comes!

please please help me! i have made a backup 3 monthes ago, the database was only 1.8MB. they told me, that they have entert about 100 new enteries...it seems to me very strage! how could 100 enteries make the database size from 1.8 to 3.2MB! is it possible that a virus couse this?? i tried also to scan te data, but there is no viruses to be recognized!



when do they learn to seperate the work computer from the entertainment one!?

p.s.: i tried to open the file on 2 other pc s with msaccess 2003 and 2007...every time the same message appeares.... i have cpu 2.8Ghz, 512 of RAM on my Dell computer


Post your answer or comment

comments powered by Disqus
I have just created a new query in my db and it runs fine, also the query attached to this one works; however, when I get to the third query in this string I get the error message "system resource exceeded" and the query proceeds to open in SQL view. I can make changes to the query, but I don't know what to change mostly because I am not sure how much of this query I have to change for it to open properly. Can someone explain this message to me and maybe give some pointers?
Thanks!

Hi all,

Any idea why the following would cause error 3035, System Resource Exceeded? It's not joining more than 16 tables, and doesn't have any kind of where clause, so the KB article on this error doesn't apply. The db isn't more than 2GB, which is where this call has sometimes failed before. I previously ran this in Access 2003, and it was fine.


	Code:
	dbInput.Execute " ALTER TABLE " & inputTableName & _
                  " ADD COLUMN ROW_ID counter(1,1);", dbFailOnError



Hi,

I am getting a "system resource exceeded" message when running a macro in a database on one PC but the same database and macro do not generate the message on other PC's.

The PC that is giving the error message is a Win7 64bit PC with i7-2600k CPU, 16gb RAM using Office 2003.

Two other PC's both running Win7 64bit with Office 2003 and less RAM and less powerful CPU run the macro OK without any error message.

Is there some trick to configuring the BIOS or Access for use on a sandybridge motherboard/CPU that I am not aware of? All PC's are clean fresh installs of Windows and Office 2003 with all microsoft updates applied.

If it is not some configuration issue that I am not aware about then I am assuming I have a hardware issue from either the motherboard or RAM or CPU on the PC that yields the error message?

Any feedback most appreciated.


Rgds
Growlos

I've been creating a fairly complex (the equations and calculated fields anyway) Access 2007 database for the company I'm at right now for the past several weeks. I have run into numerous issues and have been able to overcome them all (with help from Access World Forums of course).

Here's the deal. I've broken Access. System Resources Exceeded error messages are popping up when I am just 3 fields away from being completely done with the database.

There are many, many calculated fields in different queries (about 50 calculated fields in about 30 queries).

I don't have too many tables (32 is the max) for a query. I don't have too many joins (16 is the max).

EDIT: It overloads when I have 14 joins (for Calc2 query). The max is 16 but I saw the asterisk just now and it says (from Microsoft) "*Maximum values might be lower if the query includes multivalued lookup fields." However, I have another query (Calc1 query) that has 19 joins and is fine. What's the deal?

I think I might be exceeding the Nested Queries limit (50 is the max). However, I'm not exactly what is meant by a nested query or how to go about getting around this nasty error message.

Here is a little more explanation of the database itself (if you need some more please feel free to ask). There are dozens of different types of fees that have to be calculated and eventually summed together in a Query (Fee Summary Total Query). However, this is not possible to do on it's own because there are too many characters (1,024 is the max) for the calculated field area itself. Thus, it is broken down into 2 shorter calculated fields in their own queries which are then added together (Calc1+Calc2=Fee Summary Total).

Does Access count the Nested Queries as part of the other 2 parts (Calc1 and Calc2) because Fee Summary Total Query is based off of both of them?

Hi all,

I connect to Access from an open source optimization language (GLPK) through ODBC, in order to read some data, which I use as model inputs in GLPK and then I make the GLPK writes outputs back to Access. I use Access 2007 and windows xp.This has been working pretty well, but now for a larger database, I'm able to read the data, but when it comes to writing the outputs back to Access, seems like I'm running into a memory problem; as I get the error: HY001:1:-1011:[Microsoft] [ODBC Microsoft Access Driver] System Resource Exceeded.

To give you an idea on how I connect through ODBC, I do the following: I have a .dsn file called d2.dsn which has the following lines written in it:

[ODBC]
DRIVER=Driver do Microsoft Access (*.mdb)
DBQ=./my_database.mdb

I call this file each time I need to read from or write to a certain table in the database my_database.mdb. An example is the following (that is part of my GLPK code):

table bom_jpvt IN "ODBC"
'FileDSN=.d2.dsn;READONLY=FALSE'
'TransformerTypeAssemblyPeriodBOMProducts' :
bom_set

I have a report that is fairly complecated, the report is based off of a Cross Tab Query, and has 3 subreports on the report with their information based off of a Cross Tab Query. Normally this report works fine when 1 - 3 pages is the length. The users then tried to run this report with criteria that equaled 18 pages of information. The report previewed the first and second page just fine, but when you try to go to the third page in print preview, the error "System Resource Exceeded" pops up with an OK button. Once the OK button is pressed, then the report closes.

I have removed the subreports, and if I remove the only subreport that is linked to each record (and leave the subreports that only show Totals information at the bottom), then all 18 pages will preview correctly.

I am just curious if anyone has run into this problem before.

Thank you for you time,

T.J.

Hi everyone,

I have a problem with system rosurce error.

The macro works perfectly - really quickly (about 30 - 40 second to check what components are needed to be produced).

The problem is that macro:
- increase size of the data base (from 70 MB to 210 MB)
- after executing the macro I offten see the "3035 error - system resource exceeded" - esspecialy when I want to open the table updated by macro.

I tap into the whole Internet but found nothing that could help me.

I already:
- changed in the registry the key: MaxLocksPerFile - to 500.000

I think there was no problem unitl I created second recordset (signallist) - before I did it I updated the signallist table via sql query but performance was really poor) - about 50 minutes (now 40 seconds but there's the error).

Here's my code. Can anyone help me and check it? Maybe I did something wrong?

Thank you very much.




	Code:
	 
Sub MainEngine()
DoCmd.SetWarnings False
 
TableName = "DlaPlanisty_Sorted"
'funcion "CzyTabIstnieje" checks if the table exists
If CzyTabIstnieje(TableName) Then
    DoCmd.Close acTable, TableName
    DoCmd.DeleteObject acTable, TableName
End If

QryToTbl = "SELECT * INTO DlaPlanisty_Sorted" & _
    " FROM (SELECT tbl_BOM.Key, tbl_OO_SumedUp.[Mtrl No], tbl_OO_SumedUp.[Order Number], format([MinOfRequest
Date],""yyyy-mm-dd"") AS [Request Date], tbl_OO_SumedUp.[SumOfQty Open] AS [Qty Open], tbl_BOM.Component, tbl_BOM.Level,
(([tbl_OO_SumedUp].[SumOfQty Open]*[tbl_BOM].[Req Component Qty])/1000) AS Demand, tbl_Routing.[Work Center], """" as Data,
"""" AS [Braknie]" & _
    " FROM (tbl_OO_SumedUp LEFT JOIN tbl_BOM ON tbl_OO_SumedUp.[Mtrl No] = tbl_BOM.Header) LEFT JOIN tbl_Routing ON
tbl_BOM.Component = tbl_Routing.Material" & _
    " WHERE (((tbl_BOM.Key) is not null and tbl_BOM.Component is not null))) AS DlaPlanisty" & _
    " ORDER BY DlaPlanisty.[Request Date], DlaPlanisty.[Order Number], DlaPlanisty.[Mtrl No], DlaPlanisty.Key;"

CurrentDb.Execute QryToTbl

strSQL = "UPDATE SignalList SET Demand = 0"
CurrentDb.Execute (strSQL)

Dim Streambase As DAO.Database
Dim RstPlanista As DAO.Recordset
Dim RstSignalList As DAO.Recordset
Dim StrSQL_Planista As String
Dim StrSQL_SignalList As String
Set Streambase = CurrentDb
Set RstPlanista = Streambase.OpenRecordset("DlaPlanisty_Sorted")
Set RstSignalList = Streambase.OpenRecordset("SignalList")
 
RstSignalList.Index = "PrimaryKey"
KeyPrev = 0
Do Until RstPlanista.EOF
    
KeyActual = RstPlanista!Key
    
    'Check if the component is chagned (current record vs previous record)
    If KeyPrev  KeyActual Then
        
        Material = RstPlanista!Component
        Demand = RstPlanista!Demand
                
        Level = RstPlanista!Level
        
        RstSignalList.Seek "=", Material
        If RstSignalList.NoMatch Then
           Stock = 0
           DemandPrev = 0
        Else
           Stock = RstSignalList!Stock
           DemandPrev = RstSignalList!Demand
        End If
       
        RstSignalList.Edit
        RstSignalList!Demand.Value = DemandPrev + Demand
        RstSignalList.Update
      
    End If
    
'check if production of component is needed
If ProdukcjaKonieczna = False Then
            
    If LevelBase < Level Then
            
    Else
        
        If Stock < DemandPrev + Demand Then
            'Production needed becuase there's no stock
                
            If Stock - DemandPrev < 0 Then
                PozostaloDoUzycia = 0
            Else
                PozostaloDoUzycia = Stock - DemandPrev
            End If
            
            IleBraknie = Demand - PozostaloDoUzycia
            
            RstPlanista.Edit
            RstPlanista!Braknie.Value = IleBraknie
            RstPlanista.Update
             
            ProdukcjaKonieczna = True
        Else
            'production not needed
            ProdukcjaKonieczna = False
            LevelBase = RstPlanista!Level
        End If
        
    End If
Else
    'Production needed
    
    
        If Stock < DemandPrev + Demand Then
            'roduction needed
                
            IleBraknie = DemandPrev + Demand - Stock
                
            RstPlanista.Edit
            RstPlanista!Braknie.Value = IleBraknie
            RstPlanista.Update
                
            ProdukcjaKonieczna = True
        Else
            'Production not needed
                
            ProdukcjaKonieczna = False
            LevelBase = RstPlanista!Level
        End If
End If
KeyPrev = KeyActual
RstPlanista.MoveNext
Loop
    RstPlanista.Close
    RstSignalList.Close
    Streambase.Close
    Set RstPlanista = Nothing
    Set RstSignalList = Nothing
    Set Streambase = Nothing

End Sub



Hi Guys

My client wants me to copy data stored in around 40 excelsheets to the access table. So I have done following steps:

1. Link all excelsheets in Access as follows:

File->Get External Data->Link tables

2. So after doing it I have 40 linked excelsheets in Tables tab.

3. Now I am going to write union query that will combine the data from all the tables.

Now when I write the sql union query then it gives me "System resources exceeded" message. So I tried to apply union to few tables first e.g 10-15 and it worked. I named this query Query1.

Now when I try to write union sql again to combine data from Query1 with rest of all the tables then again the same message appears "System resources exceeded"

Please guide me how can I combine all the tables together(They have the same format) as I want just one table that will store data from those 40 excelsheets.

Thanks a lot .

Hi,

First time on the forums - so please correct me if I'm posting in the wrong place etc.

I have constructed an Access 2010 database on windows 7 used by 5 people in a corporate environment. I split the database into a front end and back end to enable users to write the queries on their front end located on the desktop. This also enable easier updates to the back end tables.

This works extremely well in the office, but when people are working remotely over VPN, we start to get the much dreaded "system resource exceeded" on our front end databases.

I have broken all connections and loaded all of the tables locally on my front end DB, and my queries run without issue. This led me to believe the database structure is not the problem, it's the fact I have split the database for use over VPN.

I have a few ideas - none of them terrific for dealing with this issue. I am loathe to get corporate IT involved. Has anyone else encountered this problem?

Thanks in advance.

I have a nasty message that pops up on a union query I run. It states, "System Resources Exceeded". Until today I never encountered this message. The only changes I've made today is adding one column to each of the two union queries that feed into this one. I don't even include this column in the final query. What's happening?

Thanks for the help.

I have a database of 45 MB.
I am running a report from a form.
the report is based on a query, which is too long(about 14 unions with join also)

when i am running this on a P4 PC having 256MB RAM it is working.

but when I am running it on P3 PC having 256 MB RAM, is displays an err like System Resource exceeded.
nothing is running behind. only the database is opened.

why it is so?

I am building a SQL Selecet Statement in code to create query (Set QueryDef = CurrentDb.CreateQueryDef("qryTemp1", strSQL_Select & strSQL_From & strSQL_Where & strSQL_Group)) which has a column for every month in a time period specified by the user. The time period can be up to 10 years (120 columns). If the time period is around 4 years the query is created just fine, but take it to 5 years and I get "System Resources Exceeded". I have another similiar query creation situation where the length of the SQL Selecet Statement string is not quite as long per length of time period and as a result it does not fail until the time period goes beyond around 6 years. It seems like there is a limit too how many characters (it seems to be failing @ a very rough estimate of 125,000 characters) a SQL Select Statement can be. Is that true? Is there some other way to build this query for the user to view and/or export to excel?

Thanks for any ideas.
Scott

Hello, could someone please offer some suggestions as to how I can fix a problem I have with Access 2010?

I have written some code to pull data from tables on various web pages. The code iterates through several thousand pages and pulls data from each one. Everything works fine when I run this on Access 2010 under Windows 7, but on Windows XP (on a more powerful machine) I get an error saying "System Resource Exceeded".

There are no complex queries involved and no memory leakage that I can see.

I've tried installing the patch from http://support.microsoft.com/kb/2760394 but that doesn't seem to work.

Does anyone have any ideas about how I can fix this? Grateful for any suggestions.

Thanks

Hello,

I have a database with a big table (78 columns, 930 000 records) that I update using several query linking with other smaller tables.

I need to transfer this from one machine to another. While it is working well on the current machine, I have this Error 3035 : System resource exceeded on the new one, while it is bigger and newer than the old one :

Old machine :
Core 2 Duo E8400 @ 3Ghz
4 Gb RAM
300 Gb SATA RAID Volume (4 * 150 Gb in Raid 1+0)
Windows Vista Enterprise SP2 / 32 bits
Office 2007

New machine :
Xeon E5645 @ 2.4Ghz
24 Gb RAM
1.2 Tb SAS RAID Volume (4 * 600Gb in Raid 1+0)
Windows Server 2008 R2 SP1 / 64 bits
Office 2010

So I would expect the database to run even faster on the new one, but it just fails...

I've googled the error, and tried to increase the MaxLocksPerFile to 1 500 000 in the registry as suggested, with no success. No other type of answer from google

Anyone with another idea that I could check ?

Thanks !

Having a problem with my app. Everything runs fine, and system resources remain high and constant. However, whenever I manually enter any one of my Modules, the system resources on my computer (and those of my clients) drops dramatically. Often times the system resources will drop down to 10%. Eventually I have a fatal error and the user is forced to exit Access and re-enter.

I have double and triple checked all of my variables and recordsets. I have declared them properly and 'emptied' them at the end of the procedure. I have set and loaded my recordsets properly and R.Closed them at the end. I do not have any global variables in the app. What could be wrong?

Help would be appreciated.

Pat

Hi all,

We are using Win 7/Access 2010 (MS Office Professional Plus 2010) SP1. Our application is an accdb file (Switchboard main menu), with an accdb back-end which holds the data. It has evolved over 7 years from the mdb format to the accdb format.

We are seeing 3 major errors that have not occurred under Windows XP with Office 2003 or Office 2007, or Win 7 with Office 2007 (we never saw them before). We think it's an Office 2010 SP1 issue.

I tried decompiling the main app, re-compiling and then doing a Compact and Repair, with no success. I also eliminated the opening of the main menu upon Access load, but that did not help either. I attempted to use the Basic Windows format (no Aero effects) and other performance saving measures with no success.

The laptop has an i7 Core processor, 8 GIG RAM, and 400 GIG hard drive space available - plenty to run this application.

The errors are:

1- System resource exceeded
2- Not enough memory or disk space to undo this operation. Continue? (this occurs after the "System resource exceeded" error occurs)
3- Microsoft Access has stopped working. Windows can try to recover your information and restart the program. --> Restart the Program

For the "System resource exceeded" error, I found a hotfix which may fix the problem, but Microsoft's emailing service for the hotfix is currently down.

Any help is appreciated.

I am currently testng the following - This select works fine. (This is from the query objects of Access)


	Code:
	SELECT patent_akz AS q_akz, em.status AS q_status, em.decision_number AS q_decision_number, CVDATE(em.decision_date) AS
q_decision_date, em.work_around_ability AS q_work_around_ability, em.attractive AS q_attractive, em.used_by_third_party AS
q_used_by_third_party, em.total_eval AS q_total_eval, em.can_use AS q_can_use, em.eval_reason AS q_eval_reason,
em.use_inhouse AS q_use_inhouse, em.product AS q_product, em.decider AS q_decider, em.bkz_id AS q_bkz_id, em.bkz_questionaire
AS q_bkz_questionnaire, em.hierarchie_level_2 AS q_hierarchie_level_2, em.hierarchie_level_3 AS q_hierarchie_level_3,
em.hierarchie_level_4 AS q_hierarchie_level_4, em.hierarchie_level_5 AS q_hierarchie_level_5, em.piv AS q_piv, em.piv_name AS
q_piv_name, pik_coordinator AS q_pik_coordinator, pik_name AS q_pik_name, fal_pik AS q_fal_pik, fal_name AS q_fal_name, l_akz
AS q_l_akz, derwent_title AS q_derwent_title, derwent_abstract AS q_derwent_abstract, CVDATE(date_of_lapse) AS
q_date_of_lapse, countries AS q_countries, only_siemens_in_charge AS q_only_siemens_in_charge, em.type AS q_type,
em.em_akten_id AS q_em_akten_id, connect.srf_akten_id AS q_srf_akten_id
FROM [SELECT IIf(IsNull(connect.srf_akz), em.akz, connect.srf_akz) as patent_akz, em.status, em.decision_number,
em.decision_date, em.work_around_ability, em.attractive, em.used_by_third_party, em.total_eval, em.can_use, em.eval_reason,
em.use_inhouse, em.product, em.decider, em.bkz_id, em.bkz_questionaire, em.hierarchie_level_2, em.hierarchie_level_3,
em.hierarchie_level_4, em.hierarchie_level_5, em.piv, em.piv_name, '' as pik_coordinator, '' as pik_name, '' as fal_pik, ''
as fal_name, '' as l_akz, '' as derwent_title, '' as derwent_abstract, null as date_of_lapse, '' as countries, '0' as
only_siemens_in_charge, em.type, em.em_akten_id, connect.srf_akten_id
FROM (em
LEFT JOIN connect
ON em.em_akten_id = connect.em_akten_id)
UNION select srf.akz as patent_akz, srf.status, srf.decision_number, srf.decision_date, srf.work_around_ability,
srf.attractive, srf.used_by_third_party, srf.total_eval, srf.can_use, srf.eval_reason, srf.use_inhouse, srf.product,
srf.decider, srf.bkz_id, srf.bkz_questionaire, srf.hierarchie_level_2, srf.hierarchie_level_3, srf.hierarchie_level_4,
srf.hierarchie_level_5, srf.piv, srf.piv_name, srf.pik_coordinator, srf.pik_name, srf.fal_pik, srf.fal_name, srf.l_akz,
srf.derwent_title, srf.derwent_abstract, srf.date_of_lapse, srf.countries, srf.only_siemens_in_charge, srf.type, null,
srf.srf_akten_id
FROM srf]. AS [%$##@_Alias];

Now I need to add this result to a linked table which is empty. The back end is SQL Server.

So to the above I add

	Code:
	INSERT INTO tbl_questionnaire

This give me the error message. I have tried it with DAO in code as well but I still get an error.

Any thoughts why?

I have tried this


	Code:
	Application.DBEngine.SetOption dbMaxLocksPerFile, 1000000



Hi there,

I have a table that has

Category SubCategoy Value


With my query I want to select for each row the minimum vale in the category:

I use the following query, as some categoriy combinations might not exist..

SELECT PrePreReport.Category, PrePreReport.SubCategory,
(SELECT MIN(PrePreReport.Value) FROM PrePreReport WHERE Category=PrePreReport.Category AND
SubCategory =PrePreReport.SubCategory AND
PrePreReport.Value 0 AND

EXISTS(
SELECT MIN(PrePreReport.Value) FROM PrePreReport WHERE Category=PrePreReport.Category AND
SubCategory =PrePreReport.SubCategory AND
PrePreReport.Value 0 AND )

AS MinValue
FROM PrePreReport;

Running the query I got the "System ressource exceeded" error....any idea?

I'm working on that issue since 4 hours and no solution in place...
Is the query inefficient?

Thanks a lot!

I appreciate a lot your help!

I just found something interesting.

I have a small db open. One form, viewing info.

I notice in Windows Task Manager that MSACCESS.EXE is taking up between 70 to 97% sometimes 100% of systems resources.

How could this be?

Cheers!
QTNM = )

hi.
i finally finished - with the help of the forum's own great pbaldy - the query that i was going to use as the data feed for my Excel 2007 pivot table.

everything seemed to be working initially - i insert the pivot table, find my query in the 'External Connection...' dialog, the "Reading Data:" counter is counting, the data is flowing in, i am about to open a beer, when - it stops! at 7000 something. out of only about 10000!
and displays the following messages -

[Microsoft][ODBC Microsoft Access Driver] System resource exceeded.
and then
Problems obtaining data.

what is it? can anything be done to improve this exceeded 'resource' a bit?..
thanks,
l

Hi ppl, i have combined a few queries using an union but when i tried to run it, there is an error message "System resources exceeded"

When i researched online, it seems the union was badly designed and as a result, exceeded the character limit.

ive read some proposed solutions such as "not prefixing field names with [table name] when the
field name is not used in more than one table in the FROM
clause" and "excluding field names of other select stms" at http://us.generation-nt.com/answer/r...201384272.html

however, i dont quite get the solutions and ended up having error when i tried some trial and error methods. Can anyone advise on how to optimise an union query so its not too lengthy and so it will run properly.

Below is part of the SQL of the union - as you can see, too much details.


SELECT "Scenario A" AS Expr1, [Scenario A].Source, [Scenario A].Label, [Scenario A].Period, [Scenario A].Category, [Scenario A].Origin_Region, [Scenario A].AP_Country, [Scenario A].Month, [Scenario A].Month1, [Scenario A].Quarter, [Scenario A].YTD, [Scenario A].Sector, [Scenario A].Bleed, [Scenario A].[Route Impact], [Scenario A].Org, [Scenario A].Dest, [Scenario A].Dest_Region, [Scenario A].Currency, [Scenario A].Weight, [Scenario A].[Freight (LCCY)], [Scenario A].[FSC (LCCY))], [Scenario A].[FSC Adj (LCCY)], [Scenario A].[Handling (LCCY)], [Scenario A].[Ground Handling (LCCY)], [Scenario A].[Security (LCCY)], [Scenario A].[Ticket Costs (LCCY)], [Scenario A].[Tax (LCCY)], [Scenario A].[Others (LCCY)], [Scenario A].[Total CAL Costs (LCCY)], [Scenario A].[Freight (LCCY) Cpk], [Scenario A].[FSC (LCCY)) Cpk], [Scenario A].[FSC Adj (LCCY) Cpk], [Scenario A].[Handling (LCCY) Cpk], [Scenario A].[Ground Handling (LCCY) Cpk], [Scenario A].[Security (LCCY) Cpk], [Scenario A].[Ticket Costs (LCCY) Cpk], [Scenario A].[Tax (LCCY) Cpk], [Scenario A].[Others (LCCY) Cpk], [Scenario A].[2011AOPR], [Scenario A].[Freight @11 AOPR], [Scenario A].[FSC @11 AOPR], [Scenario A].[FSC Adj@11 AOPR], [Scenario A].[Handling @ 11 AOPR], [Scenario A].[Ground Handling @11 AOPR], [Scenario A].[Security @11 AOPR], [Scenario A].[Ticket Costs @11 AOPR], [Scenario A].[Tax @11 AOPR], [Scenario A].[Others @11 AOPR], [Scenario A].[Total CAL Costs @11 AOPR], [Scenario A].[Freight @11 AOPR Cpk], [Scenario A].[FSC @11 AOPR Cpk], [Scenario A].[FSC Adj@11 AOPR Cpk], [Scenario A].[Handling @ 11 AOPR Cpk], [Scenario A].[Ground Handling @11 AOPR Cpk], [Scenario A].[Security @11 AOPR Cpk], [Scenario A].[Ticket Costs @11 AOPR Cpk], [Scenario A].[Tax @11 AOPR Cpk], [Scenario A].[Others @11 AOPR Cpk], [Scenario A].[2012AOPR], [Scenario A].[Freight @12AOPR], [Scenario A].[FSC @12AOPR], [Scenario A].[FSC Adj @12AOPR], [Scenario A].[Handling @12AOPR], [Scenario A].[Ground Handling @12AOPR], [Scenario A].[Security @12AOPR], [Scenario A].[Ticket Costs @12AOPR], [Scenario A].[Tax @12AOPR], [Scenario A].[Others @12AOPR], [Scenario A].[Total CAL Costs @12AOPR], [Scenario A].[Freight @12AOPR Cpk], [Scenario A].[FSC @12AOPR Cpk], [Scenario A].[FSC Adj @12AOPR Cpk], [Scenario A].[Handling @12AOPR Cpk], [Scenario A].[Ground Handling @12AOPR Cpk], [Scenario A].[Security @12AOPR Cpk], [Scenario A].[Ticket Costs @12AOPR Cpk], [Scenario A].[Tax @12AOPR Cpk], [Scenario A].[Others @12AOPR Cpk], [Scenario A].[Mvt related to], [Scenario A].[ORIGINAL SECTOR], [Scenario A].LOOKUP, [Scenario A].FCT, [Scenario A].[ORG-], [Scenario A].[DEST-], [Scenario A].TYPE
FROM [Scenario A]
GROUP BY [Scenario A].Source, [Scenario A].Label, [Scenario A].Period, [Scenario A].Category, [Scenario A].Origin_Region, [Scenario A].AP_Country, [Scenario A].Month, [Scenario A].Month1, [Scenario A].Quarter, [Scenario A].YTD, [Scenario A].Sector, [Scenario A].Bleed, [Scenario A].[Route Impact], [Scenario A].Org, [Scenario A].Dest, [Scenario A].Dest_Region, [Scenario A].Currency, [Scenario A].Weight, [Scenario A].[Freight (LCCY)], [Scenario A].[FSC (LCCY))], [Scenario A].[FSC Adj (LCCY)], [Scenario A].[Handling (LCCY)], [Scenario A].[Ground Handling (LCCY)], [Scenario A].[Security (LCCY)], [Scenario A].[Ticket Costs (LCCY)], [Scenario A].[Tax (LCCY)], [Scenario A].[Others (LCCY)], [Scenario A].[Total CAL Costs (LCCY)], [Scenario A].[Freight (LCCY) Cpk], [Scenario A].[FSC (LCCY)) Cpk], [Scenario A].[FSC Adj (LCCY) Cpk], [Scenario A].[Handling (LCCY) Cpk], [Scenario A].[Ground Handling (LCCY) Cpk], [Scenario A].[Security (LCCY) Cpk], [Scenario A].[Ticket Costs (LCCY) Cpk], [Scenario A].[Tax (LCCY) Cpk], [Scenario A].[Others (LCCY) Cpk], [Scenario A].[2011AOPR], [Scenario A].[Freight @11 AOPR], [Scenario A].[FSC @11 AOPR], [Scenario A].[FSC Adj@11 AOPR], [Scenario A].[Handling @ 11 AOPR], [Scenario A].[Ground Handling @11 AOPR], [Scenario A].[Security @11 AOPR], [Scenario A].[Ticket Costs @11 AOPR], [Scenario A].[Tax @11 AOPR], [Scenario A].[Others @11 AOPR], [Scenario A].[Total CAL Costs @11 AOPR], [Scenario A].[Freight @11 AOPR Cpk], [Scenario A].[FSC @11 AOPR Cpk], [Scenario A].[FSC Adj@11 AOPR Cpk], [Scenario A].[Handling @ 11 AOPR Cpk], [Scenario A].[Ground Handling @11 AOPR Cpk], [Scenario A].[Security @11 AOPR Cpk], [Scenario A].[Ticket Costs @11 AOPR Cpk], [Scenario A].[Tax @11 AOPR Cpk], [Scenario A].[Others @11 AOPR Cpk], [Scenario A].[2012AOPR], [Scenario A].[Freight @12AOPR], [Scenario A].[FSC @12AOPR], [Scenario A].[FSC Adj @12AOPR], [Scenario A].[Handling @12AOPR], [Scenario A].[Ground Handling @12AOPR], [Scenario A].[Security @12AOPR], [Scenario A].[Ticket Costs @12AOPR], [Scenario A].[Tax @12AOPR], [Scenario A].[Others @12AOPR], [Scenario A].[Total CAL Costs @12AOPR], [Scenario A].[Freight @12AOPR Cpk], [Scenario A].[FSC @12AOPR Cpk], [Scenario A].[FSC Adj @12AOPR Cpk], [Scenario A].[Handling @12AOPR Cpk], [Scenario A].[Ground Handling @12AOPR Cpk], [Scenario A].[Security @12AOPR Cpk], [Scenario A].[Ticket Costs @12AOPR Cpk], [Scenario A].[Tax @12AOPR Cpk], [Scenario A].[Others @12AOPR Cpk], [Scenario A].[Mvt related to], [Scenario A].[ORIGINAL SECTOR], [Scenario A].LOOKUP, [Scenario A].FCT, [Scenario A].[ORG-], [Scenario A].[DEST-], [Scenario A].TYPE
UNION SELECT "Scenario BB" AS Expr1, [Scenario BB].Source, [Scenario BB].Label, [Scenario BB].Period, [Scenario BB].Category, [Scenario BB].Origin_Region, [Scenario BB].AP_Country, [Scenario BB].Month, [Scenario BB].Month1, [Scenario BB].Quarter, [Scenario BB].YTD, [Scenario BB].Sector, [Scenario BB].Bleed, [Scenario BB].[Route Impact], [Scenario BB].Org, [Scenario BB].Dest, [Scenario BB].Dest_Region, [Scenario BB].Currency, [Scenario BB].Weight, [Scenario BB].[Freight (LCCY)], [Scenario BB].[FSC (LCCY))], [Scenario BB].[FSC Adj (LCCY)], [Scenario BB].[Handling (LCCY)], [Scenario BB].[Ground Handling (LCCY)], [Scenario BB].[Security (LCCY)], [Scenario BB].[Ticket Costs (LCCY)], [Scenario BB].[Tax (LCCY)], [Scenario BB].[Others (LCCY)], [Scenario BB].[Total CAL Costs (LCCY)], [Scenario BB].[Freight (LCCY) Cpk], [Scenario BB].[FSC (LCCY)) Cpk], [Scenario BB].[FSC Adj (LCCY) Cpk], [Scenario BB].[Handling (LCCY) Cpk], [Scenario BB].[Ground Handling (LCCY) Cpk], [Scenario BB].[Security (LCCY) Cpk], [Scenario BB].[Ticket Costs (LCCY) Cpk], [Scenario BB].[Tax (LCCY) Cpk], [Scenario BB].[Others (LCCY) Cpk], [Scenario BB].[2011AOPR], [Scenario BB].[Freight @11 AOPR], [Scenario BB].[FSC @11 AOPR], [Scenario BB].[FSC Adj@11 AOPR], [Scenario BB].[Handling @ 11 AOPR], [Scenario BB].[Ground Handling @11 AOPR], [Scenario BB].[Security @11 AOPR], [Scenario BB].[Ticket Costs @11 AOPR], [Scenario BB].[Tax @11 AOPR], [Scenario BB].[Others @11 AOPR], [Scenario BB].[Total CAL Costs @11 AOPR], [Scenario BB].[Freight @11 AOPR Cpk], [Scenario BB].[FSC @11 AOPR Cpk], [Scenario BB].[FSC Adj@11 AOPR Cpk], [Scenario BB].[Handling @ 11 AOPR Cpk], [Scenario BB].[Ground Handling @11 AOPR Cpk], [Scenario BB].[Security @11 AOPR Cpk], [Scenario BB].[Ticket Costs @11 AOPR Cpk], [Scenario BB].[Tax @11 AOPR Cpk], [Scenario BB].[Others @11 AOPR Cpk], [Scenario BB].[2012AOPR], [Scenario BB].[Freight @12AOPR], [Scenario BB].[FSC @12AOPR], [Scenario BB].[FSC Adj @12AOPR], [Scenario BB].[Handling @12AOPR], [Scenario BB].[Ground Handling @12AOPR], [Scenario BB].[Security @12AOPR], [Scenario BB].[Ticket Costs @12AOPR], [Scenario BB].[Tax @12AOPR], [Scenario BB].[Others @12AOPR], [Scenario BB].[Total CAL Costs @12AOPR], [Scenario BB].[Freight @12AOPR Cpk], [Scenario BB].[FSC @12AOPR Cpk], [Scenario BB].[FSC Adj @12AOPR Cpk], [Scenario BB].[Handling @12AOPR Cpk], [Scenario BB].[Ground Handling @12AOPR Cpk], [Scenario BB].[Security @12AOPR Cpk], [Scenario BB].[Ticket Costs @12AOPR Cpk], [Scenario BB].[Tax @12AOPR Cpk], [Scenario BB].[Others @12AOPR Cpk], [Scenario BB].[Mvt related to], [Scenario BB].[ORIGINAL SECTOR], [Scenario BB].LOOKUP, [Scenario BB].FCT, [Scenario BB].[ORG-], [Scenario BB].[DEST-], [Scenario BB].TYPE
FROM [Scenario BB]
GROUP BY [Scenario BB].Source, [Scenario BB].Label, [Scenario BB].Period, [Scenario BB].Category, [Scenario BB].Origin_Region, [Scenario BB].AP_Country, [Scenario BB].Month, [Scenario BB].Month1, [Scenario BB].Quarter, [Scenario BB].YTD, [Scenario BB].Sector, [Scenario BB].Bleed, [Scenario BB].[Route Impact], [Scenario BB].Org, [Scenario BB].Dest, [Scenario BB].Dest_Region, [Scenario BB].Currency, [Scenario BB].Weight, [Scenario BB].[Freight (LCCY)], [Scenario BB].[FSC (LCCY))], [Scenario BB].[FSC Adj (LCCY)], [Scenario BB].[Handling (LCCY)], [Scenario BB].[Ground Handling (LCCY)], [Scenario BB].[Security (LCCY)], [Scenario BB].[Ticket Costs (LCCY)], [Scenario BB].[Tax (LCCY)], [Scenario BB].[Others (LCCY)], [Scenario BB].[Total CAL Costs (LCCY)], [Scenario BB].[Freight (LCCY) Cpk], [Scenario BB].[FSC (LCCY)) Cpk], [Scenario BB].[FSC Adj (LCCY) Cpk], [Scenario BB].[Handling (LCCY) Cpk], [Scenario BB].[Ground Handling (LCCY) Cpk], [Scenario BB].[Security (LCCY) Cpk], [Scenario BB].[Ticket Costs (LCCY) Cpk], [Scenario BB].[Tax (LCCY) Cpk], [Scenario BB].[Others (LCCY) Cpk], [Scenario BB].[2011AOPR], [Scenario BB].[Freight @11 AOPR], [Scenario BB].[FSC @11 AOPR], [Scenario BB].[FSC Adj@11 AOPR], [Scenario BB].[Handling @ 11 AOPR], [Scenario BB].[Ground Handling @11 AOPR], [Scenario BB].[Security @11 AOPR], [Scenario BB].[Ticket Costs @11 AOPR], [Scenario BB].[Tax @11 AOPR], [Scenario BB].[Others @11 AOPR], [Scenario BB].[Total CAL Costs @11 AOPR], [Scenario BB].[Freight @11 AOPR Cpk], [Scenario BB].[FSC @11 AOPR Cpk], [Scenario BB].[FSC Adj@11 AOPR Cpk], [Scenario BB].[Handling @ 11 AOPR Cpk], [Scenario BB].[Ground Handling @11 AOPR Cpk], [Scenario BB].[Security @11 AOPR Cpk], [Scenario BB].[Ticket Costs @11 AOPR Cpk], [Scenario BB].[Tax @11 AOPR Cpk], [Scenario BB].[Others @11 AOPR Cpk], [Scenario BB].[2012AOPR], [Scenario BB].[Freight @12AOPR], [Scenario BB].[FSC @12AOPR], [Scenario BB].[FSC Adj @12AOPR], [Scenario BB].[Handling @12AOPR], [Scenario BB].[Ground Handling @12AOPR], [Scenario BB].[Security @12AOPR], [Scenario BB].[Ticket Costs @12AOPR], [Scenario BB].[Tax @12AOPR], [Scenario BB].[Others @12AOPR], [Scenario BB].[Total CAL Costs @12AOPR], [Scenario BB].[Freight @12AOPR Cpk], [Scenario BB].[FSC @12AOPR Cpk], [Scenario BB].[FSC Adj @12AOPR Cpk], [Scenario BB].[Handling @12AOPR Cpk], [Scenario BB].[Ground Handling @12AOPR Cpk], [Scenario BB].[Security @12AOPR Cpk], [Scenario BB].[Ticket Costs @12AOPR Cpk], [Scenario BB].[Tax @12AOPR Cpk], [Scenario BB].[Others @12AOPR Cpk], [Scenario BB].[Mvt related to], [Scenario BB].[ORIGINAL SECTOR], [Scenario BB].LOOKUP, [Scenario BB].FCT, [Scenario BB].[ORG-], [Scenario BB].[DEST-], [Scenario BB].TYPE
HAVING ((([Scenario BB].Source)="2012AOP"))
UNION ....................

Im using a series of expressions containing IIF statements to carry forward (itinerate) balances. I get this message and sometimes "System resource exceeded". I'm not using nested queries nor, do I think, too many fields. Access Specifications don't mention any such limitation. Im using Access 2000 on Win98 and a fairly fast comp. with plenty of memory.

Forgive me, but this would be my first time ever creating a database from a flat file. The flat file that is being sliced and diced many ways to get values needed for payroll and assignment tracking. The way I am handling it now is that I am creating chain queries and am realizing that the deeper I go with this, the longer it takes to run a query. Do you think I need to start looking into temp tables when I get a system resource exceeded error?

I am guessing that I should create a query, then transfering the data results to a table. Then create a query from that table to later export into another table and so on. Is this possible and what is the correct name for this process? I am also think that a macro can trigger the chain. All help is greatly appreciated.

Here is a tool that can help in avoiding situations where you delivered your database and two months later the client is complaining that it takes FOREVER to get anything done. Is Access really that bad, or is it your code?

This is a sample database developed by Patrick Crews and Giuseppe Maxia and provides a combination of a large base of data (approximately 220 MB) spread over six separate tables and consisting of 4 million records in total adapted into an Access database.

The database can be useful in assessing answers to various questions such as
Which general classes of SQL statement (e.g. Joins or subqueries) will perform better? How does Access use index and how efficient it is under what conditions? How well will it perform in a networked environment?

...among other things.

While it may always not be the answer to every questions, it can be useful in answering questions that is best answered with a dataset consisting more than ten rows worth of data and understanding how Access manage such load.

The schema is given as below:


It should be noted that authors left up to the users to modify schema and find the inconsistencies in the data. If you do elect to modify the schema note that the best way going at it would be to create a new blank table, define relationships and indexes to the blank table first then run an append query moving the data from old table to new table. Altering table in design view may be possible but in my experience, usually takes up to 20 minutes before quitting with a "System resource exceeded." error which is frankly a big time-waster.

The database is licensed under Creative Commons Attribution-Share Alike 3.0 Unported License, and is free to distribute & share provided the conditions given in the link.

Adam Taylor was kind enough to host the files at his United Kingdom site:
Driffield Computing.

Employees2000
Employees2003
Employees2007

Bob Larson was kind enough to host the file zipped into 70 MB at his Western USA site: btabdevelopment.

Employees2000
Employees2003
Employees2007

George Hepworth also was more than happy to host the files as well at his Western USA site: GPC Data site:

Employees2000
Employees2003
Employees2007


Not finding an answer? Try a Google search.