Error 3183 (Exceeding the 2GB temp file space)

[sbrad86]

I am a beginner in SQL coding in Access and I have been trying to run a process but it keeps giving me a Run-time error 3183. In the debug, it keeps bringing me to the below query:

DoCmd.RunSQL _
    "UPDATE PotintialTOPNiin_tbl " & _
    "SET [80Count] = [80Count] + 1 " & _
    "WHERE niin IN (" & _
        "SELECT d.niin " & _
        "FROM (" & _
            "SELECT CurrentProjection_tbl.niin, " & _
                "SUM(a.ProjectedSavings)/" & _
                    CurrentProjection & _
                " AS Contribution " & _
            "FROM CurrentProjection_tbl " & _
            "LEFT JOIN (" & _
                "SELECT b.niin, c.ProjectedSavings " & _
                "FROM CurrentProjection_tbl AS b " & _
                "LEFT JOIN CurrentProjection_tbl AS c " & _
                "ON b.ProjectedSavings < c.ProjectedSavings) AS a " & _
            "ON CurrentProjection_tbl.niin = a.niin " & _
            "GROUP BY CurrentProjection_tbl.niin) AS d " & _
        "WHERE d.Contribution <= " & Opportunity & _
        " OR d.Contribution IS Null);"

And can't seem to find out what the problem is. This database was already preexisting. Someone please help

[twinnyfo]

sbrd86,

Welcome to Bytes!

Your problem has nothing to do with your Query. It has to do with your file size, which has reached the MS Access maximum of 2 GB (it will not go one byte--not even a bit--over 2 GB).

FIRST MAKE A BACKUP OF YOUR DATABASE!

MAKE SURE YOU HAVE MADE A BACKUP OF YOUR DATABASE!

Before you try running your query again, try compacting and repairing your DB. This is one of the DB options under your File Menu. Because of its size, this may take a few minutes to compact. If, after you compact it, it is still at the 2 GB max limit, you will have to split your DB into smaller parts. This can be a pain, but will be necessary in order for you to do anything with it.

Hope this hepps!

[twinnyfo]

Also, as a side note, if you are going to use DoCmd.RunSQL, it is always wise to use a String variable, assign your SQL string to that variable and then execute the SQL via the string variable. This allows you to troubleshoot your string (Debug.Print, etc.) should you have any issues.

Most people offer me a penny for my thoughts, but I always throw my two cents in....

[NeoPa]

Please check out Before Posting (VBA or SQL) Code.