 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / September 2006Freeing Resources - DAO
I inherited an Access 97 db that is running on a windows XP box, and it has started running into memory errors. This may not necessarily being a DAO only question, but the code I am working with is DAO/VBA. I have ruled out drivers as a cause of the errors, as the application has sucessfully run three times since the issue started, and I can open the linked tables before running the application. I have also checked the Temp folder (it was empty) and tested the application on 2 boxes with similar results. This prompted me to check more into the code and I've found that the code is using the same recordset variable multiple times. Normally I would think this wouldn't be a problem, but each time the recordset is closed it is then reopened to a new recordset immediately. I know standard practice is to set the recordset variable to Nothing when done using it, but I was wondering should I also do so inbetween closing one recordset and opening another. It seems like this would help to free resources but I am not 100% certain. I have included an example of the code below and welcome and suggestions. Thanks! [Code Example] While Not Myset.EOF If Myset![Field1] <> "Yes" Then Myset.Delete End If Myset.MoveNext Wend Myset.Close Set Myset = MyDB.OpenRecordset("OtherTable", DB_OPEN_DYNASET) Myset.MoveFirst [End Code Example] Matty If you are going to use the recordset again, you just have to close it. When you are finished with it entirely, you set it to Nothing. I doubt if that is causing the memory leaks. Is the database object being set Nothing as well? if not it should be. What about all other objects. Search your code from top to bottom for the word Set. Make sure there is a Set...Nothing for each one. I always put the Nothings in my exit part of my error traps so I know those lines will fire.  SignatureBill Mosca, Microsoft Access MVP > I inherited an Access 97 db that is running on a windows XP box, and it has > started running into memory errors. This may not necessarily being a DAO only [quoted text clipped - 29 lines] > > [End Code Example] Never loop through a recordset when you can do the same thing using a single SQL statement. Rather than: While Not Myset.EOF If Myset![Field1] <> "Yes" Then Myset.Delete End If Myset.MoveNext Wend simply use CurrentDb.Execute "DELETE FROM MyTable WHERE [Field1] = 'Yes'", dbFailOnExecute SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > I inherited an Access 97 db that is running on a windows XP box, and it > has [quoted text clipped - 35 lines] > > [End Code Example] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.