By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,070 Members | 1,238 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,070 IT Pros & Developers. It's quick & easy.

Access "not enough memory" - error 3183

codegecko
Expert 100+
P: 533
Hi folks,

Got a massive headache. I'm working as a contractor on a project for a SAM company and they are using an Access database with a particular query to extrat information to import from a DB into Visio.

The query is incredibly lengthy and would take too long to remove company-specific info from so I will keep this brief.
The query runs fine on the database when it is used locally on my machine (Acer Aspire 5003WLMi, 1.8GHz AMD Turion ML-32 processor, 512MB RAM, 60GB HDD and WinXP SP2) but when used on the company's machines (HP DX2250, 512MB RAM, 160GB HDD, and AMD Athlon 64-bit 3800+ - around 2.0GHz) the Jet 4.0 engine (as the DB uses linked tables) creates a TMP file that hits 2GB before coming up with an error of "not enough memory on temporary disk".

I have checked:
yet I am STILL getting problems.

If anyone has any ideas on how to fix this then it would be so much appreciated.

Many thanks in advance,

medicineworker
Oct 15 '07 #1
Share this Question
Share on Google+
10 Replies


nico5038
Expert 2.5K+
P: 3,072
Did you check where Office has located the temporary files?
I ran once in trouble running my .mdb on a D: drive, but being low on space on the C: drive (less then 150Mb) I got this error....

Nic;o)
Oct 15 '07 #2

codegecko
Expert 100+
P: 533
Hi nico,

Have checked, MDB is running on C: drive and temp is located in C:\Documents and Settings\%Logged on User%\Local Settings\Temp.
I have checked read/write perms and all is fine there.

I understand that Access will not deal in any data that is larger than 2GB. The TMP file that is being created goes all the way up to 2GB which suggests (I think) that the Jet ODBC driver is leaking something (as the query runs absolutely fine on my machine with no problems).

The only substantial difference I have noticed between the machines is this: I have SQL Server 2005 Express installed, which colleagues have suggested might fix the (probably broken) drivers, and I also have Jet 3.0 and 3.5 as well as 4.0 (the HP machines only have Jet 4.0 and no previous versions). Could it be something to do with backward-compatibility (all Access DBs that use linked tables are in Access 2000 format)?

Many thanks in advance,

medicineworker
Oct 16 '07 #3

codegecko
Expert 100+
P: 533
Addenum - have converted databases to Access 2003 format and still no luck, getting CPU spikes in the region of 70 - 100%. My manager says our PCs should still be under warranty... lmao.

Any last-minute suggestions would be appreciated.

medicineworker
Oct 16 '07 #4

Scott Price
Expert 100+
P: 1,384
What Office service pack do you have installed on the affected computer? This is Access service pack that I'm asking about. There are some significant issues apparently with using SP3 for Office/Access 2003. Since it runs fine on your computer and not on the other, I'd be inclined to suspect something like that.

See here: http://allenbrowne.com/tips.html

Regards,
Scott
Oct 16 '07 #5

codegecko
Expert 100+
P: 533
Hi Scott,

Both machines (mine, which works, and theirs, which doesn't) are running identical copies of Office 2003 with Service Pack 3.

Hope it helps narrow things down further!

medicineworker
Oct 16 '07 #6

Scott Price
Expert 100+
P: 1,384
Are you both working with the exact same copy of the database? If it's a front-end/back-end situation, have you checked to see that you are, in fact, running the very same version of the front-end?

Also, check to make sure that the version of the front-end they are running hasn't been adapted in some other way that might be affecting the query indirectly...

If you are absolutely sure that there are no software differences between the Access installations and the database versions, then we'll have to look outside Access/Office for possible problems.

Regards,
Scott
Oct 16 '07 #7

nico5038
Expert 2.5K+
P: 3,072
Hmm, did you consider to change the query in a Pass through query.
It will require a re-write in the original database's native SQL, but all space problems will be solved as the query is processed by the original backend database and only the result is passed back...

Just check the Access helpfile on the Pass through subject.

Nic;o)
Oct 16 '07 #8

codegecko
Expert 100+
P: 533
FINALLY IT WORKS!

Discovered problems in the query where it was retrieving data from the tables but had missed out the INNER JOIN statements - hence opening a billion different tables without reference points!

My day is finally stress-free!!!!!!!! :-D :-D :-D

medicineworker

P.S. Thanks to Nic for the idea but as you can now tell by my ecstatic response, it's now sorted. I can go home and SLEEP..... |-)
Oct 16 '07 #9

Scott Price
Expert 100+
P: 1,384
Glad you got it to work!

Thanks for posting back with what was wrong also.

Regards,
Scott
Oct 16 '07 #10

codegecko
Expert 100+
P: 533
We finally found out the meat of the problem, so to speak.

Having copied the DB and structures for our new client project, I deleted all the client-specific data from the DB structure so I could then start from scratch.
Apparently, Access has a (rather annoying) habit of destroying relationships when a table with relationships is emptied.

So the trick is: delete all but one row to use as padding to maintain the relationship references, then when new data is added, delete the padding row!

medicineworker
Oct 19 '07 #11

Post your reply

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