ADO Memory Leak results in out of memory 
Author Message
 ADO Memory Leak results in out of memory

Hy,

I'm working on an old VB6 project and I ran into a {*filter*} problem.

The problem is that every time I read a recordset from the database, my
memory is increasing and seems never to be released.
After a while, this results in an error 2004 (Out of memory) and the
application hangs :-s

I am using DAO 3.51.

The simplest code generating the problem is :

Set RS = .DB.OpenRecordset("SELECT * FROM Nomenclature Order by Index", ,
dbReadOnly)
RS.Close
Set RS = Nothing

Updating to other Data drivers is not an option.

Could someone please help me out with this ?

Thx,

Frederick R.



Mon, 30 Jan 2012 21:09:07 GMT  
 ADO Memory Leak results in out of memory



Quote:
> Hy,

> I'm working on an old VB6 project and I ran into a {*filter*} problem.

> The problem is that every time I read a recordset from the database, my
> memory is increasing and seems never to be released.
> After a while, this results in an error 2004 (Out of memory) and the
> application hangs :-s

> I am using DAO 3.51.

> The simplest code generating the problem is :

> Set RS = .DB.OpenRecordset("SELECT * FROM Nomenclature Order by Index", ,
> dbReadOnly)
> RS.Close
> Set RS = Nothing

> Updating to other Data drivers is not an option.

> Could someone please help me out with this ?

Not enough information.

As it is obvious that that simple statement in itself could not be causing
an "out of memory" then there must be something else about the context in
which it is being used.

First off, "out of memory" errors when dealing with DAO are often bogus in
the sense they are more likely caused by an "out of resource" problem. For
example, miss-matched Jet versions can cause this error. So can corrupted
databases.

Second, the wrong 'data' driver in a mix is also a likely suspect. The fact
you are claiming that changing out the Driver is NOT an option leads me to
believe you might already believe your current data stack might be the real
problem. Never the less, this may be the only cure.

Since you are dealing with an "old" project on perhaps an newer development
box my money, without more information, is that you indeed have a miss-match
data stack (data access library, driver, data engine, database).

Post more of your code including how RS and DB is declared, the Driver you
are using, and the database version.

-ralph



Mon, 30 Jan 2012 21:57:48 GMT  
 ADO Memory Leak results in out of memory
Ralph,

some more information :

- the function is called by a timer
- the database is opened as following, and is set as property of an object

With MyObject
Set .DB = daoWS.OpenDatabase(genLocalDBPath & .DBName, Exclusif, ReadOnly)
End With

(I'v tried putting this "Set .DB = nothing" after my openrecordset with no
result)

- I am using a Access 97 Database
- As DB-driver I use "Microsoft DAO 2.5/3.51 Compatibility Library"
(dao2535.tlb)
- My senior developer is telling me that updating the driver is not an
option because :
he says that with the newer driver this doesn't work anymore :

RS.AddNew
MyVar = RS!ID (where ID is an autoincrement field)

He claims that the new driver doesn't return a value there. (and that is a
lot used in the code)

If you need any more information, just ask.

Thanks for helping me out.

Frederick R.

Quote:



> > Hy,

> > I'm working on an old VB6 project and I ran into a {*filter*} problem.

> > The problem is that every time I read a recordset from the database, my
> > memory is increasing and seems never to be released.
> > After a while, this results in an error 2004 (Out of memory) and the
> > application hangs :-s

> > I am using DAO 3.51.

> > The simplest code generating the problem is :

> > Set RS = .DB.OpenRecordset("SELECT * FROM Nomenclature Order by Index", ,
> > dbReadOnly)
> > RS.Close
> > Set RS = Nothing

> > Updating to other Data drivers is not an option.

> > Could someone please help me out with this ?

> Not enough information.

> As it is obvious that that simple statement in itself could not be causing
> an "out of memory" then there must be something else about the context in
> which it is being used.

> First off, "out of memory" errors when dealing with DAO are often bogus in
> the sense they are more likely caused by an "out of resource" problem. For
> example, miss-matched Jet versions can cause this error. So can corrupted
> databases.

> Second, the wrong 'data' driver in a mix is also a likely suspect. The fact
> you are claiming that changing out the Driver is NOT an option leads me to
> believe you might already believe your current data stack might be the real
> problem. Never the less, this may be the only cure.

> Since you are dealing with an "old" project on perhaps an newer development
> box my money, without more information, is that you indeed have a miss-match
> data stack (data access library, driver, data engine, database).

> Post more of your code including how RS and DB is declared, the Driver you
> are using, and the database version.

> -ralph



Tue, 31 Jan 2012 14:21:01 GMT  
 ADO Memory Leak results in out of memory


Quote:
> - As DB-driver I use "Microsoft DAO 2.5/3.51 Compatibility Library"
> (dao2535.tlb)

On XP, this actually maps to DAO350.DLL. I just tried a sample application
and viewed the list of loaded DLL's.

Quote:
> - My senior developer is telling me that updating the driver is not an
> option because :
> he says that with the newer driver this doesn't work anymore :

> RS.AddNew
> MyVar = RS!ID (where ID is an autoincrement field)

> He claims that the new driver doesn't return a value there. (and that is a
> lot used in the code)

I am sure that there is a solution for that. Search the newsgroups, or MSKB
for "vb DAO AddNew AutoNumber".


Tue, 31 Jan 2012 15:40:00 GMT  
 ADO Memory Leak results in out of memory

Quote:

> Ralph,

> some more information :

