423,850 Members | 1,661 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,850 IT Pros & Developers. It's quick & easy.

Error 3183 (Exceeding the 2GB temp file space)

P: 1
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:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL _
  2.     "UPDATE PotintialTOPNiin_tbl " & _
  3.     "SET [80Count] = [80Count] + 1 " & _
  4.     "WHERE niin IN (" & _
  5.         "SELECT d.niin " & _
  6.         "FROM (" & _
  7.             "SELECT CurrentProjection_tbl.niin, " & _
  8.                 "SUM(a.ProjectedSavings)/" & _
  9.                     CurrentProjection & _
  10.                 " AS Contribution " & _
  11.             "FROM CurrentProjection_tbl " & _
  12.             "LEFT JOIN (" & _
  13.                 "SELECT b.niin, c.ProjectedSavings " & _
  14.                 "FROM CurrentProjection_tbl AS b " & _
  15.                 "LEFT JOIN CurrentProjection_tbl AS c " & _
  16.                 "ON b.ProjectedSavings < c.ProjectedSavings) AS a " & _
  17.             "ON CurrentProjection_tbl.niin = a.niin " & _
  18.             "GROUP BY CurrentProjection_tbl.niin) AS d " & _
  19.         "WHERE d.Contribution <= " & Opportunity & _
  20.         " OR d.Contribution IS Null);"
And can't seem to find out what the problem is. This database was already preexisting. Someone please help
4 Weeks Ago #1
Share this Question
Share on Google+
3 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,704
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!
4 Weeks Ago #2

twinnyfo
Expert Mod 2.5K+
P: 2,704
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....
4 Weeks Ago #3

NeoPa
Expert Mod 15k+
P: 31,121
Please check out Before Posting (VBA or SQL) Code.
4 Weeks Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.