Re: Recordset Closed After Having Just Being Opened

Yes SQLserver with an access ADP 2003 frontend.

Regards,
Marc

Ralph wrote:

"Karnifexx" <m.p.fletcher@xxxxxxxxxxxxxx> wrote in message
news:1165761611.777992.72620@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hey,
If someone could help me with this it would be greatly appreciated.
I have the following code, part of a function (irrevlant and parameter
declaration has been removed). Suffised to say the function works most
of the time and a recordset is usually returned. However sometimes, as
is commented the recordset is closed after the execute statement. What
is causing this to happen?

Dim sqlConn As ADODB.Connection
Dim sqlCmd As ADODB.Command
Dim sqlRS As ADODB.Recordset

' Establish connection.
Set sqlRS = New ADODB.Recordset
Set sqlCmd = New ADODB.Command
Set sqlConn = New ADODB.Connection

sqlConn.ConnectionString = CurrentProject.Connection
sqlConn.Open

sqlConn.CursorLocation = adUseClient 'Cache data locally

' Open recordset.
With sqlCmd
.ActiveConnection = sqlConn
.CommandText = ExecuteSP.Name
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters....(removed)
Set sqlRS = .Execute()
End With

'Here the SP has executed correctly but the sqlRS is closed ;(
???!?!?!?


Just a quick comment...

This is a common phenomena when using ADO to call a stored procedure if no
data is returned. But the exact details escape me at the moment. IIRC there
are possible changes that can be made to the SP itself to protect against
this. (depending on provider/database of course).

I'll do a little research and get back to you. But for the moment it will
help to assure you that no major 'bug' is at work here. <g>

What database are you using? SQLServer?

-ralph

.

Relevant Pages
Loading