> - the function is called by a timer
> - the database is opened as following, and is set as property of an object

> With MyObject
> Set .DB = daoWS.OpenDatabase(genLocalDBPath & .DBName, Exclusif, ReadOnly)
> End With

> (I'v tried putting this "Set .DB = nothing" after my openrecordset with no
> result)

> - I am using a Access 97 Database
> - As DB-driver I use "Microsoft DAO 2.5/3.51 Compatibility Library"
> (dao2535.tlb)
> - My senior developer is telling me that updating the driver is not an
> option because :
> he says that with the newer driver this doesn't work anymore :

> RS.AddNew
> MyVar = RS!ID (where ID is an autoincrement field)

> He claims that the new driver doesn't return a value there. (and that is a
> lot used in the code)

> If you need any more information, just ask.

> Thanks for helping me out.

> Frederick R.

Many years ago I used code like the following:

To list/retrieve data...

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String

sSQL = "SELECT RecordID, Record FROM Records ORDER BY Record;"

'Use a WHERE clause in the above if you don't need all rows!

Set db = DAO.DBEngine.Workspaces(0).OpenDatabase(sConnection)
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot, dbForwardOnly)

With rs
     If .BOF And .EOF Then
         'No records
     Else
         Do Until .EOF
             lst.AddItem .Fields("Record").Value & ""
             lst.ItemData(lst.NewIndex) = .Fields("RecordID").Value

             .MoveNext
         Loop
     End If
     .Close
End With

Set rs = Nothing

db.Close
Set db = Nothing

To add a new record, or update an existing record...

sSQL = "SELECT * FROM Records WHERE RecordID = " & lID & ";"

Set db = DAO.DBEngine.Workspaces(0).OpenDatabase(sConnection)
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)

With rs
     If .Updatable Then
         If lID = 0 Then
             .AddNew
         Else
             .Edit
         End If

         .Fields("Record").Value = Trim$(txtRecord.Text)

         .Update

         If lID = 0 Then
             .Move 0, .LastModified    ' Go to new record

             lID = .Fields("RecordID").Value  'Autonumber value
         End If
     Else
         Debug.Assert False
     End If
End With

rs.Close
Set rs = Nothing

db.Close
Set db = Nothing

Hope this helps.



Tue, 31 Jan 2012 22:54:22 GMT  
 ADO Memory Leak results in out of memory



Quote:
> Ralph,

> some more information :

> - the function is called by a timer
> - the database is opened as following, and is set as property of an object

> With MyObject
> Set .DB = daoWS.OpenDatabase(genLocalDBPath & .DBName, Exclusif, ReadOnly)
> End With

> (I'v tried putting this "Set .DB = nothing" after my openrecordset with no
> result)

> - I am using a Access 97 Database
> - As DB-driver I use "Microsoft DAO 2.5/3.51 Compatibility Library"
> (dao2535.tlb)
> - My senior developer is telling me that updating the driver is not an
> option because :
> he says that with the newer driver this doesn't work anymore :

> RS.AddNew
> MyVar = RS!ID (where ID is an autoincrement field)

> He claims that the new driver doesn't return a value there. (and that is a
> lot used in the code)

> If you need any more information, just ask.

First off there is a slight difference between us on definitions.
"Driver" is usually used to describe the data link component between a data
access library (client code) and a database engine. In this case it is the
Jet driver. [The term "Provider" is used for an analogous OLE DB data link
component when using the ADO data access library.]
What you are calling your "DB-driver" is actually the data access library.
In this case a library that allows you to use older 2.5 methods with the DAO
3.5 data access library.

[I'm getting rather confused because first you said you were using DAO 3.51
and now you are saying you are using the compatiblity library (not to
mention you said "ADO" in the title. lol]

So to get on the same page, lets make sure we understand the environment.
This is a legacy project, so I assume something that was once working
without problems, until you decided to re-work it for some reason. And now
you have a memory leak - or was this always a problem?

There have been several known issues with memory leaks through the years,
but most that have been acknowledge by MS have supposedly been fixed in
subsquent Jet Service Packs. So the first thing I would do is to make sure
you have installed the Latest 'n Greatest Jet package for your platform
(unless you are using Vista or Win7 in which case it is already installed).

This means you will be using Jet 4 with a Jet 3 formatted database. This
article will help explain the differences:
"How to keep a Jet 4.0 database in top working condition"
http://support.microsoft.com/kb/303528/
You aren't using a Jet 4 database, but read on anyway. <grin>

Check and see if you still have a memory problem.

I would replace the compatiblity library with straight DAO 3.51 and test out
the code for myself. If it is true the particular code construct doesn't
work - comment it out or work-around and test for the memory problem.

At some point it doesn't matter what the "senior developer" does or doesn't
want to change - if it doesn't work, it doesn't work and needs to be fixed.

-ralph



Tue, 31 Jan 2012 23:05:10 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. OLE Automation Problems - MEMORY HOG (Memory Leak?)

2. memory leak in vitual memory

3. memory leak on vitual memory

4. Memory Leak -- Out of Memory

5. ADO cause memory leak on EXCEL.

6. Memory leak! (ADO.NET, VB.NET)

7. Possible Memory Leak When Using ADO/DAO with VB6

8. Memory leaks in ADO

9. Memory Leak - VB5 ADO

10. Suspect VB5 ADO Memory Leak

11. On Error Resume Next problem and ADO memory leak

12. (Relatively) Slow memory leak in ADO?

 

 
Powered by phpBB® Forum Software