Access "not enough memory" - error 3183

codegecko

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:

• Temporary folder permissions and size limits;
• Microsoft's knowledge base and recommended fixes (listed at http://support.microsoft.com/kb/286153/en-us);
• Checked for all patches, service packs and ODBC/Jet drivers;

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

nico5038

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

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

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

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

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

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

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

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

Glad you got it to work!

Thanks for posting back with what was wrong also.

Regards,
Scott

Oct 16 '07 #10

codegecko

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