Error code 3035 question.

[mojo69]

I have a VB6 app using DAO to connect with Access database. The last two days users have been getting Error Code 3035 System Resource Exceeded when doing a simple query. Example: "SELECT * FROM APHF01 WHERE (APHFCheckNo = '999999');" The table APHF01 has around 200,000 records. It is also occurring when executing on a different table with around 30,000 records. The tables have a small number of columns ~20. Any ideas on why this would come up and what can I do about it?

Thanks.

[jggtz]

...and the Access database version is?
...and the Windows version is?

[mojo69]

Access 2007
Windows Server 2008 R2 Standard Service Pack 1 64 bit

[DataMiser]

I am a bit surprised that DAO even works with 2007.

You really should consider updating the code to ADO and the ACE provider if possible.

[jggtz]

Check next links, maybe they can help you
http://www.justanswer.com/computer-p...or-access.html
http://bytes.com/topic/access/answer...ource-exceeded

[mojo69]

Thanks but I already changed the MaxLocks to 2,000,000 awhile ago. Any other ideas?

[mojo69]

I am a bit surprised that DAO even works with 2007.

You really should consider updating the code to ADO and the ACE provider if possible.

We have started the process to upgrade the application to .Net. However this process is going to take some time since we are a two person shop and have a learning curve.
We have this application in about 50 offices and this is the first time in 12 years that this message has come up?

[DataMiser]

Well clearly it was not using Access 2007 for all that time, I have a few old apps that still use DAO as well but they still use the old DB structure and have not had any issues with them as of yet. It just seems odd that one would update the database to 2007 but still be using the engine from 1997 to talk to it. I would have thought it would not work at all but apparently it sorta does.

Depending on how the code was written and how big the project is changing to ADO and the ACE provider may or may not be a big deal, surely not nearly so much as rewriting in VB.Net and ADO.Net but may not be worth while. Have you tried it with and older DB format like 2000 or even 1997 MDB formats? Remember DAO was being phased out in favor of ADO as of 1998 when VB6 was released.

[jggtz]

We can create mdb format with Access 2007 and still use VB6 & DAO
The problem here, IMHO, is to use it in Windows 64 bit

[mojo69]

To be clear, Access 2007 is installed on the machine and we only use it to make database changes etc. The application is still using the old jet engine.

Also I wish the original developer would have used ADO instead. However, that was not case and I am stuck trying to fix this.

[mojo69]

SELECT PMTDHDR05.RecordID, PMTDHDR05.RecordTS, PMTDHDR05.RecordCS, PMTDHDR05.OwningID, PMTDHDR05.PaymentType, PMTDHDR05.HDateFlag, PMTDHDR05.NetTot, PMTDHDR05.CmptrNo, PMTDDET05.OwnerID, PMTDDET05.Extension, PMTDDET05.CshDrwBalPrt FROM PMTDHDR05, PMTDDET05 WHERE (PaymentType = 'CSH') AND (CmptrNo = '06') AND (Mid(PMTDHDR05.RecordCS,1,10) = '2013/05/21') AND (PMTDDET05.CshDrwBalPrt <> 'Y') AND (PMTDHDR05.OwningID = PMTDDET05.OwnerID);

This query returned the error code message 3035 System Resouce Exceeded. This query does not have too many joins? I need some help with this. Again this is a VB6 application using DAO.

There are two records that this query should have returned, very small amount of data.

[mojo69]

This is the line where the error occurs.

Set RecSet = Glob.dbAppDB.OpenRecordset(SQLStatement, rstype)

rstype is dbOpenDynaset

[mojo69]

Just noticed that RecSet.Close never happens for this operation. Is this a problem? A new Recordset is created each time the function is called.

[si_the_geek]

If you have finished with the recordset, leaving it open is a problem.

If you don't close it, it will stay open (using up various resources) until something somewhere decides to makes it close (which might be the driver/provider you connect with, or the database system itself). The chances are that it wont be closed automatically until some time after your program closes.

It may not be causing your current problem, but there is a good chance that it is - and even if it isn't, there is no point leaving recordsets open and using resources without good reason.


Note that in addition to closing recordsets, setting them to Nothing is a wise move too (as it helps let the database etc know they can clean up)... but it isn't quite as important, because it uses far less resources, and it the auto-cleanup is done automatically within the VB side of things (so tends to be done sooner, but not as soon as if you'd done it yourself).

[mojo69]

That is what I thought. Curious as to why the person who wrote this code would not clean it up. When I put the .Close in the code is there a way to tell if the RecordSet is null?
ie.

If Not IsNull(RecSet) then
    RecSet.Close
End If

[si_the_geek]

A recordset cannot be null.

It can however be Nothing, and you can use similar code to this safely close it and tidy up:

    If Not rs Is Nothing Then    
       If (rs.State And adStateOpen) = adStateOpen Then rs.Close
       Set rs = Nothing 
     End If

...but as this code is for ADO, the If statement will need to change a bit - unfortunately it has been far too long since I used DAO to be able to remember what it should be.