When you’re working with QueryDef (SQL instructions stored on an MDB database) and open it, DAO loads all the QueryDefs. For example, if you have an MDB with five QueryDefs named qryCustomers, qryOrders, qryContacts, qrySales, and qryPersons, and you want to use the qryCustomers, do this:
Dim qdCustomer as QueryDefDim rsCustomer as RecordSet Set qdCustomer= Db.QueryDefs("qryCustomers") qdCustomer.Parameters![Custom ID]= 195 Set rsCustomer= qdCustomer.OpenRecordSet(dbReadOnly) While not rsCustomer.Eof txtCustomerName= rsCustomer!Name ........... rsCustomer.MoveNext Wend rsCustomer.Close 'Close it set rsCustomer=Nothing 'Free the reference to rsCustomer qdCustomer.Close 'Close it set qdCustomer = Nothing 'Free the reference to qdCustomerThe problem is that DAO only closes the qdCustomer, but the other four QueryDefs (qryOrders, qryContacts, qrySales, and qryPersons) remain open. To solve the problem, use this subroutine:
Public Sub ToNothing()Dim qdGeneric as QueryDef 'Surf the QueryDefs Collection For each qdGeneric in Db.QueryDefs qdGeneric.close 'Close it Set qdGeneric = Nothing NextEnd Sub Now put the call to the subroutine ToNothing: . . .rsCustomer.CloseSet rsCustomer = NothingToNothing
