Re: Should I close connection?

I'm just reading code that's already there and trying to fix it, so
don't know why the keyset thing. And yes, I shortened the function
name to make the post less cluttered, but forgot to change it in one
place :)
I tried something to see what was happening where... I started a trace
in the SQL server and observed what was happening. Looks like the
connection is automatically closed in the main sub after the function
returns the recordset, right when the resulting recordset is closed.
So I guess the connections are getting closed implicitly without my
closing them. Guess the problem lies elsewhere

Steve Gerrard wrote:
> <wolfing1@xxxxxxxxx> wrote in message
> news:1120142264.606990.236020@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> > Hi all. I'm trying to fix a VB6 application that is returning
> > 'Application error. The instruction at 0x7c8327f9 referenced memory at
> > 0xffffffff' when it finishes (everything works fine, but when it's
> > supposed to end it returns this error).
> > I'm looking at the code for possible problems, and I found this that
> > looks suspicious:
> >
> > Public Function ADId(DBConnectStr As String, ReportId As Integer) As
> > ADODB.Recordset
> > Dim ObjConn As New ADODB.Connection
> > Dim rs As New ADODB.Recordset
> > Dim strSQL As String
> >
> > strSQL = "Select * from tablet1 where Report_Id=" & CStr(ReportId) &
> > ""
> >
> > ObjConn.Open DBConnectStr
> > rs.Open strSQL, ObjConn, adOpenKeyset, adLockReadOnly
> >
> > Set AllDistributionMethodbyReportId = rs
> > end function
> >
> > So basically, it returns a recordset, but the application that calls
> > the function then closes the returning recordset and sets it to
> > nothing, but what about the connection? Does it get automatically
> > closed? Should I close it in the application before closing the
> > recordset? Could this be the problem?
> >
>
> Since you are opening a KeySet cursor (why?), there may be some reason to keep
> the connection open. I think KeySet implies that only the keys are initially
> retrieved, and the rest is retrieved later as needed.
>
> Normally ADO objects are closed and released when they go out of scope, and if
> the connection is getting closed, that may be tangling things up. I would try a
> Static cursor type first of all, and if that works, then you can explicitly
> close the connection at the end of the function.
>
> Also, the line:
> Set AllDistributionMethodbyReportId = rs
> doesn't have the same name as your function declaration:
> Public Function ADId(DBConnectStr As String, ReportId As Integer)
> Was that just shortened for the post?

.