memory leak in ODBC driver

[Former Member]

I have a serious problem with the maxdb ODBC Driver. I tested Version 7.06.03.00 and some older Versions.

Here is a little example for MS Access to show the problem. The table "artikel" is a linked maxdb table. The example code opens and closes a recordset in a loop. You can see the memory usage in the task manger going up and up. If you have many recordsets in the table, the system is crashing when it is going out of memory.

If i change the linked table to for example a mysql-table the memory usage is stable. So I think there must be a memory leak in the maxdb odbc driver.

Example code:

Sub memtest()

Dim RS1 As Recordset, RS2 As Recordset, DB1 As Database

Set DB1 = CurrentDb

Set RS1 = DB1.OpenRecordset("select id from artikel")

Do While Not RS1.EOF

Set RS2 = DB1.OpenRecordset("select * from artikel where id=" & RS1("id"))

'do something

RS2.Close

Set RS2 = Nothing

RS1.MoveNext

Loop

RS1.Close

Set RS1 = Nothing

DB1.Close

Set DB1 = Nothing

End Sub

I hope you can follow me and find the leak.

Thorsten

[TTK · Accepted solution]

Hello Thorsten

Acutally in the 7.6 and older ODBC drivers, there is a leak, if the driver is loaded and unloaded by the driver manager again and again. This can usually be avoided with connection pooling (Control Panel -> Admin -> Data Source Administator: tab Connection Pooling).

Another possibility would be to hold at least one ODBC environment (or if this cannot be done in your application, then open an additional connection). This prevents the unloading of the ODBC driver.

Other memory leaks are not known. If you think, you found a new one, please let me know.

Regards Thomas

[Former Member · Accepted solution]

Hello Thomas,

thanks for your tips, but they did not help. Activating Connection Polling did not change anything. And opening another connection did not help, either. I did it the following way:

Sub memtest2()

Dim conODBC As New ADODB.Connection

conODBC.ConnectionString = "Data Source='xxxx';User ID='xxx';Password='xxx'"

conODBC.Open

Dim Rs1 As Recordset, Rs2 As Recordset, DB1 As Database

Set DB1 = CurrentDb

Set Rs1 = DB1.OpenRecordset("select id from artikel")

Do While Not Rs1.EOF

Set Rs2 = DB1.OpenRecordset("select * from artikel where id=" & Rs1("id"))

'do something

Rs2.Close

Set Rs2 = Nothing

Rs1.MoveNext

Loop

Rs1.Close

Set Rs1 = Nothing

DB1.Close

Set DB1 = Nothing

conODBC.Close

End Sub

Perhaps one of you has another tip how this can be done.

@Lukasz:

using a seperate ADODB.Connection works fine, but that does not solve the problem itself. At first I would have to rewrite much code and the second much bigger thing is that the memory leak occurs even when working with bound forms in MS Access. For example: I have one large form with many subforms on it. If i go from record to record on this form the memory usage is increasing dramatically fast and there is no way to influence what kind of connection MS Access is using for bound forms.

Regards

Thorstem

[TTK · Accepted solution]

Hello Thorsten

Can you please provide me a small Visual Studio example (including DDL) for reproducing? This would me a lot.

Thanks in advance Thomas

PS: My email address is in my user profile.

[TTK · Accepted solution]

Hello Thorsten

Eventually I pursuit the whole thing down.

For the files, there is actually a memory leak in the 7.6.03 b15 driver.

Please download the files in the archive:

[https://sapmats-de.sap-ag.de/download/download.cgi?id=60E1XTT0JCYNDEUJZOPC8UGBWW231DQ1N50E0SUTGMSWSMOWNN]

and replace the dlls.

FYI, this is a not officially download, i.e. it has no fully qualified release stamp from our software distribution process. However, to accelerate the access, I think you appreciate this download instead of waiting any longer.

Greetings & thanks for helping

Thomas

[Former Member · Accepted solution]

I you tested the files and they solved my problem.

Thank you very much

Thorsten

[Former Member]

Hi Thorsten,

I had similar problem.

Check this:

- run access project

- run sub (probably thread memory grows)

- when sub ends check memory (probably you have leak)

- minimize all access windows to taskbar

MSACCESS thread shrinks memory. (sic!)

BTW Remove where id=" & Rs1("id") from query - there is no leak.

Workaround is:

- open another connection to MAXDB and use it:

Dim testConnection As New ADODB.Connection

testConnection.ConnectionString = _

"Data Source='odbcname';" & _

"User ID='xx';Password='xx'"

testConnection.Open

Dim Rs1 As New ADODB.Recordset

Rs1.Open "Select * from ARTIKEL", _

testConnection, adOpenStatic, adLockReadOnly, adCmdText

Do While Not Rs1.EOF

Dim Rs2 As New ADODB.Recordset

Rs2.Open "select * from ARTIKEL" ' where id=" & Rs1!id, _

testConnection, adOpenStatic, adLockReadOnly, adCmdText

Rs2.Close

Rs1.MoveNext

Loop

Rs1.Close

testConnection.Close

It helps.

Best regards,

Lukasz.