I have a component, DBX, that handles my database connectivity. It has a CreateRecordSet method that takes SQL and returns a rdoResultSet. The problem is that if in an application I open a resultset with the CreateRecordSet method and then need to do another CreateRecordSet (using a different resultset object), it uses a second database connection for the second resultset.
Here is an example of the code:
dim result1 as rdoResultset
dim result2 as rdoResultSet
set result1= CreateRecordset("select * from table1")
while not result1.eof
'Next line causes a new connection
set result2 = createrecordset("select * from table2 where x= " & result1("field"))
I can't close the resultset in my DBX component because then it can't be returned to the calling procedure. At the end of a routine like the one above, I wind up with tons of open connections.