MS Access keeps crashing when opening a query in Design View

Question

I have a query that keeps crashing the entire DB. It runs fine (and fast) and I can open it in SQL view, but when I click Design View I get the error message that says something like "MS Access has stopped working. Windows is checking for a solution."

SQL Code:

SELECT 

    dbo_job.job_date AS Job_Date, 
    dbo_job.item AS FG, 
    dbo_job.job AS Job, 
    dbo_job.suffix AS Suffix, 
    dbo_job.qty_complete AS SL_Production, 
    "" AS SAP_Production, 
    Nz(ibl.sumqty,0) AS SL_Inventory, 
    "" AS SAP_Inventory, 
    dbo_job.Uf_CustPo AS PO, 
    dbo_job.Uf_CustPOLine AS PO_Line

FROM 

    (dbo_job 
    LEFT JOIN 
                (SELECT

                          i2.item
                        , SUM(i2.qty_on_hand) AS sumqty
                        , i2.whse

                    FROM

                        dbo_item_by_location_lp_all AS i2

                    WHERE

                        i2.whse = [Forms]![MainForm]![Combo367]

                    GROUP BY

                          i2.item
                        , i2.whse

                )  AS ibl ON dbo_job.item = ibl.item) INNER JOIN dbo_job_sch ON (dbo_job.suffix = dbo_job_sch.suffix) AND (dbo_job.job = dbo_job_sch.job)

WHERE 

        dbo_job.job Like "H*"
    AND dbo_job.job_date= (Date() - (DatePart("w", Date(), 2, 1)-1))        
    AND dbo_job.item Not Like "indirect"
    AND dbo_job.whse = [Forms]![MainForm]![Combo367]

ORDER BY 

    dbo_job.Uf_Production_Line, 
    dbo_job_sch.priority;

score 0 · Answer 1 · answered Aug 31 '16 at 12:21

There's metadata for each query in Access that records things like how wide the columns are and so on. If that metadata becomes corrupt, you can see the behavior that you see. Copy the SQL text, make a new query in SQL view, and paste the SQL text into the new query. Then try looking at it in design view.

MS Access keeps crashing when opening a query in Design View

1 Answers1