An area that can cause performance degradation is the constant recompiling of stored queries. This can occur when DAO objects are not explicitly closed. The reason for this is that stored queries reference a particular memory address in order to use the Visual Basic for Applications expression service. If a DAO object is opened, and then a stored query is executed, re-executing that query may cause Microsoft Jet to recompile the query because the original memory address space is occupied. In certain circumstances, this also causes the database to increase in size. Many of these issues were resolved by enhancements to Microsoft Jet 3.5, but some of the issues can be resolved only by using the Close method to explicitly close DAO objects when they are no longer being used. You should not rely on Visual Basic for Applications to implicitly close objects in your code when a Sub or Function procedure is exited.
If you cannot explicitly close a DAO object because it is nested in an explicit transaction or some other scenario, another possible workaround is to use the dbOpenSnapShot or the dbForwardOnly constant for the type argument of the OpenRecordset method when you open a recordset. Using the dbOpenSnapShot or the dbForwardOnly constant prevents Microsoft Jet from recompiling the query. This is most useful when using the data grid that comes with Microsoft Visual Basic 5.0, because you can’t explicitly close the DAO objects being used.