Solved

ADO recordset requery

Posted on 2000-04-27
3
1,631 Views
Last Modified: 2013-11-25
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
Comment
Question by:justinhemphill
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 28

Expert Comment

by:AzraSound
ID: 2755902
you mentioned requery, is it not working for you?
0
 
LVL 1

Accepted Solution

by:
lochiano earned 100 total points
ID: 2755981
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
 
LVL 1

Expert Comment

by:ca_heckler
ID: 2756262
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

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question