Wtf? Access is severly angering me!

[dantheram]

Everytime i try to run queries that i have successfully executed in the past i get "system resource exceeded" error message.

What is causing this?

 

[Joe4]

What is the size of your database?
You may want to try running a "Compact and Repair" to reduce the size.

It could also be a limitation of your computer resources, and not Access.

 

[dantheram]

77.9mb. Could it be that the data is all in linked tables from excel and any query must use a union to join these excel sheets prior to executing?

 

[Joe4]

Could it be that the data is all in linked tables from excel and any query must use a union to join these excel sheets prior to executing?

Definitely. Union queries aren't the most efficient things.

One option is to do the following at run-time:
Instead of using Union Queries, have Append Queries which write the data from each linked table to a "Temporay" table, and get your final results off of that. Just have your process delete all records in that temporary table each time at the beginning. If you can add indexes to that table, it could make it more efficient too.

 

[dk]

Just to add my 2 cents worth. In my current position I inherited a database that had a number of linked tables that were linked to Excel worksheets. One of the tables was a customer mapping that mapped customer number to a customer group/location/etc. Given that there were only about 20,000 records in that Excel file the performance was awful - any query that included that table took anything from 30 seconds to 5 minutes to run and I knew that wasn't right. After a bit of playing around I got it so that the customer table was converted to an Access table with appropriate indexes added and the performance improvement was MASSIVE - 10 to 100 times faster for some queries.

So the moral of my story is - don't link to Excel worksheets - use Access tables instead. If you have a need to still maintain an Excel version of your tables then have Access do this for you - maintain the table in Access through a form - once complete you can open the Excel file and refresh with the latest data (all automated obviously). That's what I've ended up doing here and it works almost flawlessly (only problem occurs if someone else has the Excel file open in read-write mode when I try to update).

HTH
DK

 

[dantheram]

The linked tables (6 in total) contain a years worth of data which often stretches to some 100K rows. Would having this stored within the database make it too big, and thus, cause more problems?

 

[dk]

Well having 100k rows+ in an access table is going to be more efficient than linking to an excel file containing the same data. The problem is that there is an extra layer of communication (via ODBC) when you link to excel and that will introduce extra time to any query. I've got a database that works fine with tables of 1.5m records so 100,000 should be ok.

 

[Fazza]

Another two cents' worth: regarding the Excel file being open in read-write mode. I had something rather similar once. Basically, VBA in the Excel file created on file closure a copy of the required data in a dedicated subdirectory: then the Access file queried this data file in the sub-directory. No one ever went into the sub-directory, so I could rely on the file being available for querying.