Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005

    Unanswered: Memory issue with ADO 2.8

    I'm having serious trouble with a VB6 application which connects to a Access 2000 DB using DAO 2.8 library. This application writes about 10000 records in a table in a loop. During this process it looks like there is a memory issue because we are losing a lot of memory (sometimes 128 MB depending on the number of records).

    I made a sample application which reproduces this problem:
    Private Sub Command1_Click()
    Dim conn As New Connection
    Dim x As String
    Dim i as Long
    conn.Open "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\MyDB.mdb"
    conn.Properties("Jet OLEDB:Max Buffer Size") = 2048
    ' MyTable contains columns field ID (autonumber long) and a Memo field Text
    x = String$(1000, "x")
    conn.Execute "DELETE FROM MyTable", , adExecuteNoRecords
    For i = 1 To 10000
       conn.Execute "INSERT INTO MyTable (Text) VALUES('" & x & "')"
       Command1.Caption = "Writing record " & i
    Next i
    Set conn = Nothing
    Memory available before writing 10000 records

    Memory available after writing 10000 records

    I also found the following Microsoft KB article:;en-us;248014

    This article describes my problem and offers the following resolution:
    The Max Buffer Size property control is the maximum amount of memory that can be used by the read-ahead/write-ahead Jet cache.

    Set the Jet OLE DB:Max Buffer Size property to a value such as 2048 kilobytes. When using ADO, the property must be set after opening the connection.
    conn.Properties("Jet OLEDB:Max Buffer Size") = 2048

    As you can see I assed this line in my code, but this does not solve my problem. The article also implies the memory will be freed after the connection is closed, but this is not the case! Hope some of you experts can help.

    Thanks, Peter

  2. #2
    Join Date
    Jan 2005
    Try calling the following code after your inserts have'll need a reference to the 'Jet and Replication objects library' msjro.dll, or just late bind the object...

    Public Sub RefreshConnectionCache(objConnection As ADODB.Connection)
        Dim objJet As JRO.JetEngine
        Set objJet = New JRO.JetEngine
        objJet.RefreshCache objConnection
        Set objJet = Nothing
    End Sub
    Haven't used Access for a while, but from what I remember Access does a lot of its work within the client RAM - refreshing the cache forces RAM held data to be written to the physical location.

    This solved a problem I had with one application - data was showing as saved and the user could browse it. When they closed & reopened the application, the data had vanished - it wasn't being flushed from RAM!

    The best solution which fixes 99.99% of problems is not to use Access at all!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts