"Error 3035 : System Resource exeeded" while moving to a better hardware

[Guigui]

Hello,

I have a database with a big table (78 columns, 930 000 records) that I update using several query linking with other smaller tables.

I need to transfer this from one machine to another. While it is working well on the current machine, I have this Error 3035 : System resource exceeded on the new one, while it is bigger and newer than the old one :

Old machine :
Core 2 Duo E8400 @ 3Ghz
4 Gb RAM
300 Gb SATA RAID Volume (4 * 150 Gb in Raid 1+0)
Windows Vista Enterprise SP2 / 32 bits
Office 2007

New machine :
Xeon E5645 @ 2.4Ghz
24 Gb RAM
1.2 Tb SAS RAID Volume (4 * 600Gb in Raid 1+0)
Windows Server 2008 R2 SP1 / 64 bits


Office 2010

So I would expect the database to run even faster on the new one, but it just fails...

I've googled the error, and tried to increase the MaxLocksPerFile to 1 500 000 in the registry as suggested, with no success. No other type of answer from google

Anyone with another idea that I could check ?

Thanks !

[rpeare]

What are you doing that generates this error, are you running a query or running a script? Are you looking at the exact same dataset when you try to run this, if you've got a problem with your data you may be trying to perform a calculation or string function on something access can not handle.

[Guigui]

I'm running an update query when it happens. I've got the same message when running from a script or directly. The same query on the same database with the same set of data works on the old machine, not on the new one.

[rpeare]

Have you tried increasing the timeout on queries? I'm not sure how to get to it in 2007 but in 2003 (as an example) you go to

TOOLS > OPTIONS > ADVANCED > OLE DDE TIMEOUT (set to 300 is the max in 2003)

You may also want to make a copy of your database and compact and repair it as well. I haven't encountered this error before but it seems like it's a machine specific setting that we're talking about. Is the data source for the table a linked table? if so is it to an access back end? SQL?

What exactly is the update performing is it updating one field only or a series of fields? if it's updating a series of fields have you tried updating one field at a time and adding fields until you get the error? also if you do this method and find the field it craps out on if you run the query again with JUST that field there may be some problem with the field.

Do the machines have the same version of access?

[Guigui]

Thanks for the hints.

Have you tried increasing the timeout on queries? I'm not sure how to get to it in 2007 but in 2003 (as an example) you go to

TOOLS > OPTIONS > ADVANCED > OLE DDE TIMEOUT (set to 300 is the max in 2003)

Found the same option in 2010, but no effect.

I haven't encountered this error before but it seems like it's a machine specific setting that we're talking about. Is the data source for the table a linked table? if so is it to an access back end? SQL?
Do the machines have the same version of access?

Yes looks like a machine issue, or a Access 2007 vs 2010 issue : it works on the "small" machine under 2007 and fails on the "big" machine under 2010.
This is all linked tables to 2 different access back end.

What exactly is the update performing is it updating one field only or a series of fields? if it's updating a series of fields have you tried updating one field at a time and adding fields until you get the error? also if you do this method and find the field it craps out on if you run the query again with JUST that field there may be some problem with the field.

This is a couple of update queries, each one updating a few fields of the same table. I get this same error if I run them using a VB code or directly in the access interface. I don't think this is an issue with the queries themselves as they sometimes run well (see below).

You may also want to make a copy of your database and compact and repair it as well.

This was the first thing I tried, but it does not really solves the issue : when I compact & repair the 3 databases (front-end + the 2 back-end), I can run one query, then the second one fails and I need to compact & repair again. and this goes on for the following 5 queries...
Each of the 3 databases are far from the 2Gb limit (600k for the biggest one).


I did some digging around and managed to get the same error by simply opening one of the table in the interface and scrolling around : "Error 3035 : System Resource exceeded", then all fields replaced by "NAME?". Couldn't reproduce this systematically, but saw it twice

[rpeare]

Are these linked to 'live' data? as in people are updating the data using some other software and you're trying to view back end data? I am going to assume that's the case, I just had a similar problem linking to a SQL database and I can't recall how I solved it. In my case it was an issue surrounding using NT authentication or not in the ODBC driver. Are you using NT authentication in your links or does it prompt you for a username/pw when you connect to the tables in your application?

[Guigui]

No the links are to static table in another access database on the same drive. They could be accessed by other people (this is a shared station with access via Remote Desktop), but I tested right after a reboot, with nobody connected and it failed the same way.

I will try to move this to my notebook under W7 / Access 2010 to see if it works, that would maybe point Access 2010 as the issue, but I doubt it.

[kpjgpm]

Did you ever find a solution to this? I am having the same issue. It runs fine on the local machine even when the database is on a shared drive (no one else has access to it). But when I remote to that machine it gets the error. I am wondering if it's the virtual server causing an issue.

[Rawb]

A quick Google search makes this look like maybe the Query itself is too long. Try printing out your Query (using Debug.Print) to see how long it is.

Access only supports Queries that are about 64,000 characters (or less) in length.

[kpjgpm]

The query is not too long. And that doesn't answer why it works on one computer and not on another faster more powerful computer.

[Rawb]

kpjgpm, sorry, I didn't notice before that you're not the OP.

Please create a new thread and provide the following details:

• What exactly are you doing when you receive the error?
• If you have access to any other machines, does this process work any of them?
• You mentioned "remoting" into the computer. How are you doing this, Microsoft's Remote Desktop Connection or a third-party program (like WinVNC)?
• You also mentioned virtual servers. Are you running the BE database on a VM? Or just the FE?

[Guigui]

Did you ever find a solution to this? I am having the same issue. It runs fine on the local machine even when the database is on a shared drive (no one else has access to it). But when I remote to that machine it gets the error. I am wondering if it's the virtual server causing an issue.

Not really found a definitive solution, but I tried those :
1- http://support.microsoft.com/kb/2760394 => worked partly (less errors)
2- moving to 2013 on the new machine helped, then moving to 2013 32 bits (instead of 64) helped again : now I get the error only every 3 or 4 query.
3- Implemented an automated compact and repair into the VB code every 2 queries => work around the issue, but increases the process length.

In the end I spent a few hours on this surfing the web and testing some fixes. It still takes more time on the "big" machine under the latest software and OS than on the "small" old one but at least it works. So I will call this a Microsoft Enhancement and let it be this way until it fails again

[Guigui]

A quick Google search makes this look like maybe the Query itself is too long. Try printing out your Query (using Debug.Print) to see how long it is.

Access only supports Queries that are about 64,000 characters (or less) in length.

query is working perfectly on a smaller configuration. So to me the database itself is not in question.

[PhilB]

query is working perfectly on a smaller configuration. So to me the database itself is not in question.

Hi I'am French (my english is not perfect), I have the same problem. My database works with a smaller configuration, and it doesn't work with bigger configuration. For me the problem is not associated to the database, it is a configuration problem or Microsoft Access bugs. When I run my database on Access 2010 the database work perfectly (on Windows 7, 2Go of RAM, 1,3 Go for swap system space). When I run my database on Access 2013 (on windows 8, 8Go of Ram, 50Go of swap system space). I change some information in Registry (MaxBufferSize, MaxLock......) --> No change, memory system error .... I have a big database,but my database does not exceed the limits. I have already seen regressions and bugs on Access. For regressions generally they are referenced by microsoft.