Freeing Resources - DAO

[Guest]

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]

 

[Guest]

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.

 

[Douglas J. Steele]

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