I have a function that is supposed to return an ADODB.RecordSet. The code is
shown below. My problem is that when I close the connection and close the
recordset in this function the function returns nothing. I commented out the
lines and it works fine. How doe I Keep the recordset even after the
connection has been closed?


Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As String, _
ByVal strWhere As String, ByVal strOrderBy) As ADODB.Recordset

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset

'Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
m_cDBLocation & ";"
rst.Open m_cUpdateDetailsFile, cnt, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

Set rst = cnt.Execute(strSelect & " " & strFrom & " " & strWhere & " " &
strOrderBy)

Set RunQuery = rst

' ' Close ADO objects
' rst.Close
' cnt.Close
' Set rst = Nothing
' Set cnt = Nothing

Jim,

Don't close the RS, just set the connection to nothing to get a disconnected
recordset. Stephen Bullen's VBA book is quite a good ref on this. You might
also want to have a look at http://www.able-consulting.com/ADO_Faq.htm

This is an extract of the kind of thing you need:

Public Function DsRs(strSQL as String) as ADODB.Recordset
'set up the connnection string here
Set DsRs = New ADODB.Recordset
With DsRs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
End With
DsRs.Open strSQL, strCon, , , adCmdText
Set DsRs.ActiveConnection = Nothing
Exit Function

Robin Hammond
www.enhanceddatasystems.com

"Jim Thomlinson" wrote in message
...
I have a function that is supposed to return an ADODB.RecordSet. The code
is
shown below. My problem is that when I close the connection and close the
recordset in this function the function returns nothing. I commented out
the
lines and it works fine. How doe I Keep the recordset even after the
connection has been closed?


Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As
String, _
ByVal strWhere As String, ByVal strOrderBy) As ADODB.Recordset

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset

'Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
m_cDBLocation & ";"
rst.Open m_cUpdateDetailsFile, cnt, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

Set rst = cnt.Execute(strSelect & " " & strFrom & " " & strWhere & " "
&
strOrderBy)

Set RunQuery = rst

' ' Close ADO objects
' rst.Close
' cnt.Close
' Set rst = Nothing
' Set cnt = Nothing

Thanks a bunch...

"Robin Hammond" wrote:

Jim,

Don't close the RS, just set the connection to nothing to get a disconnected
recordset. Stephen Bullen's VBA book is quite a good ref on this. You might
also want to have a look at http://www.able-consulting.com/ADO_Faq.htm

This is an extract of the kind of thing you need:

Public Function DsRs(strSQL as String) as ADODB.Recordset
'set up the connnection string here
Set DsRs = New ADODB.Recordset
With DsRs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
End With
DsRs.Open strSQL, strCon, , , adCmdText
Set DsRs.ActiveConnection = Nothing
Exit Function

Robin Hammond
www.enhanceddatasystems.com

"Jim Thomlinson" wrote in message
...
I have a function that is supposed to return an ADODB.RecordSet. The code
is
shown below. My problem is that when I close the connection and close the
recordset in this function the function returns nothing. I commented out
the
lines and it works fine. How doe I Keep the recordset even after the
connection has been closed?


Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As
String, _
ByVal strWhere As String, ByVal strOrderBy) As ADODB.Recordset

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset

'Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
m_cDBLocation & ";"
rst.Open m_cUpdateDetailsFile, cnt, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

Set rst = cnt.Execute(strSelect & " " & strFrom & " " & strWhere & " "
&
strOrderBy)

Set RunQuery = rst

' ' Close ADO objects
' rst.Close
' cnt.Close
' Set rst = Nothing
' Set cnt = Nothing