Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1724
  • Last Modified:

ADO recordset requery

I have an procedure that needs a recordset to select a different set of records in each iteration of a loop.  I am currently performing this by using the open method at the start of the loop and closing it at the end.  Unfortunately this is proving to be somewhat slow so I was wondering if there was any other way of doing this such as requery.  I am also limited to using a forward only recordset so FindFirst etc will not work.
0
justinhemphill
Asked:
justinhemphill
1 Solution
 
AzraSoundCommented:
you mentioned requery, is it not working for you?
0
 
lochianoCommented:
The method that we are using is to place the records into an array via a DLL Com object and disconnect from the database after the records have been read.  This way all of the programs what are running have access to the same table sets without going back to the database.  Each table has it's own class, dll-Com object.




sample code from the class  (real field names have been changed}:



Public Sub GetRow(ByRef row As Long)
   '  This method places the contents of a row derived in a LoadData method call
   '  into the object to return it to the calling program
   '  No validationdone to assure that ALL columns are in the array, as it is
   '  the responsibility of the program.  We are assuming a SQL to populate the
   '  arras as follows : select * from TABLE where ...............
   '  All columns are assumed to be included.  The results will not be valid if this
   ' not the case.
   
   On Error Resume Next
        mvar(Field1) = GetDataField(maryRecords(row, 0))
        mvar(Field2) = GetDataField(maryRecords(row, 1))
 .....
    On Error GoTo 0

End Sub





Public Function RetrieveRow(row As Long, Optional Delimiter As Variant) As String

   Dim strTemp       As String
   Dim X             As Long
   
   '  Set the delimiter to vbTab if not passed
   If IsMissing(Delimiter) Then
      Delimiter = vbTab
   End If
   
   strTemp = ""
   For X = 0 To mvarArrayColCount - 1
      strTemp = strTemp & maryRecords(row, X)
      If X < mvarArrayColCount - 1 Then
         strTemp = strTemp & Delimiter
      End If
   Next
   RetrieveRow = strTemp
   
End Function





Public Property Let {Field1}(ByVal vData As Boolean)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.InsertMode = 5
    mvar{Field1} = vData
End Property

Public Property Get {Field1}() As Boolean
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.InsertMode
    {Field1} = mvar{Field1}
End Property






Public Function Find(strWhat As String) As Long
......


strSQL = "select * from (table name) where (PrincID) = '" & strWhat & "'"
   Dim rsAnchor_Detail     As ADODB.Recordset
   Set  = cnn.Execute(strSQL)
   If Not .EOF Then
      Dim RecCount      As Long
      On Error GoTo FindError
      rs.MoveFirst
      While Not rs.EOF
         RecCount = RecCount + 1
         rs.MoveNext
      Wend
      mvarRecordCount = RecCount
      rs.MoveFirst
      If RecCount > 1 Then
         Dim X, y   As Long
         mvarArrayColCount = rs.Fields.Count
         ReDim maryRecords(RecCount, mvarArrayColCount)
         rs.MoveFirst
         While Not rs.EOF
            For X = 0 To mvarArrayColCount - 1
               maryRecords(y, X) = GetDataField(rsAnchor_Detail.Fields(X))
            Next
            y = y + 1
            rs.MoveNext
         Wend
.......
0
 
ca_hecklerCommented:
Keeping the original schema (open/close recordset on the loop), one way to optimize things is to create a Connection object *before* the loop, open the connection, and use it inside the loop, without closing. Something like this:


   Dim oConnection As ADODB.Connection
   Dim oRS As ADODB.Recordset

   Set oConnection = CreateObject("ADODB.Connection")
   oConnection.Open tConnectionString

   Set oRS = CreateObject("ADODB.Recordset")
   Set oRS.ActiveConnection = oConnection

   while ( ... condition ... )
   
      oRS.Open tQuery

      ' ... do something

      oRS.Close

   loop


   Set oRS = Nothing
   Set oConnection = Nothing


Sorry if you already do this now ...

Claudio A. Heckler

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now