Access Crashing without and error message

[tonygg]

Hi. I might not be posting in the right forum section but I am not sure what the issue and I am hoping for some help. I am using 64 bit version ACCESS 2013 on a pc with 8gb memory however I think it might be access running out of memory or something similar? Please bear with me as I explain.



I have a series of nested queries that that build up a profitability analysis for products ordered. The nesting is initially 3 levels and combines results across 2 sets of queries to build up a profitability by product (including a union query). This data is then grouped in another query by product type before being displayed in a form (4 levels of nested queries in total). I actually want to see the grouping in 2 different ways (by product line and by product type). To get the second grouping I re-run the nested queries with a different grouping and then display both sets of results as sub form in a main form.

To give a size of the data set the base query at the bottom pulls back 18,000 rows. The top level query displays between 10 rows (products grouped by type) and 200 rows ( products grouped by line)

With a fixed data set I am finding is some times the form loads fine and at least 50% of the time using my fixed data set it causes Access to stop and say it needs to restart. No other explanation is given.

I do nothing specific to clear the data after running each nested query and I was wondering if it is just too big a data set for access to handle?

Is there any best practices for using nested queries or should I be creating a temporary table ( don't know how to do this) with the results so it can be grouped in the various ways I need to see the data?

Any thoughts would be gratefully received.

Thanks

Tony

[ItsMe]

Avoid using SELECT DISTINCT and GROUP BY on large datasets. Do not use SELECT * and apply WHERE clauses in your sub-queries. Use parenthesis to tell Access which statements within your WHERE clause have precedence.

[CJ_London]

I was wondering if it is just too big a data set for access to handle?

Access will comfortably handle millions of rows - so providing your data is OK it should not be a problem.

this comment

With a fixed data set I am finding is some times the form loads fine

indicates you have problems in your data or the way it is handled. For example a frequent reason for failure is having nulls in your data which are not handled correctly - for example this iif statement to avoid nulls will fail because the whole statement is evaluated.

myValue:iif(somefield is not null, somefield,0)

[tonygg]

Ajax. Thank you.

I think the nulls might be an issue as I do this check for Null on some fields using iif. Just so I understand is there a reason it sometimes works and sometimes does not? I am finding with my fixed data set the same query works 50- 60%. If you run it twice in a row it increases the chance of the failure. Is there a workaround you could suggest?

Also just in case it is relevant I do also select distinct in VBA to pull back all the product types and product lines into two combobox (to do some filtering on the form) - please see code below. They access the same join query. Oddly (again with same fixed data set) I am sometimes getting an error (vba goes to debug) on the second of the 2 queries (Set Me.ComboProductSelection.Recordset). The error is that there is an unequal number of columns in the join. This is odd it is the same query as for the first combobox:

SQLOrderString = "SELECT DISTINCT [TonyOrderHistorybyProductOrderValueAnalysis].[FirstOfFirstOfProductReportingType] FROM [TonyOrderHistorybyProductOrderValueAnalysis]" & _
     " WHERE [TonyOrderHistorybyProductOrderValueAnalysis].[StockRemaining] > 0" & _
     " ORDER BY [TonyOrderHistorybyProductOrderValueAnalysis].[FirstOfFirstOfProductReportingType];"


  Set Me.ComboProductType.Recordset = CurrentDb.OpenRecordset(SQLOrderString)
     
  Me!ComboProductType = Me!ComboProductType.ItemData(0)
    
  SQLOrderString = "SELECT DISTINCT [TonyOrderHistorybyProductOrderValueAnalysis].[FirstOfProduct Section Name] FROM [TonyOrderHistorybyProductOrderValueAnalysis]" & _
     " WHERE [TonyOrderHistorybyProductOrderValueAnalysis].[StockRemaining] > 0" & _
     " ORDER BY [TonyOrderHistorybyProductOrderValueAnalysis].[FirstOfProduct Section Name];"


  Set Me.ComboProductSelection.Recordset = CurrentDb.OpenRecordset(SQLOrderString)
     
  Me!ComboProductSelection = Me!ComboProductSelection.ItemData(0)

[CJ_London]

As far as I am concerned there is no issue with using select distinct if required - but it will be slower than a select. In the two sql strings provided it looks like it might be appropriate to use - no way for me to know without knowing the data.

Just so I understand is there a reason it sometimes works and sometimes does not?

sometimes your data has a null in it, sometimes it does not. another issue can be a divide by zero error - e.g.

newvalue: iif(somevalue is null, 0, 100/somevalue) will fail if somevalue is 0

If you data 'fails' search for nulls and zeros where not are expected in the data. Alternatively modify your code to properly protect against them (using is null or nz/isnull functions)

Having seen the length of your table and field names, you may be approaching access limits - 64 characters for each - would be a consistent problem but if you are building up names like this you will hit the limit

[tonygg]

Hi Ajax.

Thanks again. Sorry I was not clear. The data set is fixed and the same each time I run it so it should not change the evaluation of the expressions. On one run it will not crash but on another it will. Is there another way of doing the iif statements to check for null and zeros. Some of my calculations are to create % so it I need to check for zero to ensure I don't divide by zero?

Thanks

Tony

[CJ_London]

The data set is fixed and the same each time I run it

if by this you mean it is exactly the same data, why would you run it more than once?

Is there another way of doing the iif statements to check for null and zeros

use the nz function - if you don't know it, google it for a full explanation

so it I need to check for zero to ensure I don't divide by zero

assuming you could have a zero, yes

[ssanfu]

Another thought......

Would have to see all of your code, but are you properly closing recordsets and destroying objects? Not doing so could use up memory.
Not the best way to describe it, but from what I understand, it has to do with leaving a recordset instantiated in memory with no way to access the recordset. therefore, the available memory is reduced. Requires a reboot to clear the memory.

The rule I learned is "If you open it, close it. If you create it, destroy it."

So here,

Dim dB as DAO.Database
Dim rs as DAO.Recordset

Set dB = CurrentDB
Set rs = db.OpenRecordset ("Table1")

at the end of the routine, you should have

rs.Close    ' you opened the recordset, so close it
Set rs = Nothing   ' you created the object, so destroy it 

Set dB = Nothing  'You did not open it , so don't close it. You DID create it, so destroy it

I always try to close a recordset as soon as I can.
But do not destroy the recordset object until the end of the routine.