Re: ADODB Recordset Function

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Jim Thomlinson (JimThomlinson_at_discussions.microsoft.com)
Date: 12/11/04 

Date: Fri, 10 Dec 2004 17:31:02 -0800

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" <JimThomlinson@discussions.microsoft.com> wrote in message
> news:3B41599C-DE92-4CB1-AEC2-91827B43958D@microsoft.com...
> >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
>
>
>

Relevant Pages

  • Re: Database Access
    ... > time to re-establish the connection there will always be, unavoidably, the ... > recordset where you would hit the database just once (the first time the ... > Public ClonedRS as ADODB.Recordset ... > Function DatabaseLookup(sDate As String, sCol As String, sTable As String) ...
    (microsoft.public.excel.programming)
  • Re: Database Access
    ... As for the connection issue: you could design code that holds the connection ... recordset where you would hit the database just once (the first time the ... Public ClonedRS as ADODB.Recordset ... Function DatabaseLookup(sDate As String, sCol As String, sTable As String) ...
    (microsoft.public.excel.programming)
  • Re: Idea of SQL integration
    ... function OpenConnection(const ConnectionString: string): Connection; ... // Recordset functions ... TRecFieldDefArray = array of TRecFieldDef; ...
    (borland.public.delphi.non-technical)
  • Re: MSDE 2000 Release A: Run-Time Error -2147467259 (8000040050)
    ... One suggestion would be to replace the "localhost" value in the connection ... Dim str1 As String ... 'Create recordset reference, and set its properties. ... Set rst1 = New ADODB.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: ADO connections question
    ... Function CreateADOObjects(ConnectionString as string) ... you'll see this error if the connection has not been ... I have a specific login form that calls the dbLogin function as shown ... Dim strCriteria As String ...
    (microsoft.public.access.adp.sqlserver)