FIX: Memory Leak When You Use Now Function in SQL with Jet 4.0 Provider or Driver

If an application connects to a database using the Microsoft Access ODBC Driver or the Microsoft OLE DB Provider for Jet version 4.0, and if the application uses the Now function of the Visual Basic for Applications in the WHERE clause of a SQL query, a memory leak occurs. If you use the function repeatedly, eventually enough memory leaks so that the application fails (crashes).

Install the latest Microsoft Jet 4.0 service pack. For additional information about how to install the latest Jet service pack, click the following article number to view the article in the Microsoft Knowledge Base:

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

Steps to Reproduce Behavior

1. Create a new Visual Basic Standard EXE project. Form1 is created by default.
2. Set a reference to Microsoft ActiveX Data Objects (ADO).
3. Place a timer control on Form1, and set its Interval property to a value such as 1000.
4. Paste the following code:

   Dim m_connADO As ADODB.Connection
   
   Private Sub Form_Load()
       Set m_connADO = New ADODB.Connection
       m_connADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source=C:\Program Files\Microsoft Visual Studio\Vb98\nwind.mdb;"
   End Sub
   
   Private Sub Form_Unload(Cancel As Integer)
       Timer1.Enabled = False
       m_connADO.Close
       Set m_connADO = Nothing
   End Sub
   
   Private Sub Timer1_Timer()
       Dim rsTemp As ADODB.Recordset
       Dim strQry As String
       Debug.Print "Timer event: " & Now
       strQry = "SELECT * FROM Orders WHERE OrderDate <= Now()"
       Set rsTemp = m_connADO.Execute(strQry)
       rsTemp.Close
       Set rsTemp = Nothing
   End Sub
   					

5. Run the project, and use the Performance Monitor tool or Task Manager to monitor the memory usage.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base: