424,289 Members | 1,875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,289 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
Nov 13 '18 #1
Share this Question
Share on Google+
3 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,888
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!
Nov 13 '18 #2

twinnyfo
Expert Mod 2.5K+
P: 2,888
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....
Nov 13 '18 #3

NeoPa
Expert Mod 15k+
P: 31,186
Please check out Before Posting (VBA or SQL) Code.
Nov 15 '18 #4

Post your reply

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