asked on

Excel vba and disconnected recordsets

I created a function that will query a SQL Server 2000 database and return a value based on the contents of cell A1. I "call" this function from cell B1.

If I copy B1 down to B2 (using A2 as the "filter") the recordset gets completely re-queried from the database.

Can I avoid this? I'd like B2, C2, etc just to pull/filter data from a disconnected or memory resident recordset.

Thanks

Mike in IT

Can you post your code or better yet add a file with the code in it so that we can see what exactly is happening?

Sheldon Livingston

ASKER

Here is the explanation of what I am trying to accomplish with the code below. I am trying to open a recordset and query it multiple times without having to pull the data from the database again and again.:

Public Function TDLookup(theQuery As String, returnThis As String, whereClause As String)
  Dim oConn As ADODB.Connection
  Dim oRS As New ADODB.Recordset
  Dim sSQL As String

  If oRS.State = 0 Then 'If the RS is closed... open it
      Set oConn = New ADODB.Connection
    
      Dim sConnStr As String
    
      sConnStr = "Provider=sqloledb;Server=M2MSERVER;Database=M2Mdata01;User Id=sa;Password=just4admin;"
        
      oConn.Open (sConnStr)
    
      Dim oComm As ADODB.Command
    
      Set oComm = New ADODB.Command
    
      oComm.CommandTimeout = 300
    
      sSQL = wksSQL.Range(theQuery)
    
      oRS.CursorLocation = adUseClient
    
      oRS.Open sSQL, oConn, adOpenStatic 'adLockBatchOptimistic
    
      Set oRS.ActiveConnection = Nothing
  End If


  oRS.Filter = whereClause

  If oRS.RecordCount = 0 Then
      TDLookup = 0
  Else
      TDLookup = oRS.Fields(returnThis)
  End If
End Function

Open in new window

ASKER CERTIFIED SOLUTION

Mike in IT

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sheldon Livingston

ASKER

Thanks Mike... I basically just looped through my data... simulated the same thing.